We are planning to migrate an application written in Perl + mySQL to .NET + MS-SQL Server. The test machine now has SQL Server running. During the development phase in .NET, we would want to access the mySQL database which is being updated at real time, from the Windows box. This is how I think it has to be done:
1. Use DTS to migrate data from mySQL. Does DTS take care of migrating schema and the difference in data types between the mySQL and MS-SQL? Is there any 3rd party software out there that takes care of the migration without any manual labor?
2. Synchronize the data between 2 DBs by creating some kind of a batch job? If so, whats the best way of doing this?
Instead, is it easier to access the live mySQL database on the Linux box from the .NET code and move the DB only after development in the new environment is completed?
In order to handle the schema, you use the transformation portion of dts(data transformation task). Do you need to have "live" information in the sql server database while you are creating the application in .Net ? If not, just take a snapshot of the data from mysql to sql server and perform the complete migration when you are ready to switch database servers.
Ok, so if you have an ODBC driver for mySQL you should be able to use DTS to do SQL-92 compliant stuff with your old db.
In all honesty I would suggest that you move these two things one at a time, move either your database or your code but not both at the same time, your testing and debugging will be a nightmare otherwise.