Replication is a technology that has been around for a long time in the world of SQL Server. However, with the introduction of SQL Server AlwaysOn in SQL Server 2012, replication seems to have taken a backseat in discussions. Despite this, replication still remains a valuable tool for those who require partial synchronization of selected tables or articles in a large database environment.

In a recent attempt to configure replication for one of my upcoming sessions, I encountered an error while trying to “Configure Distribution” and “Create a New Publication”. The error message stated that SQL Server was unable to connect to the server and required the actual server name to establish a connection. This error message mentioned two different names: “SQL16NODEB\SQL2014” and “MyServer”.

To troubleshoot this issue, I decided to capture a profiler trace on the SQL instance to understand what was causing the error. I discovered two queries that were being executed:

SELECT SERVERPROPERTY(N'servername')
SELECT @@SERVERNAME

When I ran these queries individually in SQL Server Management Studio (SSMS), I noticed that the output was different:

SERVERPROPERTY(N'servername') = 'SQL16NODEB\SQL2014'
@@SERVERNAME = 'MyServer'

Upon further investigation, I realized that I had recently changed my server name for another demo, which was causing the problem. To resolve this, I executed the following commands:

DECLARE @actualname NVARCHAR(100)
DECLARE @currentname NVARCHAR(100)

SELECT @actualname = CONVERT(NVARCHAR(100), SERVERPROPERTY(N'servername'))
SELECT @currentname = @@SERVERNAME

EXEC sp_dropserver @currentname
EXEC sp_addserver @actualname, local

After running these commands, I restarted the SQL Server service. Finally, I verified that the server name had been successfully changed by executing the following queries:

SELECT SERVERPROPERTY(N'servername')
SELECT @@SERVERNAME

Have you ever encountered a similar issue with replication? If so, I would love to hear about your experience and any alternative solutions you may have found. Let’s learn from each other!