Substitute publisher server

Question

Substitute publisher server

Marco R

Grasshopper

Points: 17
More actions
July 3, 2009 at 2:25 am

#93767

I have an old cluster server that is publisher of a merge replication (it publishes 2 database: 1 bidirectional (300Mb) and the other monodirectional (25Gb) from subscribers to publisher).
The server has SQL Server 2000 Enterprise SP1 installed, while the subscribers have MSDE SP4.
Totally I have 70 subscribers for about 140 merge replication agents-
I would like to change the server with a new one (Window Server 2008 Enterprise Edition and SQLServer2008 STD).
What is the best way to change the server with the minimun down of the system?
Are there some problems with the publisher SQL2008 and the subscribers MSDE?
Thanks in advance for any suggestion.
Regards,
Marco

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello Marco,

I had a quick look through the following article and couldn’t spot any reason why 2008 to (MSDE) 2000 would not work, but you might want to read the document more thoroughly:-

http://msdn.microsoft.com/en-us/library/ms143241.aspx

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Marco R Grasshopper Points: 17 More actions · Answer 2

Thanks for the usefull link.

And what about the best method to change the publisher?

Maybe these are the step to follow?

1) Configure the new server as publisher

2) Stop the replication process of the bidirectional DB (named BidDBOld) on the old server

3) Stop the replication process of the monodirectional DB (named MonDBOld) on the old server

4) Backup DBs on the old server

5) Restore DBs on the new server

6) Define triggers to update DATA of the BidDBOld from the old server to the new one BidDBNew (so the data are synchronized)

7) Define triggers to update DATA of the MonDBOld from the old server to the new one MonDBNew (so the data are synchronized)

8) Reactivate replication process on the Old server

9) Publish the Dbs on the new server

10) Create a parallel replica with new merge agents for the 70 subscribers (using new Db on the clients)

11) For each client/subscriber: redirect application connections from the old local db to the new local db

Is there any faster or more simple method?

Thanks,

Marco

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Actually the recommended method is In-place upgrade. Especially for the distributor.

I read this in a document.

"The Distributor is the key to how much downtime will be encountered during the upgrade. If there are multiple Distributors in the topology, there will be multiple outages. Where possible, assuming the hardware is not out of date, performing an in-place upgrade instead of installing a new instance of SQL Server and reconfiguring the Distributor is the preferred upgrade method. When upgrading in an environment that has multiple Distributors, upgrade them in order of magnitude: Do not upgrade the biggest and most important Distributor first. Schedule the upgrades to have the least impact on the end users and applications.

Tip: Always upgrade the Distributors first because they can push changes from a Publisher to a Subscriber that are both down level from the SQL Server 2008 Distributor. Ensure that the Publishers and Subscribers are at the right SQL Server patch levels; otherwise, upgrading the Distributor first will be pointless."

-Roy

WayneS SSC Guru Points: 95460 More actions · Answer 4

Roy... do you have a link to that document?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Marco R Grasshopper Points: 17 More actions · Answer 5

I cannot upgrade the cluster server (publisher and distributor) because the hardware is obsolete. I need a new server....

Thanks,

Marco

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

WayneS (7/3/2009)
Roy... do you have a link to that document?

I downloaded it from the Microsoft site itself.

SQL Server 2008 Upgrade Technical Reference Guide

By Microsoft Corporation

Published: November 2008

-Roy