SQL Server replication requires the actual server name to make a connection to the server

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:

1_SQL_Server_replication_requires_the_actual_server_name_to_make_connection_to_the_server

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

2_SQL_Server_replication_requires_the_actual_server_name_to_make_connection_to_the_server

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:

3_SQL_Server_replication_requires_the_actual_server_name_to_make_connection_to_the_server

Job done. I could now begin setting up the distribution database.

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “SQL Server replication requires the actual server name to make a connection to the server”

  1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *