SQL Server Performance Forum – Threads Archive

Upgrading Subscription Server

Hi Everyone, Transactional replication has been setup and running perfectly from OLTP to Data warehouse servers in my company and there are no issues with this. Now Data warehouse servers are upgrading to the new hardware and server will be a new server name(Assume the current data warehouse server is Server1 and new upgraded one is Server2). This upgrading process will be taking about 36 hours to 48 hours and obviously the replication is stopped during this period. After that transactional replication should be resumed whereever it is stopped before upgrading process to new server (Server2) without reinitializing the snapshot. I am planning to achieve this task by upgrading the system tables values and distribution agent as below. 1) Stop all replication agents (snapshot, distribution and log reader agent). I am aware of log file growth since the marked transactions are not moved to distribution database through log reader agent . 2) Update the srvid and name column values to the new server id and name in the syssubscriptions system table in the publication database. 3) Update the distribution agent job script. -Subscriber [Server2(New upgraded server name)] -SubscriberDB [Subscriber Database] -Publisher [Publisher Server Name] -Distributor [Distributor Server Name] -DistributorSecurityMode 1 -Publication [Publication Name] -PublisherDB [Publisher Database] -Continuous It seems this is theortically correct but the replication is not still working for some reason. It will be great If anyone come across this situation and share your experiences. Thanks, Bhushan
Bhushan Kalla

It is not recomended and it is not a good practice…
You are not sure how many places it is using servername… Best approach would be drop the subscription before upgrading the new subscriptions and create the new subscription with an option stating schema and data exists at the subscriber…
or you can create the subscription with keep the existing data so that it will not be deleted when applying snapshot…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>