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

Question

i have a virtual machine that contains sql server that contains a replication ... the publisher and subscriber are on this same VM ... i've cloned this production VM for testing purposes, then i dropped the replication (on the testing) then recreate it again, but the replication won't start and when i tried to right click the publication then choose properties, i got the following error: 

 

SQL server is unable to connect to server 'testing'

SQL server replication requires the actual server name to make a connection to the server.
connections through a server alias, IP address, or any other alternate name are not supported Specify the actual server name, 'production' (Replication. Utilities)

 

I'm sure i've changed the computer name of the new VM to "testing", but why i'm getting this error?! ... and how can i fix it  ?!!

 

Answer 1

Can you do this?

 

select @@ServerName

 

Is this the same as what you get when you go to a command prompt and type

hostname

?

If not do this:

 

EXEC  Sp_dropserver   @@ServerName

GO
EXEC  Sp_addserver 'newservername' ,  'local'

GO

Then restart SQL Server Service and try again.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

thanks Hilary,

the result of select @@ServerName is not the same as cmd hostname

but when i tried to run 

 

EXEC  Sp_dropserver   @@ServerName,'droplogins'
GO

EXEC  Sp_addserver 'testing' ,  'local'
GO

 

i got the following error:

 

Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 31

Cannot drop server 'production' because it is used as a Publisher in replication.

Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 74

The server 'testing' already exists.

 

i tried to run exec sp_droppublication  @publication ='all' and it was completed successfully, but when i tried to run the above script i'm still getting the same error.

 

Answer 3

You will need to disable this machine as a distributor and then try again with the sp_dropserver command. Right click on the replication folder to do this.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Hilary,

what if the select @@server and C:>Hostname both return the same server name but I still get the error?  We didn't make any name changes.  The one thing we did recently was changing the IP address scheme from static to DHCP for that server.   I connet to that server by name and it is the default instance.  We have no name instance.

when I am in SQL mgmt studio, right click to check the snapshot status or right click to select properties, i get the same error.  For me to do any publishing, I need to do it with script.  For me to run the snap shot or check status, I will need to do it in Replication monitor or job agent.

Any thought?

od

---

Ocean Deep