Catch-22 Errors in Configure Replication Wizard on a Failover Cluster Named Instance

Question

I'm attempting to setup replication on a new SQL 2019 install but I'm encountering a catch-22 in errors.

I have two (2) named instances setup side by side, each setup as a cluster role. The cluster role names, we'll call them:

toolsdb
catdb

As installed, if I run on toolsdb and catdb, respectively:

select @@SERVERNAME as ServerName, SERVERPROPERTY('ServerName') as ServerProp

...they return:

ServerName	ServerProp
TOOLSDB\TOOLS	TOOLSDB\TOOLS

ServerName	ServerProp
CATDB\CAT	CATDB\CAT

The issue I'm encountering happens on either instance, but we'll use toolsdb for now. When I right-click on Replication > Configure Distribution, I get:

===================================

SQL Server is unable to connect to server 'toolsdb'. (Configure Distribution Wizard)

------------------------------ For help, click: https://go.microsoft.com:80/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18410.0&EvtSrc=Microsoft.SqlServer.Management.UI.ConfigureWizardErrorSR&EvtID=CantConnect&LinkId=20476

===================================

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, ''. (Replication.Utilities)

------------------------------ Program Location:

at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn) at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open()
at Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.PrepareToShow()

The resolution to this was to have the value that the @@SERVERNAME function returns, match the cluster role name, using:

sp_dropserver 'TOOLSDB\TOOLS'
go

sp_addserver 'TOOLSDB', LOCAL
go

Once I restart the role/service, and run this again:

select @@SERVERNAME as ServerName, SERVERPROPERTY('ServerName') as ServerProp

...it now returns as expected:

ServerName	ServerProp
TOOLSDB	TOOLSDB\TOOLS

...and I was able to proceed with replication setup. However, once I went through the Configure Distribution Wizard steps to the end and clicked Finish, the "Configuring the Distributor" action errored out with:

===================================

SQL Server could not configure 'toolsdb' as a Distributor. (Microsoft.SqlServer.ConnectionInfo)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------ Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn) at Microsoft.SqlServer.Replication.ReplicationServer.InstallDistributor(String password, DistributionDatabase distributionDB) at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.InstallDistributor(WizardInputs inputs, Boolean bScripting) at Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.InstallDistributor(Boolean& anyExceptions, Boolean bScripting, ApplicationException& outerEx, StringBuilder command)

===================================

Could not connect to server 'TOOLSDB\TOOLS' because 'distributor_admin' is not defined as a remote login at the server. Verify that you have specified the correct login name. . Changed database context to 'master'. (.Net SqlClient Data Provider)

------------------------------ For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18483-database-engine-error

------------------------------ Server Name: toolsdb Error Number: 18483 Severity: 14 State: 1 Line Number: 1

------------------------------ Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

From the research I've read: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18483-database-engine-error

...it appears that the value that the @@SERVERNAME function returns needs to match the instance name, e.g.- TOOLSDB\TOOLS, but I cannot start the wizard with it set to this value.

Any ideas?

Thank you.

Answer 1

It sounds like you installed your SQL Server as a named instance called TOOLSDB\TOOLS. Replication requires that you are connected directly to the named instance (instead of the default name) in order to be able to configure it for that server, else the first error you received "SQL Server is unable to connect to server 'toolsdb'. (Configure Distribution Wizard)" will occur. (I personally think this may be a defect of the Replication Wizard in SSMS.)

To go from where you're currently at, I would put back the named instance in your list of servers via:

sp_dropserver 'TOOLSDB', LOCAL go

sp_addserver 'TOOLSDB\TOOLS' go

Then disconnect and reconnect to your instance with the full name as TOOLSDB\TOOLS in the SSMS Server name textbox. E.g:

Notice the full named instance in the above example is HUNTERTOP7\SQLEXPRESS but for you this would be TOOLSDB\TOOLS. After you connect to the full instance name, then the Replication Wizard should work for you.