SQL Server replication requires the actual server name to make a connection to the server
I was setting up Replication between two hyper-v images. The moment I started off with node 1, I was greeted with an error. I was trying to create a distribution database on node 1, but before I could connect, I got this error:
If you observe the error, we can figure out that even though my machine name is REPLDBMIRRNode1, it is trying to connect to WIN2K8R2.
This reminds me that I renamed by server from WIN2K8R2 to REPLDBMIRRNode1. But this change did not take place in the internals of SQL Server :(. I ran the following statement and indeed, my instance still believes that the server name is WIN2K8R2
So, how do you fix this?
I ran the following statement to drop the old server information and add the new one. A restart of the instance is also required.
--select @@SERVERNAME sp_dropserver 'WIN2K8R2\SQL2K8R2' GO sp_addserver 'REPLDBMIRRNode1\SQL2K8R2', 'local'
After running the above and restarting the instance; I executed SELECT @@SERVERNAME to get the following output:
Job done. I could now begin setting up the distribution database.