2

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.

  • I found a question that is similar to mine, not a duplicate. There are ideas to help me debug. Hopefully it solves my issues, but I'm not holding my breath. If anyone reads this comment and has a suggestion please still do provide your suggestion!! I can't check the firewall rules right now, but I'm going to leave this here for later (tomorrow): dba.stackexchange.com/questions/48082/… – Inversus Jul 5 '18 at 22:51
  • 1
    You mentioned about this is an upgrade. Was the servername changed? Could you run the following query on your instance to see its servername? "SELECT @@SERVERNAME . From the error message, it seems that it can't recognize the instance name. And for replication, it has to be the exact name. – Yoshiaki Jul 6 '18 at 0:19
  • @Yoshiaki thanks! Indeed it was a different servername. Can this be fixed or should I reinstall sql server? – Inversus Jul 6 '18 at 17:50
  • 1
    Sorry for the late reply. In that case, let me reply in a better format in the answer column. – Yoshiaki Jul 9 '18 at 4:44
1

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
  • I tried this and successfully renamed the server; however, I still encounter the same issue.. the answer you provided is useful and was something that should be done, and thank you. Please stay with me as there is more to do to solve this. – Inversus Jul 9 '18 at 14:04
  • Do you mean you are still seeing the same exact issues? And before the upgrade, was there any replication setup? – Yoshiaki Jul 9 '18 at 15:56
  • Yes it's still sending the same error popup after the same amount of time trying to connect. And no, before the upgrade there was no replication set up – Inversus Jul 9 '18 at 16:51
  • I've edited the answer to include the solution. I'll accept the answer once the edits have been accepted by peer review. – Inversus Jul 10 '18 at 15:39
  • My edits to the answer were rejected with the comments that I should add a separate answer. Sorry – Inversus Jul 10 '18 at 18:11
0

In addition to renaming, it was necessary to start the SQL Server Browser Service from the Configuration Manager.

  • Oh... I didn't know this step was needed as it works for me without restarting the SQL server browser as well... – Yoshiaki Jul 11 '18 at 23:59
  • @Yoshiaki So many SQL services lol.. I put it on automatic with the others: SQL Server, SQL Server Agent, probably more lol. Anyway, if you update your answer to include this I'll change the accepted answer to yours. You deserve the answer for helping. Thanks! – Inversus Jul 12 '18 at 14:22
  • Ah is okay actually. Just that I am quite surprised that the SQL server browser requires a restart in this situation. No worries..! – Yoshiaki Jul 14 '18 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.