New-SqlAvailabilityGroup and Set-SqlAvailabilityGroup need a new parameter to support TDS 8.0 #116
Description
Both of New-SqlAvailabilityGroup and Set-SqlAvailabilityGroup need a new parameter called ClusterConnectionOptions to support TDS 8.0
For Always on Availability group in SQL 2025+, to support TDS 8.0 and TLS 1.3, ClusterConnectionOptions was introduced to let Windows failover cluster connect to SQL server instance via ODBC.
ClusterConnectionOptions should be a string contains list of key value pairs.
Available Keys: Encrypt, TrustServerCertificate, HostNameInCertificate and ServerCertificate.
Detailed explanation of each key: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-availability-group-transact-sql?view=sql-server-ver17#cluster_connection_options
Examples: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-availability-group-transact-sql?view=sql-server-ver17#b-enforce-encryption-in-connections-to-an-availability-group
The corresponding SMO object is https://github.com/microsoft/sqlmanagementobjects/blob/main/src/Microsoft/SqlServer/Management/Smo/AvailabilityGroupBase.cs
- Search for
ClusterConnectionOptionsin the SMO class and you will find all the implementation. - Search for
ClusterConnectionOptionsConstantsand you will find the available values for keys.
There are some constraints for those key-value pair combination. I do not think Powershell should contains the logic to check it. The ODBC driver will throw error if the key or value is incorrect and SMO will not accept wrong value.
Both of New-SqlAvailabilityGroup and Set-SqlAvailabilityGroup already contains 3 parameters called Encrypt, TrustServerCertificate and HostNameInCertificate, to distinguish them from the ClusterConnectionOptions, need to call out in document explicitly that these 3 parameters is used to connect to the SQL Server Instance to create/alter availability group. It is an one-time used value but the ClusterConnectionOptions will be kept as a registry value by WSFC and used continuously.