2

I have the following scenario: a database in SQL Server 2012 that must replicate changes in a transactional way to a SQL Server 2000 database. Since the schemas of two databases differ, a transformation must be made for the destination. Both databases are in the same network. What's the better approach?

  1. Use triggers in the source database to make the transformations and perform the changes in the destination.

  2. Use SQL Server transactional replication (but transformations seems not possible, as I know).

  3. Any alternative?

Improve this question
7
  • Only the schema is different or the table structure is different as well ?
    – Kin Shah
    Commented Jun 19, 2013 at 18:20
  • The table structure is different.
    – Apocatastasis
    Commented Jun 19, 2013 at 18:31
  • How often you want to keep the source and destination in sync ?
    – Kin Shah
    Commented Jun 19, 2013 at 18:46
  • As near to real time as possible.
    – Apocatastasis
    Commented Jun 19, 2013 at 19:05
  • If the SQL Server 2000 database is read only (sounds like it needs to be, or at least these tables need to be), why don't you replace the tables with views over a linked server?
    – Aaron Bertrand
    Commented Jun 19, 2013 at 19:22

2 Answers 2

Reset to default
2

Since you have different schema and different table structure, SQL Server transactional replication is out of scope.

Any alternative?

As an alternative, you are better off designing your own custom solution using :

Microsoft Sync framework and change tracking (CT) (not Change data capture (CDC)).

Note : The source should be SQL Server 2008 and up as CT was introduced in sql server 2008 and up.

You can also look into Service Broker as it is optimized for uni-directional data flow and it is MUCH faster than writing to a table, even a heap. In addition, you retain the transactional consistency of SQL Server, so once data has been written to a queue, it is guaranteed to be in the queue, and will only be deleted from the source server has it has been received.

Also refer to :

Improve this answer
1
  • Well, since Service Broker isn't available to SQL Server 2000 that left us only Sync Framework to investigate.
    – Apocatastasis
    Commented Jun 19, 2013 at 21:42
2

Use a linked server as Aaron mentioned and bulk load your data to your SQL 2000 tables after doing the transformation within SQL 2012. You could just create a few staging tables in SQL 2012 where you can place your transformed data as it comes in.

Beware of linked server from 2012 to 2000 using SQLNCLI10: see here SQLNCLI10

The new client SQLNCLI11 does not work with SQL 2000 as far as I know.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.