Database replication from SQLserver 2000 to SQLserver 2008

Question

I'm trying to replicate a rather large database from SQLServer 2000 to SQLServer 2008, located on two different servers. I found an article about attempting this and have been trying to follow its direction. Here is the article.

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

Here is the part I'm stuck on:

"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."

I was able to get the stored procedures in place for all of the tables but need some direction on creating a generated publication script. Does anyone have some direction or a good example of a generated publication script?

Answer 1

I'm just doing an upgrade of SQL 2000 to SQL 2008 R2 and I'm finding the publication wizard works just fine without any need to modify the scripts generated.

The article suggests creating some stored procs because it does not work bi-directionally out of the box. However, when it works brilliantly both directions, and very quickly too, just using the wizard. This is using SQL 2008 R2 which may be the reason, it may have improved since the original SQL 2008.

Answer 2

On the final step of the publication wizard you will have the two options: 1. Create the publication 2. Generate a script file with steps to create the publication

The article referenced here is suggesting that you do not let the wizard create the publication but instead only choose to generate the script file. At that point you can edit the script file as instructed in the article.

Answer 3

You can run through the SQL Publication Wizard in 2008 and have it output to a script and then use that as a base model to modify with the appropriate arguments for the sp parameters listed in the question.