Take the 2-minute tour ×
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.

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?

share|improve this question
    
Only the schema is different or the table structure is different as well ? –  Kin Jun 19 '13 at 18:20
    
The table structure is different. –  Apocatastasis Jun 19 '13 at 18:31
    
How often you want to keep the source and destination in sync ? –  Kin Jun 19 '13 at 18:46
    
As near to real time as possible. –  Apocatastasis Jun 19 '13 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 Jun 19 '13 at 19:22

2 Answers 2

up vote 1 down vote accepted

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 :

share|improve this answer
    
Well, since Service Broker isn't available to SQL Server 2000 that left us only Sync Framework to investigate. –  Apocatastasis Jun 19 '13 at 21:42

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.

share|improve this answer

Your Answer

 
discard

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

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