SQL Server 2017 CU 9 Whats New

This is a bit late, but below are the changes detected between CU 8 and CU 9:

The view sys.dm_hadr_availability_replica_states has a new column called write_lease_remaining_ticks

The Microsoft documentation doesn’t mention this column yet, but the below new messages do give an idea on why it has been added.

The following messages have been added:

Message ID	Severity	Is Event Logged	Text
47115	16	No	The ‘%ls’ option is not valid for WSFC availability group ‘%.*ls’. Remove the option or set the ‘CLUSTER_TYPE’ option to a different value, then retry the operation.
47116	16	No	The external lease cannot be set on availability group ‘%.*ls’. External Lease updates are not enabled for this availability group.
47117	16	No	The ‘%ls’ option must be specified with a valid time value when updating the Availability Group’s external write lease status on availability group ‘%.*ls’.
47118	16	No	The ‘%ls’ option must be specified with a valid value when updating the external lease status on availability group ‘%.*ls’.
47119	16	No	The current write lease of the availability group ‘%.*ls’ is still valid. The lease expiration time cannot be set to an earlier time than its current value.

The following Module changes occurred:

sys.dm_hadr_availability_replica_states now includes a reference to the new write_lease_remaining_ticks

sys.sp_vupgrade_replsecurity_metadata has moved away from using the legacy dbo.sysdatabases and now uses sys.databases and excludes database snapshots from its check

New

Old

CREATE VIEW sys.dm_hadr_availability_replica_states AS
SELECT
ars.replica_id,
ars.group_id,
ars.is_local,
ars.role,
ars.role_desc,
ars.operational_state,
ars.operational_state_desc,
ars.connected_state,
ars.connected_state_desc,
recovery_health = CASE
WHEN dbs.database_state_aggr IS NULL THEN NULL
WHEN ars.configured_database_count > dbs.database_count THEN CAST (0 AS TINYINT) — ONLINE_IN_PROGRESS
WHEN dbs.database_state_aggr = 0 THEN CAST (1 AS TINYINT) — ONLINE
ELSE CAST (0 AS TINYINT) END, — ONLINE_IN_PROGRESS
recovery_health_desc = CASE
WHEN dbs.database_state_aggr IS NULL THEN CAST (NULL AS NVARCHAR(60))
WHEN ars.configured_database_count > dbs.database_count THEN CAST (‘ONLINE_IN_PROGRESS’ AS NVARCHAR(60))
WHEN dbs.database_state_aggr = 0 THEN CAST (‘ONLINE’ AS NVARCHAR(60))
ELSE CAST (‘ONLINE_IN_PROGRESS’ AS NVARCHAR(60)) END,
synchronization_health = CASE
WHEN (war.availability_mode = 4 AND ars.connected_state = 1) THEN CAST (2 AS TINYINT) — Configuration-only, always healthy when connected
WHEN dbs.synchronization_health_aggr IS NULL THEN CAST (0 AS TINYINT) — NOT_HEALTHY
WHEN ars.configured_database_count > dbs.database_count THEN CAST (0 AS TINYINT) — NOT_HEALTHY (one or more DBs not joined)
ELSE CAST (dbs.synchronization_health_aggr AS TINYINT) END,
synchronization_health_desc = CASE
WHEN (war.availability_mode = 4 AND ars.connected_state = 1) THEN CAST (‘HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr IS NULL THEN CAST (‘NOT_HEALTHY’ AS NVARCHAR(60))
WHEN ars.configured_database_count > dbs.database_count THEN CAST (‘NOT_HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr = 2 THEN CAST (‘HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr = 1 THEN CAST (‘PARTIALLY_HEALTHY’ AS NVARCHAR(60))
ELSE CAST (‘NOT_HEALTHY’ AS NVARCHAR(60)) END,
ars.last_connect_error_number,
ars.last_connect_error_description,
ars.last_connect_error_timestamp,
ars.write_lease_remaining_ticks
FROM
sys.dm_hadr_internal_availability_replica_states ars
LEFT OUTER JOIN
(
SELECT
replica_id,
database_count = COUNT (*),
synchronization_health_aggr = MIN (synchronization_health),
database_state_aggr = MAX (database_state)
FROM
sys.dm_hadr_database_replica_states
GROUP BY replica_id
)
AS dbs
ON (ars.real_replica_id = dbs.replica_id)
LEFT OUTER JOIN
(
SELECT
ag_replica_id,
availability_mode
FROM
sys.dm_hadr_internal_wsfc_ag_replicas
)
AS war
ON (ars.real_replica_id = war.ag_replica_id)

CREATE VIEW sys.dm_hadr_availability_replica_states AS
SELECT
ars.replica_id,
ars.group_id,
ars.is_local,
ars.role,
ars.role_desc,
ars.operational_state,
ars.operational_state_desc,
ars.connected_state,
ars.connected_state_desc,
recovery_health = CASE
WHEN dbs.database_state_aggr IS NULL THEN NULL
WHEN ars.configured_database_count > dbs.database_count THEN CAST (0 AS TINYINT) — ONLINE_IN_PROGRESS
WHEN dbs.database_state_aggr = 0 THEN CAST (1 AS TINYINT) — ONLINE
ELSE CAST (0 AS TINYINT) END, — ONLINE_IN_PROGRESS
recovery_health_desc = CASE
WHEN dbs.database_state_aggr IS NULL THEN CAST (NULL AS NVARCHAR(60))
WHEN ars.configured_database_count > dbs.database_count THEN CAST (‘ONLINE_IN_PROGRESS’ AS NVARCHAR(60))
WHEN dbs.database_state_aggr = 0 THEN CAST (‘ONLINE’ AS NVARCHAR(60))
ELSE CAST (‘ONLINE_IN_PROGRESS’ AS NVARCHAR(60)) END,
synchronization_health = CASE
WHEN (war.availability_mode = 4 AND ars.connected_state = 1) THEN CAST (2 AS TINYINT) — Configuration-only, always healthy when connected
WHEN dbs.synchronization_health_aggr IS NULL THEN CAST (0 AS TINYINT) — NOT_HEALTHY
WHEN ars.configured_database_count > dbs.database_count THEN CAST (0 AS TINYINT) — NOT_HEALTHY (one or more DBs not joined)
ELSE CAST (dbs.synchronization_health_aggr AS TINYINT) END,
synchronization_health_desc = CASE
WHEN (war.availability_mode = 4 AND ars.connected_state = 1) THEN CAST (‘HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr IS NULL THEN CAST (‘NOT_HEALTHY’ AS NVARCHAR(60))
WHEN ars.configured_database_count > dbs.database_count THEN CAST (‘NOT_HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr = 2 THEN CAST (‘HEALTHY’ AS NVARCHAR(60))
WHEN dbs.synchronization_health_aggr = 1 THEN CAST (‘PARTIALLY_HEALTHY’ AS NVARCHAR(60))
ELSE CAST (‘NOT_HEALTHY’ AS NVARCHAR(60)) END,
ars.last_connect_error_number,
ars.last_connect_error_description,
ars.last_connect_error_timestamp
FROM
sys.dm_hadr_internal_availability_replica_states ars
LEFT OUTER JOIN
(
SELECT
replica_id,
database_count = COUNT (*),
synchronization_health_aggr = MIN (synchronization_health),
database_state_aggr = MAX (database_state)
FROM
sys.dm_hadr_database_replica_states
GROUP BY replica_id
)
AS dbs
ON (ars.real_replica_id = dbs.replica_id)
LEFT OUTER JOIN
(
SELECT
ag_replica_id,
availability_mode
FROM
sys.dm_hadr_internal_wsfc_ag_replicas
)
AS war
ON (ars.real_replica_id = war.ag_replica_id)

CREATE PROCEDURE sys.sp_vupgrade_replsecurity_metadata
AS
BEGIN
SET NOCOUNT ON

DECLARE @retcode int,
@statement nvarchar(4000),
@database nvarchar(512),
@has_dbaccess bit,
@is_distributiondb bit,
@is_subscriberdb bit,
@is_upgraded bit
,@agentname nvarchar(100)
,@description nvarchar(100)
,@category_name sysname
,@dbname sysname

— Security Check
IF ISNULL(IS_SRVROLEMEMBER(‘sysadmin’), 0) = 0
BEGIN
— “Only members of the sysadmin fixed server role can perform this operation.”
RAISERROR(21089,16,-1)
RETURN 1
END

IF LOWER(DB_NAME()) != N’master’
BEGIN
— “sp_vupgrade_replsecurity_metadata can only be executed in the master database.”
RAISERROR (21482, 16, -1, ‘sp_vupgrade_replsecurity_metadata’, ‘master’)
RETURN 1
END

— make sure user has already upgraded msdb
IF NOT EXISTS(SELECT *
FROM msdb.sys.columns
WHERE object_id = OBJECT_ID(N’msdb.dbo.sysjobsteps’)
AND name = N’step_uid’)
BEGIN
— “The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s.”
RAISERROR(21450, 16, -1, ‘security meta-data’, ‘all’, ‘msdb database’, ‘sp_vupgrade_replsecurity_metadata’)
RETURN 1
END

BEGIN TRANSACTION
SAVE TRANSACTION tr_upgrade_security_meta

DECLARE cursorDatabases CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT HAS_DBACCESS(name),
sys.fn_MSrepl_isdistdb(name),
N'[‘ + replace(name, N’]’, N’]]’) + N’]’,
name
FROM master.sys.databases
WHERE name NOT IN ( N’master’ COLLATE DATABASE_DEFAULT,
N’tempdb’ COLLATE DATABASE_DEFAULT,
N’msdb’ COLLATE DATABASE_DEFAULT,
N’model’ COLLATE DATABASE_DEFAULT
) and source_database_id is null — exclude DB snapshots
FOR READ ONLY

OPEN cursorDatabases

FETCH cursorDatabases INTO @has_dbaccess, @is_distributiondb, @database, @dbname

WHILE @@FETCH_STATUS != -1
BEGIN
IF @has_dbaccess = 1
BEGIN
— if this is a distribution db then upgrade dist meta-data
IF @is_distributiondb = 1
BEGIN
SELECT @statement = N’SELECT @is_upgraded = 0 ‘ +
N’IF EXISTS(SELECT * ‘+
N’ FROM ‘ + @database + N’.sys.columns ‘ +
N’ WHERE name = N”job_step_uid”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_upgraded = 1 ‘ +
N’END’

EXEC @retcode = sys.sp_executesql @statement = @statement,
@params = N’@is_upgraded bit OUTPUT’,
@is_upgraded = @is_upgraded OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO

IF @is_upgraded = 1
BEGIN
SELECT @statement = @database + N’.[sys].[sp_MSupgrade_distdb_security_metadata]’

EXEC @retcode = @statement
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
ELSE
BEGIN
— “The replication %s could not be upgraded for %s database(s). Please ensure that %s is upgraded and re-execute %s.”
RAISERROR(21450, 10, -1, ‘distributor security meta-data’, @database, @database, ‘sp_vupgrade_replsecurity_metadata’) WITH NOWAIT
END
—
— Add replmonitor refresh job for this distribution database
—
select @agentname = formatmessage (20811, @dbname)
select @description = @agentname
,@statement = N’exec dbo.sp_replmonitorrefreshjob ‘
if not exists (select * from msdb.dbo.sysjobs_view where name = @agentname collate database_default and
upper(originating_server collate database_default) = upper(convert(sysname, SERVERPROPERTY(‘ServerName’)))
and master_server = 0)
BEGIN
select @category_name = name FROM msdb.dbo.syscategories where category_id = 18
EXECUTE @retcode = msdb.sys.sp_MSadd_repl_job @agentname,
@subsystem = ‘TSQL’,
@server = @@SERVERNAME,
@databasename = @dbname,
@description = @description,
@freqtype = 64, — continuous
@freqsubtype = 0, — unused
@freqsubinterval = 0, — unused
@command = @statement,
@retryattempts = 0,
@enabled = 0,
@loghistcompletionlevel = 0,
@category_name = @category_name
if @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
END

SELECT @statement = N’SELECT @is_subscriberdb = 0 ‘ +
N’IF EXISTS(SELECT * ‘ +
N’ FROM ‘ + @database + N’.sys.objects ‘ +
N’ WHERE name = N”MSsubscription_properties”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_subscriberdb = 1 ‘ +
N’END ‘ +
N’SELECT @is_upgraded = 0 ‘ +
N’IF EXISTS(SELECT * ‘ +
N’ FROM ‘ + @database + N’.sys.columns ‘ +
N’ WHERE name = N”job_step_uid”) ‘ +
N’BEGIN ‘ +
N’ SELECT @is_upgraded = 1 ‘ +
N’END’

EXEC @retcode = sys.sp_executesql @statement = @statement,
@params = N’@is_subscriberdb bit OUTPUT, @is_upgraded bit OUTPUT’,
@is_subscriberdb = @is_subscriberdb OUTPUT,
@is_upgraded = @is_upgraded OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO

IF @is_subscriberdb = 1
BEGIN
IF @is_upgraded = 1
BEGIN
— always assume this is subscriber db and attempt upgrade sub meta-data
SELECT @statement = @database + N’.[sys].[sp_MSupgrade_subdb_security_metadata]’

FETCH cursorDatabases INTO @has_dbaccess, @is_distributiondb, @database, @dbname
END

CLOSE cursorDatabases
DEALLOCATE cursorDatabases

COMMIT TRANSACTION tr_upgrade_security_meta

RETURN 0
UNDO:
ROLLBACK TRANSACTION tr_upgrade_security_meta
COMMIT TRANSACTION

RETURN 1
END