Home / server / Questions / 352165
Accepted
MichaelGG
Asked: 2012-01-21 21:27:10 +0800 CST

Safe initial sync for SQL Server OLEDB/ODBC Replication

  • 772

I am trying to setup one-way, transactional replication from SQL Server 2008 R2 to PostgreSQL 9.1. I'm trying out the PGNP OLE DB provider.

I can successfully create a subscription, and transactions are indeed replicated to the PG server. I am using sync_type = 'none' on the subscription, because it seems that otherwise, the schema, including SQL Server specific options (SET ANSI_NULLS ON, etc.) get sent to PostgreSQL, which screws it up.

This is fine, I can use SSIS and copy my entire database over, including data. My question is, how can I do this in a safe way? Because if I just run a SSIS package to export the data, then add the subscription, transactions could get lost.

E.g. SSIS exports data to PG. Row X is updated. Replication starts. In this case, row X's update wouldn't be in my manual sync, but replication started after. And I can't have replication running, because other errors could happen.

What's the proper procedure here?

sql-server replication postgresql

2 Answers

  1. Best Answer

    I had several times a similar problem. The trick for this is to setup 2 Subscriptions

    • The one you have currently setup
    • An other one to a sql server database, which will sync "normally"

    So here is the whole procedure:

    1. Configure a sql server subscriber, sync it as you like, the important point is to be sure that this subscriber is correctly synced
    2. Configure your replication to postgres. Take care to have all the destination tables created in postgres, but do not care about the data synchro.
    3. At this stage, when you modify data on the primary, it is copied on both slaves. Now you stop le LOG READER agent at the Distributor. And you wait ( thanks to replication monitor for example ) that both subscription agents have processed all transactions to both subscribers.
    4. Truncate all the replicated tables at the POSTGRES subscriber
    5. Copy all the tables from the SQL Subscriber ( NOT the primary server !! ) to the Postgres Subscriber.
    6. Both subscriber have now exactly the same data!
    7. Start the Log Reader agent. You're done

    Please note that while the Log Reader Agent is stopped the transactions on the Publisher will be stuck on the LOG of the Publisher. So take care to not let it go full. Step 5 ( copying the data ) can be pretty long, so configure a big enough log on the primary ( depending on the volume of transaction ).

    By the way, if you put sync_type to 'automatic' the snapshost agent will generate a snapshot which will be properly synced also. Where's my bounty?

    • 3
    • 1