Error: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.

By: Published: Feb 2nd, 2013 Category: 2008 Server, Microsoft Windows, MSSQL

Sometime while setting up MSSQL Replication you will receive following error at the time of connecting Slave or Secondary Database Server.

 

Detailed Error:
TITLE: Connect to Server
——————————

Cannot connect to failover-db.

——————————
ADDITIONAL INFORMATION:

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, ‘WIN-PNHS6NVSKPI’. (Replication.Utilities)

——————————
BUTTONS:

OK

error 300x85 Error: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.

 

Cause:

The problem mainly occurs when you change the computer name of the server after installation of MSSQL Server but even after reboot MSSQL uses the OLD Computer name as network name and the SQL Server instance name

Here,
OLD NAME of the Server was: WIN-PNHS6NVSKPI
Changed or New Name of the Server is: FAILOVER-DB

Solution.

1) Find Current Network Name and SQL Server Instance Name
First we need to check the Network name and the SQL Server instance name stored in the database. We can find it execute following commands in MSSQL Management Studio.

sp_helpserver
select @@servername

sp_helpserver: This Query or command shows the information of a particular or a replication server. This shows the server name, network name, identication number and collation name of the server.
select @@servername: This Query will return the server name on which the query is executed.

Following were the results which I recived clearly shows that the MSSQL didn’t accepted the new computer name which I have set i.e. FAILOVER-DB.

Output of sp_helpserver

error1 300x84 Error: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.

Output of select @@servername

error2 300x101 Error: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.

2) Change Server Instance Name and Network Name

Now to fix this issue we need change the Server Instance Name and Network Name. This can be done using Following Query

sp_dropserver ‘old_name’
go
sp_addserver ‘new_name’,’local’
go

In my case I have executed following query

sp_dropserver ‘WIN-PNHS6NVSKPI’
go
sp_addserver ‘FAILOVER-DB’,local
go

3) Restart MSSQL Server

Once the above commands get executed successfully; close the MSSQL Management Studio and restart MSSQL server using following commands in command prompt.

net stop mssqlserver
net start mssqlserver

error3 300x242 Error: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.

4) Verify the Changes

To verify if the name has been changed or not execute the queries mentioned in Step 1. I have recived following outputs

Output of sp_helpserver

error4 300x84 Error: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.

Output of select @@servername

error5 300x101 Error: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.

Thats it; now try to setup Replication of MSSQL and it won’t give that error again.

 Error: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name.

About 

Milind Koyande is a Project Manager and his job is to work with new technologies, specially Cloud Computing / Virtualization Technology. His past projects include Government Sector initiatives, Backup and Disaster Recovery Solutions.

Tags: , , , , , , , , ,

Leave a Reply

Subscribe to eITWebguru

© 2012 eITWebguru. All Rights Reserved.