Tonight I went into a virtual lab to setup Transactional Replication for demonstration purposes. Over time, the lab has been changed, altered and just about turned upside down, one way or another. Upon trying to call the wizard in SSMS, I received the following error.
This error is fairly common with replication and trying to administer or setup publications/subscriptions. The problem is written out pretty well. You must connect to the true name of the instance in order to manipulate it. This is primarily due to replication relying on the instance names in such a highly critical manner. In the case of the error above, the name of the server is actually NODE1 and the instance is NODE1SHAREPOINT2013. So where is it getting the odd default vmware name that is shown in the error?
When you rename a server, it does update SQL Server and allow everything to function. However, some things do not get updated at times and some things may stop working completely. Such is the case in the above situation.
As shown below, the first step to resolving this problem is to check the sys.servers catalog to verify what name is stored
In the case above, the name that is incorrect is shown.
Another verification should be performed by simply executing a HOSTNAME in command prompt to ensure the machine name is actually NODE1
To resolve this issue, you need to update the sys.servers catalog to reflect the name of the instance accurately. To do this, use the sp_dropserver and sp_addserver procedures.
Connecting to the instance, first execute sp_dropserver by copy/pasting the incorrect name from the previous query and passing it in as the parameter for server name.
Once the sp_dropserver is successful, execute sp_addserver with the new name. This will place the correct entry of the instance name back into sys.servers. Ensure to specify the local value for the @local parameter.
Check to ensure the sys.servers entry is accurate and matches to the machine name that resolves to active directory.
As shown below, starting the replication wizard now moves on without error. It is possible that this change will require a SQL Server services restart. Be sure to follow your internal procedures for any downtime that may happen due to that need. Or in short, do not run out and restart anything without making sure it is actually ok to restart it.