Mixed Mode Bi-Directional Transactional Replication between SQL 2000 and SQL 2008

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.


Tags:
Categories: SQL Server | Tips | Development

14 Comments
Actions: E-mail | Permalink | Comment RSSRSS comment feed

Comments

Do you have an example generated publication script you could email me?

April 3. 2009 00:58 | Tom United States |

good article.

April 21. 2009 16:57 | evden eve nakliye |

thank you, very usefull

May 12. 2009 06:19 | ricky United States |

thanks for sharing

May 14. 2009 16:23 | Earn high school diploma online United States |

good work

May 14. 2009 16:24 | Online High School United States |

I was not knowing about this one.I have been using SQL for last 2 years  but this one is new to me

May 15. 2009 08:46 | Weight Loss Pills India |

thanks for sharing

June 10. 2009 13:51 | tukang nggame United States |

great info, thanks a lot..

June 16. 2009 12:07 | mark United States |

Very useful information specially for all system administrator. Even if I am no longer working as Sales in  computer products, it is still nice to read something like this, it updates me of recent changes and technology.   

July 1. 2009 10:25 | weight loss pills United States |

Congrats Boy about this

July 13. 2009 00:03 | Rusli zainal sang visioner United States |

Thanks and great article

July 13. 2009 00:03 | Blogger Indonesia United States |

See you next time with more powerful article

July 13. 2009 00:04 | Blogger Indonesia United States |

Pingback from eonlinegratis.com

SQL 2008 Replication Corrupt Data Problem | Click & Find Answer !

August 23. 2013 08:17 | eonlinegratis.com |

Pingback from jjeasy.com

SQL 2008 Replication corrupt data problem - Just just easy answers

September 6. 2013 14:20 | jjeasy.com |

Comments are closed