Hi all, I have to migrate the SQL databases to our MySQL Linux system. Can someone please help me with how to do it? I am a SQL server DBA but not experienced with MYSQL.
Thanks in advance!
Do you just want the Tables and Data? or do you want all the Stored Procedures etc. too?
Tables and Data will be straightforward, up-to-a-point (you could probably just use SSIS if that is your thing), whereas the Stored Procedures etc. will be a nightmare ...
I wanted to give you a quick option on this and the utility of the suggestion is dependent on several things. I have had to move data from SQL Server to MySQL often. I use a combination of BCP to get the data out and then the MySQL "equivalent" which is the LOAD DATA LOCAL INFILE.
It is easy to automate both pieces but that means you only after data and may not work if the number of tables/servers is large. It depends. Yeah, I got to say that!
Working with dates in MySQL can be challenging because SQL Server likes to export them in a default format that is not readily consumable by MySQL (in my experience). In short, you will have to play with the date formats if you do this to ensure you get both systems to play nice.
You can roll all that into a PowerShell script that is driven by an XML configuration file for the tables you want to work with and even do something similar with multiple servers. SSIS is the gold standard of ETL work but you have many options available.
We have only had to PULL data from MySQL, not PUSH it in the other direction.
In all instances we've used a direct database connection (so some sort of OLEDB connector) and then processed the data "direct". This avoids all the issues with CSV-type files and datatype formatting. Well ... from MEMORY I think it avoids them all, but its possible I've forgotten some that we stumbled over and have now forgotten!
Whether it is a good, and efficient, means of transferring lots of data (i.e. large tables / many rows) I have no idea I'm afraid