name provider_string LISTENER,54321 NULL LISTENER addr=tcp:LISTENER,54321After the upgrade to SQL 2022 a couple of behaviors where observed:
The first one is adding an article to a publication of a subscriber using the old format won’t generate a snapshot: A snapshot was not generated because no subscription needed initialization
The second one is creating a new publication for an existing subscriber in the old format will get this error:
'SQL1' is not defined as a Subscriber for 'SQL2'.
Could not update the distribution database subscription table. The subscription status could not be changed.
The subscription could not be created.
The subscription could not be found.
Changed database context to 'dummy'. (Microsoft SQL Server, Error: 20032)
Or
Cannot insert the value NULL into column ‘freq_subday_interval’, table
‘distribution.dbo.MSrepl_agent_jobs’; column doen not allow nulls. UPDATE fails.
Could not update the distribution database subscription table. The subscription status could not be changed.
The subscription could not be created.
The subscription could not be found.
Changed database context to 'dummy'. (Microsoft SQL Server, Error: 20032)
The workaround is to enable TF 15005 in both the publisher and distributor as well as creating aliases for the listener subscribers with non default port numbers, example LISTENER3
If publisher and distributor are AG then aliases for the listener publisher and listener distributor are also needed, LISTENER1 and LISTENER2, respectively, in both 32 and 64-bit SQL Native Client, as shown below, in a PowerShell script
$aliases = @(
@{name = 'LISTENER1'; value = 'DBMSSOCN,LISTENER1,54321'}
@{name = 'LISTENER2'; value = 'DBMSSOCN,LISTENER2,54321'}
@{name = 'LISTENER3'; value = 'DBMSSOCN,LISTENER3,54321'}
)
$registryPaths = @(
'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
'HKLM:\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo'
)
foreach ($alias in $aliases) {
foreach ($registryPath in $registryPaths) {
if (-not (Test-Path $registryPath)) {
New-Item -Path $registryPath -Force
}
New-ItemProperty -Path $registryPath -Name $alias.name `
-Value $alias.value -PropertyType String -Force
}
}
Then you should be able to add the article or to create the subscription without specifying the port number
