Sql-server – SQL Server : cannot add subscriber to publication

connectivityreplicationsql serversql-server-2008

I am trying to configure replication as per the official tutorial from Microsoft. In the section Create a subscription to the transactional publication, see step 5.

The name of the subscriber instance is not displayed, so I click Add Subscriber, and then click Add SQL Server Subscriber from the drop-down list. Then the Connect to Server dialog box appears. I enter the subscriber instance name, switch to SQL Server Authentication, fill out the sa username and password, and then click Connect.

After a few seconds, an error dialog appears that reads:

Cannot connect to DBSUB\SQLEXPRESS.

Additional information:

-> Failed to connect to server DBSUB\SQLEXPRESS. (Microsoft.SqlServer.ConnectionInfo)

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)

The thing is, the server IS very much available. I am able to ping DBSUB as well as RDP into it.

The SQL Server Agent is running.

The SQL Server instance is configured to allow remote connections. (RDP to DBSUB, log into DBSUB\SQLEXPRESS with SQL Server Management Studio on DBSUB (as opposed to SQL Server Management Studio on DBPUB), Server Properties, Connections page, Allow remote connections to this server is checked)

Strangely, intentionally entering the wrong name (DBSUB, DBSUB\BLAH, etc) produces a different error.

Also note that this is not an Express instance. It is an old name from an upgrade.

I would very much appreciate help solving this issue.

Thank you in advance,

Update:
I have configured Aliases as per this article (sorry it's behind a microsoft login). The instructions were incorrect; however, after learning a bit about Aliases, I was able to correctly set it up. Unfortunately, I get a new error that states that Aliases are not a solution to this issue.

Cannot connect to Subscriber.

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, 'DBSUB\SQLEXPRESS'. (Replication.Utilities)

I noticed the SQL Browser service was not running. I enabled it on both Publisher and Subscriber and connected to it through DBSUB\SQLEXPRESS and now it works.

Best Answer

As discussed in the comments earlier, the replication couldn't be established is likely due to the instance name, that was being upgraded to a new host, but using back the same instance name as previous.

This issue can be fixed without reinstalling the SQL server, but you will need about a minute or so of downtime.

The following steps can be found in this link here.

(Basically I am copying the steps directly from the link here)

Case 1: For a renamed computer that hosts a default instance of SQL Server

sp_dropserver <old_name>;  
GO  
sp_addserver <new_name>, local;  
GO

Case 2: For a renamed computer that hosts a named instance of SQL Server

sp_dropserver <old_name\instancename>;  
GO  
sp_addserver <new_name\instancename>, local;  
GO

For both cases, you have to restart your SQL server service afterwards.

Once done, then do a verification again by: 

SELECT @@SERVERNAME
Related Question