Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

We have a SQL Server legacy database, db1, to put data of Project1. With change of certain tables(insert/update) in db1, we need to transfer the changes in another postgres database, db2 (used by Project2).

Presently the system is adhoc. With any change of table1 in db1, we keep the record (table1, id, actiontype) in a 'Queue' table, upon which a cron job runs every 5 minutes, which transfers data to db2 using the information in db1.Queue.

Problem is, following this way, we can't see real-time reflection in db2, i.e. data from db1 wouldn't immediately propagate to db2. We have to wait for the cron job to run.

Any suggestion to improve the solution highly appreciated.

Is distributed async task queue (e.g. Celery) a good option here?

share|improve this question
you can check in JOb Activity monitor for actual reason, for what data is not reflected on DB2. – KuldipMCA Aug 13 '12 at 6:58
Which version of MS SQL Server? It seems like Microsoft adds and removes functionality with each version, so different strategies are required for different versions. – Craig Ringer Aug 13 '12 at 8:17
It's not MS SQL at all. It's only "sql-server" in the generic Structured Query Language sense. Things like Cron and Celery are unix-based. – Bob Aug 14 '12 at 16:57

migrated from stackoverflow.com Aug 14 '12 at 14:28

2 Answers

up vote 1 down vote

You should be able to use triggers and dbilink to sync your data if you're prepared to install plperl on the postgresql end. If you are running version 9.1+ then you could instead try the foreign-data-wrapper functionality. There appear to be tds and fdw wrappers, though I've not used either myself.

Unless I'm missing something, an external queue manager sounds overkill.

share|improve this answer
Another possibility is to add triggers to the tables in MS-SQL server that log row-level changes to an audit table. You can then have a helper program in your favorite language connect to both databases, query audit tables, and apply the changes to the Pg tables. Some care for concurrency issues is required but it isn't hard. – Craig Ringer Aug 13 '12 at 7:33
@Craig, This is what we are doing now. But problem is, we have to wait (5 min) for the cron job to run. We want the reflection immediately(but asynchronously - just in case db2 is down) from db1(mssql) to db2(postgres). – str Aug 13 '12 at 8:06
@Richard, we are actually trying for the other way (mssql to postgres). Anyway, but don't we need the transfer task to be asynchronous? Think of a case, when the postgres db2 is down. – str Aug 13 '12 at 8:12
Apologies - had the transfer direction the wrong way around. You've got a slight conflict between real-time and asynchronous. How short a lag would be acceptable? Is 1 second ok? – Richard Huxton Aug 13 '12 at 8:47
up vote 0 down vote

The question .Net SQL Server Database Monitoring - Insert, Update, Delete has some useful answers for different versions of MS SQL Server, and this one covers very old versions.

It seems the most portable approach would be to have a small script/program run in the background, holding a connection to both PostgreSQL and MS SQL Server. It could query your trigger-maintained queue table every few seconds and when it finds changes insert them into PostgreSQL. The need to poll isn't lovely, but it's portable.

More version-specific approaches appear to involve MS SQL Server Notification Services or SQLDependency.

I'd avoid trying to push directly from MS-SQL using a CLR (C#/.NET) managed code trigger and an ODBC connection to Pg because:

  • If the connection between MS-SQL and Pg is down, Pg is down, etc, you'll lose the change and go out of sync;
  • MS-SQL doesn't seem to offer after-commit triggers, and you don't want to push the change until you've committed. You don't want the tx to roll back after you've pushed the change to Pg.

... however, you could have a managed code trigger use inter-process communication to wake up a synchronization daemon. That way the daemon could go to sleep when nothing was changing and avoid polling unnecessarily.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.