Don’t, don’t, don’t change the server name if you are using replication

Replication is a very old beast that has been developed over the years. Because it communicates to other machines in interesting ways it has some quirks.

One of those is the server names. We all know and love @@servername but that doesn’t have to be the name of the server. This means that if replication used that to try and to connect to another server it may fail. So they use serverproperty(‘servername’). This returns the servers real name.

The really awful thing is that in the code in some places @@servername is used and others serverproperty is used and cruically stored in tables in the distribution database the subscribers and the publishers. This means that if you change name of the server if the other tables aren’t changed which they are not then you will encounter odd problems. For instance not being able to script the replication setup.

Whilst you could possibly hack the system tables I strongly advise this with replication as you can get into all sorts of troubles and with upgrades you never know what you might screw up.

This is one of the reasons that providing a HA solution for your distributor is a far from easy task.

Therefore lesson learnt is do not change the names of servers in replication topologies unless you want to setup from scratch

Published 20 August 2010 21:02 by simonsabin
Filed under:

Comments

# Twitter Trackbacks for Don???t, don???t, don???t change the server name if you are using replication - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Don???t, don???t, don???t change the server name if you are using replication - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com

21 August 2010 11:46 by Dave Ballantyne

# re: Don’t, don’t, don’t change the server name if you are using replication

Our IT team once changed the IP-Address of a  stand by server to be the IP-address of the live server after it had died.  That was fun to recover from the doubly distributed data.

23 August 2010 10:12 by SimonS Blog on SQL Server Stuff

# Don’t, don’t, don’t change the server name if you are using replication

Replication is a very old beast that has been developed over the years. Because it communicates to other