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?

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 12 '09 at 18:59

Greg Roberts

2,562
1
20
23

I probably misused the word replication. I want it to be synchronized permanently, real-time at the least. If not real-time synchronized, bi-hourly copying is fine with me. I need options to choose from; I'm so lost. – BeemerGuy Nov 12 '09 at 19:09

Paul Sasik · Answer 2 · 2009-11-12T20:04:53.290

0

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.

edited Nov 12 '09 at 20:04

answered Nov 12 '09 at 19:02

Paul Sasik

79,492
20
149
189

Refer to the comment I added to the other answer. Thank you. – BeemerGuy Nov 12 '09 at 19:09
Saw the comment: Bi-hourly (or even more frequent) copies could easily be handled with a scheduled DTS package. – Paul Sasik Nov 12 '09 at 19:57

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

2 Answers2