A recent project I am involved in requires us to demonstrate a bi-directional replication setup between SQL Server 2000 and SQL Server 2008. There are a few gotchas one needs to be aware of while doing something like this.
First off, make sure that the SQL Server 2000 server has the latest service pack installed. Next, on the SQL Server 2008 server, open up the Distributor properties and add the name of the SQL Server 2000 server. Give a strong administrative link password when prompted.
Now, on the SQL Server 2000 server, configure distribution. Make sure you specify that the Distributor is running on the SQL Server 2008 server. (Note: if the server is already configured as a distributor and has a number of publications, read this MSDN article to see how it can be changed.)
Next comes an important part before you add publications from a SQL Server 2000 database to a SQL Server 2008 database. For each article (read: table) that you wish to publish on 2000 and subscribe on 2008, you need to have 3 stored procedures on the subscriber database – one each for INSERT, UPDATE and DELETE.
That is, if you have a table tbl2000 on SQL Server 2000 that needs to be published to a subscriber database called tbl2008 on SQL Server 2008, the SQL Server 2008 database needs 3 stored procedures called say, sp_ins_tbl2008, sp_upd_tbl2008 & sp_del_tbl2008. (Note: normally, the publication script can create these automatically when you give the schema_option=0x02 in the sp_addarticle procedure. But for some reason, it doesn’t seem to work across 2000 and 2008 scenarios that I tried.) These stored procedures basically insert, update and delete the records in the subscriber (on SQL2008) database.
So, to create a publication, you will need to NOT use the publication wizard that you get in SQL 2008. Instead, use a generated publication script and for each sp_addarticle line that you have in it, make sure that the @ins_cmd, @upd_cmd & @del_cmd parameters point to the appropriate stored procedures and run it on the SQL Server 2000 server. Once this is done, go ahead and create a subscription to SQL Server 2008’s database normally.
For the other side (that is, SQL Server 2008 to SQL Server 2000), normal publish-subscribe rules apply and work fine.
Categories: SQL Server