Export SQL Server Database from MySQL Import

Question

I have a .net script access to a microsoft server, and I need to synchronize the database for this purpose with the LAMP server on the other end. Currently, I have written my own CSV export procedure and the corresponding importer, but it creaks under stress. Is there a more efficient way to do this? Is there any way to export CSV export to IIS?

I know that if I had more privileges, I could run the BACKUP DATABASE command and then start the backup to another server with FTP and hopefully find a way to import it into MySQL. However, this is not an option.

Answer 1

If MySQL is accessible from an MSSQL server, you can create a linked server inside MSSQL and MERGE , INSERT , UPDATE or something in MySQL tables from MSSQL.

Answer 2

I did something similar. The easiest way is to have a direct connection between your MSSQL server and the MySQL server. You install the linked server in MSSQL on the MySQL server, and then use insert, update, etc. To complete transactions on a linked server. This is what his answer says. However, you do not have a direct link to the MySQL server, so the option is not viable.

Another option is to use the MSSQL Service broker. With the help of a service broker, you can queue individual updates, delete and paste them into a web service. This web service can be hosted on IIS. Webservice is called from the queue, and then you can apply individual transactions one at a time to the MySQL database. A service broker is an asynchronous messaging system built into MSSQL 2005+. With it, you can send messages to other servers, databases and even external services.

Just keep in mind that the Service Broker architecture takes time to learn and implement. I created my own replication architecture between mssql and mysql using a service broker that processes 88 million plus transaction per day on a celeron laptop. It just took some serious elbow grease.

UPDATE It seems that SQL2008 allows you to run Service Brokers outside of the SQL server. See Link http://blogs.msdn.com/b/sql_service_broker/archive/2008/11/21/announcing-service-broker-external-activator.aspx I don’t know if this could be on your street.

Answer 3

You can program a script that has .NET DataReaders to open cursors in MS SQL, and then using another connection that is connected to MySQL, inserts data row by row. You can process all tables sequentially.

Answer 4

Instead of CSV files, SQL scripts are generated that are suitable for working on the MySQL server

Answer 5

I don’t know if this will help, but a long time ago I created a small software to perform this specific task with a large data set, here it is:

http://csvimporter.codeplex.com/

I did it open source :)

Hope this helps.