Multi-Subnet Log Reader Agent

To connect the replication log reader agent to a multi-subnet Always On publisher you must add the parameter -MultiSubnetFailover 1 to the job step as documented here. Example: 

-Publisher [SQLPUB1] -PublisherDB [dummy] -Distributor [SQLDIST] -DistributorSecurityMode 1 -Continuous -MultiSubnetFailover 1 

However the log reader fails with error below when the publisher SQLPUB1 and distributor SQLDIST are in different subnets

TCP Provider: The wait operation timed out. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) Get help: http://help/MSSQL_REPL22037 

A trace shows that the log reader agent executes the following on the publisher:

declare @p3 nvarchar(128)
set @p3=NULL
declare @p4 nvarchar(128)
set @p4=NULL
exec "master"."sys"."sp_executesql";1 N'exec master.sys.sp_helpdistributor
@distributor = @p1 output, @distribdb = @p2 output',N'@p1 sysname output, 
@p2 sysname output',@p3 output,@p4 output
select @p3, @p4

and it will fail with a similar error

OLE DB provider "MSOLEDBSQL" for linked server "repl_distributor" returned message "Login timeout expired". OLE DB provider "MSOLEDBSQL" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". Msg 258, Level 16, State 1, Line 0 TCP Provider: The wait operation timed out. 

As you can see, the log reader is accessing the distributor from the publisher using the link server

The workaround is to add the MultiSubnetFailover = ‘Yes’ to link server repl_distributor using sp_serveroption which as of this writing is not documented

USE master;
GO
EXEC sp_serveroption
    @server = N'repl_distributor',
    @optname = N'provider string',
    @optvalue = N'MultiSubnetFailover=Yes';
GO
 

Automating Major SQL Version Upgrade with DBATools

You can easily automate a major version upgrade, for example to SQL 2022, using Install-DbaInstance from the DBATools PowerShell module. As of this writing, there was not a documented example, so I played around with it and figured it out. 

If you use the configuration parameter option

$config = @{
    ACTION="Upgrade"
}

You will get this error: The setting 'FEATURES' is not allowed when the value of setting 'ACTION' is 'Upgrade'. The workaround is to use a configuration file to override the FEATURES option that the configuration parameter adds by default.  The configuration file must have a least these three options: ACTION, INSTANCENAME, and QUIET

The following example does a remote upgrade of a named instance to SQL 2022 

$options = '
[OPTIONS]
ACTION="Upgrade"
INSTANCENAME="instance_name"
QUIET="True"
'
Set-Content -Path 'c:\temp\config.ini' -Value $options
 
$paramsUpgrade = @{
    ComputerName      = 'computer_name'
    Version           = '2022'
    Path              = 'sql_install_path’
    UpdateSourcePath  = 'sql_cu_path’
    ConfigurationFile = 'c:\temp\config.ini'
    Restart           = $true
    Credential        = Get-Credential
    Confirm           = $false
}
Install-DbaInstance @paramsUpgrade