Catch-22 Errors in Configure Replication Wizard on a Failover Cluster Named Instance
0
votes
1
answer
224
views
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.
Asked by Inphinite Phractals
(23 rep)
Aug 16, 2022, 09:37 PM
Last activity: Aug 17, 2022, 01:08 AM
Last activity: Aug 17, 2022, 01:08 AM