Merge replication: problems upgrading SQL-2000 to 2005

Hello!

We have a module in our business-application, that automatically installs merge replication of the business-application's database, both at publisher and subscribers. It's intended for Sql-2000. Now we need it to be applicable also for Sql-2005, so the module requires some changes, because, as we noticed, sql-2005's replication technology differs from Sql-2000's one.

A few questions to experts, familiar with Merge Replication in SQL-2005:

1. Is it possible to create hybrid replication, with publisher running at SQL-2000 (MSDE) and subscribers running at SQL-2005 Express? Merge publication is not supported in SQL 2005 Express, but some users may require option to use the application at free-of-charge database platform.

2. How deep are changes in merge replication implementation at system level? Is it just modified a bit since SQL-2000, or changed entirely? This knowledge is needed, because the module uses some low-level features (executing system sp's, querying replication-specified tables, etc.). For example, when we tried to create subscription of existing publication in SQL 2005 using the module as is, we found out that sp_addmergepullsubscription_agent doesn't use @.encrypted_password parameter anymore, and subscription creation process failed.

3. If anybody has experience using merge replication creation/deletion/detection scripts, generated by SQL 2000, in SQL 2005! Please, tell - what more problems may happen?

Thanks!

1. For merge replication, the version of the distributor has to be greater than or equal to the publisher, and the publisher has to be greater than or equal to the subscriber. So no, a SQL 2000 publisher and SQL 2005 subscriber will not work.

2. This is a very vague question, it's best if you post specific questions and we can answer them. But yes there are changes to some of the tables and stored procedures, but for the most part your SQL 2000 scripts should work in SQL 2005 with a few caveats (such as the sp_addmergesubscription_agent parameter change).

3. The best thing you can do is set up a test environment and try things out, and make any changes necessary. Once your new environment is set up correctly, you can generate new scripts to be used.

|||

I have tested creating publication on sql 2000 with RMO interface. (publisher and subscriber are sql 2000 or 2005 . of course, publisher has to be the same or higher version than the subscriber)

it doesn't work and some of the classes only work for 2005. By using scripts to do that job, I found some of the system sps on 2005 and 2000 are different. for instance,

sp_startpublication_snapshot doesn't exist in sql 2000 server.

I believe scripting are the only choice.

I browsed msdn for this topic. it 's interesting that microsoft only talks about what RMO can do and doesn't mention what it can't do.

there are few topics discussing hybrid replication snario. if you found anything useful, please reply.

ta.