Sample Header Ad - 728x90

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