Options to Replicate Microsoft SQL Server Database to MySQL/PostgreSQL on Linux

Question

I need to replicate data from Microsoft SQL Server to MySQL or PostgreSQL. The data includes images stored in BLOB columns.

Could you please comment on your experiences with the following strategies and suggest others I may have missing?

1. custom script written in Java using JDBC
2. linux odbc driver with perl script
3. Setup my own windows box and use SSIS or DTS w/Postgres ODBC driver
4. write .net program and schedule execution on Windows machine
5. FreeTDS

Answer 1

Looking at SQL Server Replication Technologies you can use Non-SQL Server Subscribers although only Oracle and DB2 are officially supported at this time, custom solutions have been implemented successfully.

http://msdn.microsoft.com/en-us/library/ms151835.aspx

Using SQL Server Integration Services would be a good approach in my opinion. You could easily create custom components to interface with Non-SQL Server Subscribers if necessary.

Answer 2

Python has good libraries for all three databases, and SQLAlchemy makes writing db stuff easy. I use freetds and unixodbc for SQL Server.

If you actually want the same table structure, SQLAlchemy makes this particularly easy, as you can define the structure once in a generic way and have it create the structure in each of the three databases.

Answer 3

You can also follow instructions from http://blog.hagander.net/archives/103-Replicating-from-MS-SQL-Server-to-PostgreSQL.html

I had to re-create table msrepl7 changing fields indagent and subtype to type integer (some problem with character = integer query on Postgres 8.3)

Good luck.