CODEWITHSUNDEEP
mysqlsql-server
Darth Egregious
Darth Egregious

Reputation: 20086

Export SQL Server database from MySQL import

I have .net script access to a microsoft server and I need to synch the database on this end with a LAMP server on the other end. Currently I've written my own CSV export routine, and a matching importer, but it's creaking under the strain. Is there a more efficient way to go about this? Can I stream a CSV export direct to IIS somehow?

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

Upvotes: 4

Views: 925

Answers (5)

RicardoE
RicardoE

Reputation: 1725

I dont know if this will help, but a long time ago I created a small software to do this particular task with a large dataset, here it is:

http://csvimporter.codeplex.com/

I made it open source :)

Hope it helps.

Upvotes: 0

Namphibian
Namphibian

Reputation: 12211

I have done something similar. The easiest way would be to have a direct connection between your MSSQL server and MySQL server. You setup a linked server in MSSQL to the MySQL server and then use insert,update etc to do transactions to the Linked server. This is what ta.speot.is mentioned in his answer. However you dont have a direct link to the MySQL server so that option is not viable.

The other option would be to use MSSQL Service broker. With service broker you could queue individual updates, deletes and inserts to a webservice. This webservice could be hosted on IIS. The webservice would get called from the queue and then you can apply the individual transactions one at a time to the MySQL database. Service broker is a asynchronous messaging system embedded inside MSSQL 2005+. With it you can send messages to other servers, databases and even external services.

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

UPDATE Seems like SQL2008 allows you to run Service Brokers outside of SQL server in some way. See this link http://blogs.msdn.com/b/sql_service_broker/archive/2008/11/21/announcing-service-broker-external-activator.aspx I dont know if this might be more up your street.

Upvotes: 4

Konstantin Pereiaslov
Konstantin Pereiaslov

Reputation: 1814

Instead of CSV-files generate SQL-scripts suitable to run on MySQL server

Upvotes: 3

somnath
somnath

Reputation: 1335

You can code a script that has .NET DataReaders to open cursors on MS SQL and then using another connection that is connected to MySQL insert the data row by row. You can process all the tables sequentially.

Upvotes: 4

ta.speot.is
ta.speot.is

Reputation: 27214

If MySQL is accessible from the MSSQL server then you can create a linked server within MSSQL and MERGE, INSERT, UPDATE or whatever into MySQL's tables from within MSSQL.

Upvotes: 5

Related Questions