Here's how I did it.
1. Created a user on my database with appropriate permissions and SQL Server password (as opposed to Windows login).
2. Set up Windows EC2 instance in private database subnet/security group where my SQL Server database was located.
3. Added the security group to itself for inbound and outbound connections to ensure instances in the group could connect to each other (see previous post).
4. Set up an instance of MySQL on RDS in database specific subnet but same security group as my SQL Server database.
5. Set up ODBC connection (Administrative tools / ODBC data sources) on EC2 instance to SQL Server database, connected and tested it out. Just created a user connection, enter IP address of EC2 instance running SQL Server, user name, password.
6. Downloaded MySQL installer for Windows from Oracle web site that does not require Internet connection.
7. Copy MySQL installer to EC2 instance and installed. Ran and failed.Said I have to run 32 bit version (which I was).
9. Spent way too long figuring out that I need to instsall this to get MySQL workbench running on small/medium AWS EC2 instances.
Microsoft Visual C++ 2010 Redistributable Package (x86)
http://www.microsoft.com/download/en/details.aspx?id=5555
10. Replace the batteries in my mouse (Just seeing if you are still paying attention. It's been a long day.).
11. Run MySQL Workbench - go to Database Menu and choose Migration Wizard.
12. Click Start Migration
13. For Source RDBMS selected:
Database System = Microsoft SQL Server
Connection Method=ODBC
Selected DSN I created above
Entered user name
Clicked store in vault and entered password
> Test Connection
> click next
14. For destination RDBMS:
Connection Method: Stardard TCP/IP
Host Name: domain name of RDS instance from AWS console
Default Port
Enter user name and password
> Test connection (at bottom)
> Next
15. Next screen - runs the script that creates the schema in the RDS instance based on analysis of MS SQL database. Note in previous post I ended up deleting a table to work around a problem with the reverse engineering of the schema step.
> Next
16. Select schemas and options from databases you want to migrate.
> Next
17. Choose objects you want to migrate
> Next
18. No migration errors.
> Next
19. Check create schema in target RDBMS (I also opted for script file)
> Next
20. Review. No errors or warnings.
> Next
21. Online copy of data to target database
> Next
22. This is where the bulk data copy occurs and the server gets really busy...
...wait...
23. At this point my MySQL WorkBench locked up and machine CPU utilization spiked to 100% for a long period of time. I ran it for a few hours, then fired up MySQL WorkBench on another instance and spot checking see that my tables and data seem to be present on the other machine.
24. Killed it - was taking too long. Could see most of tables were created by no stored procedures.
Round 2 - tried running the whole process again with new machine and only selected tables. Also corrected my selection and chose to keep my schema in tact this time around.
25. Checked logs here by default [insert your user name] while process was running because it appeared when I hit "show logs" mysql workbench locked up:
C:\Users\[user name]\AppData\Roaming\MySQL\Workbench\log
26. Saw that my script seems to lock up on dbo objects starting with dtproperties.
27.Checked tables and all in my own schema (not dbo) appear to have data.
28. Attempted to run stored procedures, functions and views - kept hanging.
29. Tried to create a script for procedures, functions and views and output to file.
30. Ran script and figured out user account created in RDS set up process does not have permissions to execute.
31.How to enable triggers and stored procedures:
http://websitenotebook.blogspot.com/2013/11/enabling-triggers-and-stored-procedures.html
http://websitenotebook.blogspot.com/2013/11/converting-sql-server-strored.html
33. For loading large amounts of data consider these recommendations...my machine kept locking up using the migration tool:
