How can I replicate a table from SQL 2000 to SQL 2008?

Question

I have a table on a SQL Server 2000 database, which I want copied verbatim to a 2008 server.

I tried to do it manually with INSERT/UPDATE triggers, but this technique runs in a distributed transaction, which does not work because I apparently have to enable MSDTC and the firewall; I don't want to do any of that.

Are there any other ways to replicate that table (in real-time) to the 2008 server?

Answer 1

My first question would be do you really need it to be replicated?

If you want to reference it real-time in Sql 2008, just do a linked server with a Synonym. This will make the table act like it is part of your schema, with minor limitations.

Answer 2

Have you tried to create a scheduled DTS/SSIS package to do the work?

Is it copying or replication? Copying implies a one-time action while replication implies an open-ended period of time during which these tables will be synchronized.