The more and more I work with replication the more and more I run into weird errors like this one.
SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name. (Replication.Utilities).
You might be thinking to yourself that you had a typo in the server name, but no, after checking the server name it matches what you can connect with.
First off a bit of background. This is a test server that I originally built out when I first installed SQL Server 2017, so I simply named the server SQL2017. Then later I migrated that server to a domain and it become known as SQL2017.Stedman.us, and installed SQL 2019 as a named instance. So when I connect to it, it is SQL2017.Stedman.us\sql2019, but internally it is known as SQL2017\SQL2019. that mismatch in the names is what is throwing off replication.
Using SELECT @@servername shows the old name.
So how do we fix it? Well we just need to tell SQL Server that the server name is no longer SQL2017 and it is SQL2017.Stedman.us.
Below we use sp_DROPSERVER and sp_ADDSERVER to drop the old name and add the new name.
After the rename we need to restart the SQL Server process for the changes to appear, then we check the @@Servername again and see that it is set correctly.
After that change, replication initializes just fine.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!