SQL Server replication requires the actual server name to make a connection to the server
Hi Friends,
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.
Thanks Amit .Just wanted to add a bit more to it .I observed that ,this not only happens becasue someone renamed the SQL Server , but also if the SQL Server no longer remains the local number .For example you will get similar error if you your @@servername returns NULL .If you run sp_helpserver you will notice your correct servername (I am not talking about server rename here as you have already covered it) , you will see your server name but the ID column will show you an integer other than 0 (which means local).
To correct it we need to follow the same steps as you have mentioned .Once done , an instance restart is required .
Cheers
Abhay