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?
Change the word "migrate" to "re-write." Make darn sure that the economic justification is really there before you begin, because that Microsoft salesman ain't gonna be nowhere nearby when you write the code.
Microsoft has architected the entire dot-net system (of course) to sell Microsoft server licenses, but that does not mean that you have to use their database. And maybe your first step should be to change only one part of the system at a time. No, maybe the first step should be to enumerate all the ultimately-compelling reasons why the system absolutely cannot be left the way it is; why all of the development effort that went into it must now be scrapped. "Warm fuzzy feelings" left by a skilled FUD-artist don't count.
Ahem... just checking.
There are various "data pump" programs out there which will handle ordinary data-type conversions but you will probably have to translate the schema (DML) manually. You'll do it twice: once to populate the database with a set of test-data; the second to go live with it.
This is such a fundamental change that I'll once again stress: the Microsoft salesman who may have persuaded your boss-of-boss to do this, won't be anywhere around after the license agreement has been sold. But your business (yours, not his...) will rely upon that system all the while. Eyes wide open, friend... eyes wide open.