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

[sajugovind]

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

 

Code: [Select]

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

Code: [Select]

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



Output of select @@servername



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

Code: [Select]

sp_dropserver ‘old_name’
go
sp_addserver ‘new_name’,'local’
go
In my case I have executed following query

Code: [Select]

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.

Code: [Select]

net stop mssqlserver
net start mssqlserver


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



Output of select @@servername


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