Microsoft loves PaaS Yeah Nah

Microsoft sure does push the cloud and why not it’s a big money earner for them.

These days lots of organizations are looking for cloud first solutions as it’s the next big thing and it theoretically will save them money, its robust etc.

Ok cool, so lets look at Microsoft’s all products page.

All Products page 2019

Can you spot how many of Microsoft’s latest key business products ‘support’ use of Azure SQL Database or Azure SQL Database managed instance for it’s back-end?

  • Microsoft Skype for Business Server 2019 – no
  • Microsoft Dynamics CRM 2016 – no
  • Microsoft Dynamics 365 (on-premises / IaaS) – no
  • Microsoft Dynamics GP 2018 – no
  • Microsoft SharePoint Server 2019 – no
  • Microsoft System Center 2019 – no
  • ….

Wow, so to me this means either Microsoft’s products teams:

  • Don’t have the expertise to make use of PaaS, or
  • Can’t make it work, or
  • Didn’t get the memo re using the cloud, or
  • Don’t trust it, or
  • Just don’t know?

So when your boss or client says, let lift to the cloud and hey what about using Azure SQL Database or Azure SQL Database managed instance as a backed for our latest Microsoft products, you can respond Yeah Nah.

Don’t pay for SQL Server Licenses if you don’t have to

Let’s face it SQL Server licensing is expensive and companies will jump at any opportunity to reduce costs where they can.

Every dollar counts

SQL Server licenses are actually bundled with some products!

What you say.

Yes true, for example, System Center Configuration Manager (SCCM) comes with a free SQL Server Standard Edition license. The catch is that if a database for any additional Microsoft or third-party product shares the SQL Server, you must have a separate license for that SQL Server instance.

Wow, so why would you ever host your SCCM databases on a shared instance. It just doesn’t make sense, unless you really need some enterprise features like Transparent Data Encryption (TDE) or you have a super complex environment.

Developer Edition became free with SQL Server 2014, so it’s a no brainier to use that for non-Production environments. Even pre-SQL Server 2014 you could have looked to use the much cheaper Developer Edition to help reduce costs.

So I’ll start a list and as it grows so can your potential cost savings:

Importing Data Migration Assistant JSON Findings

When using the Data Migration Assistant you’ll have an option to export the findings as CSV or JSON.

The following provides a guide to importing the JSON data into a database table for you to review.

Firstly, create a table to hold the information using:

CREATE TABLE DMA_Findings
(
 [Project_Status] nvarchar(128)
,[Project_Name] nvarchar(128)
,[Project_SourcePlatform] nvarchar(128)
,[Project_TargetPlatform] nvarchar(128)
,[ServerInstances_ServerName] nvarchar(128)
,[ServerInstances_Version] nvarchar(128)
,[ServerInstances_Status] nvarchar(128)
,[Databases_ServerName] nvarchar(128)
,[Databases_Name] nvarchar(128)
,[Databases_CompatibilityLevel] nvarchar(128)
,[Databases_SizeMB] decimal (20,2)
,[Databases_Status] nvarchar(128)
,[Databases_ServerVersion] nvarchar(128)
,[Databases_ServerEdition] nvarchar(128)
,[AssessmentRecommendations_CompatibilityLevel] nvarchar(128)
,[AssessmentRecommendations_Category] nvarchar(128)
,[AssessmentRecommendations_Severity] nvarchar(128)
,[AssessmentRecommendations_ChangeCategory] nvarchar(128)
,[AssessmentRecommendations_RuleId] nvarchar(128)
,[AssessmentRecommendations_Title] nvarchar(160)
,[AssessmentRecommendations_Impact] nvarchar(4000)
,[AssessmentRecommendations_Recommendation] nvarchar(4000)
,[AssessmentRecommendations_MoreInfo] nvarchar(4000)
,[ImpactedObjects_Name] nvarchar(128)
,[ImpactedObjects_ObjectType] nvarchar(128)
,[ImpactedObjects_ImpactDetail] nvarchar(4000)
,[ImpactedObjects_SuggestedFixes] nvarchar(4000)
);

Next, import the data using:

INSERT INTO DMA_Findings( 
 [Project_Status]
,[Project_Name]
,[Project_SourcePlatform]
,[Project_TargetPlatform]
,[ServerInstances_ServerName]
,[ServerInstances_Version]
,[ServerInstances_Status]
,[Databases_ServerName]
,[Databases_Name]
,[Databases_CompatibilityLevel] 
,[Databases_SizeMB]
,[Databases_Status]
,[Databases_ServerVersion]
,[Databases_ServerEdition]
,[AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations_Category]
,[AssessmentRecommendations_Severity]
,[AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations_RuleId]
,[AssessmentRecommendations_Title]
,[AssessmentRecommendations_Impact]
,[AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations_MoreInfo]
,[ImpactedObjects_Name]
,[ImpactedObjects_ObjectType]
,[ImpactedObjects_ImpactDetail]
,[ImpactedObjects_SuggestedFixes]
)
SELECT 
 Project.[Status] AS [Project_Status]
,Project.[Name] AS [Project_Name]
,Project.[SourcePlatform] AS [Project_SourcePlatform] 
,Project.[TargetPlatform] AS [Project_TargetPlatform]
,[ServerInstances].[ServerName] AS [ServerInstances_ServerName] 
,[ServerInstances].[Version] AS [ServerInstances_Version]
,[ServerInstances].[Status] AS [ServerInstances_Status]
--,[ServerInstances].[AssessmentRecommendations] AS [ServerInstances_AssessmentRecommendations]
,[Databases].[ServerName] AS [Databases_ServerName]
,[Databases].[Name] AS [Databases_Name]
,[Databases].[CompatibilityLevel] AS [Databases_CompatibilityLevel] 
,[Databases].[SizeMB] AS [Databases_SizeMB]
,[Databases].[Status] AS [Databases_Status]
,[Databases].[ServerVersion] AS [Databases_ServerVersion] 
,[Databases].[ServerEdition] AS [Databases_ServerEdition]
,[AssessmentRecommendations].[CompatibilityLevel] AS [AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations].[Category] AS [AssessmentRecommendations_Category]
,[AssessmentRecommendations].[Severity] AS [AssessmentRecommendations_Severity]
,[AssessmentRecommendations].[ChangeCategory] AS [AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations].[RuleId] AS [AssessmentRecommendations_RuleId]
,[AssessmentRecommendations].[Title] AS [AssessmentRecommendations_Title]
,[AssessmentRecommendations].[Impact] AS [AssessmentRecommendations_Impact]
,[AssessmentRecommendations].[Recommendation] AS [AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations].[MoreInfo] AS [AssessmentRecommendations_MoreInfo]
,[ImpactedObjects].[Name] AS [ImpactedObjects_Name]
,[ImpactedObjects].[ObjectType] AS [ImpactedObjects_ObjectType]
,[ImpactedObjects].[ImpactDetail] AS [ImpactedObjects_ImpactDetail]
,[ImpactedObjects].[SuggestedFixes] AS [ImpactedObjects_SuggestedFixes]
FROM
OPENROWSET(BULK N'C:\pathtoyourJSONFile\yourfile.json', SINGLE_CLOB) AS json
OUTER APPLY OPENJSON(BulkColumn)
WITH (
 [Status] nvarchar(128)
,[Name] nvarchar(128)
,[SourcePlatform] nvarchar(128)
,[TargetPlatform] nvarchar(128)
,[Databases] nvarchar(MAX) AS JSON
,[ServerInstances] nvarchar(MAX) AS JSON
) AS [Project]
OUTER APPLY  OPENJSON([ServerInstances])
WITH (
 [ServerName] nvarchar(128)
,[Version] nvarchar(128)
,[Status] nvarchar(128)
--,[AssessmentRecommendations] nvarchar(4000)
) AS [ServerInstances]
OUTER APPLY  OPENJSON([Databases])
WITH (
 [ServerName] nvarchar(128)
,[Name] nvarchar(128)
,[CompatibilityLevel] nvarchar(128)
,[SizeMB] decimal (20,2)
,[Status] nvarchar(128)
,[ServerVersion] nvarchar(128)
,[ServerEdition] nvarchar(128)
,[AssessmentRecommendations] nvarchar(MAX) AS JSON
) AS [Databases]
OUTER APPLY OPENJSON([AssessmentRecommendations])
WITH (
 [CompatibilityLevel] nvarchar(128)
,[Category] nvarchar(128)
,[Severity] nvarchar(128)
,[ChangeCategory] nvarchar(128)
,[RuleId] nvarchar(128)
,[Title] nvarchar(160)
,[Impact] nvarchar(4000)
,[Recommendation] nvarchar(4000)
,[MoreInfo] nvarchar(4000)
,[ImpactedObjects] nvarchar(MAX) AS JSON
) AS [AssessmentRecommendations]
OUTER APPLY OPENJSON([ImpactedObjects])
WITH (
 [Name] nvarchar(128)
,[ObjectType] nvarchar(128)
,[ImpactDetail] nvarchar(4000)
,[SuggestedFixes] nvarchar(4000)
) AS [ImpactedObjects];

Now you’ll be able to query the findings:

SELECT * FROM [DMA_Findings];

An example of finding issues for a migration to SQL Server 2017 you could use a query like:

SELECT 
 [Databases_ServerName] AS Instance
,[Databases_ServerEdition] AS InstanceEdition
,[ServerInstances_Version] AS InstanceBuild
,[Project_SourcePlatform] AS SourcePlatform
,[Project_TargetPlatform] AS TargetPlatform
,[Databases_Name] AS DatabaseName
,[Databases_CompatibilityLevel] AS DatabaseCompatibilityLevel
,[AssessmentRecommendations_CompatibilityLevel] AS TargetCompatibilityLevel
,[Databases_SizeMB] AS Database_SizeMB
,[AssessmentRecommendations_Category]
,[AssessmentRecommendations_Severity]
,[AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations_RuleId]
,[AssessmentRecommendations_Title]
,[AssessmentRecommendations_Impact]
,[AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations_MoreInfo]
,[ImpactedObjects_Name]
,[ImpactedObjects_ObjectType]
,[ImpactedObjects_ImpactDetail]
FROM [DMA_Findings]
WHERE 1=1
AND [AssessmentRecommendations_CompatibilityLevel] = 'CompatLevel140'
ORDER BY [Databases_ServerName],[Databases_Name],[AssessmentRecommendations_Severity],[AssessmentRecommendations_ChangeCategory];

Database settings lost on restore or attach

If you’ve ever restored/attached a database and stuff doesn’t work e.g. Service Broker, yes some database settings are not automatically re-set when a database is restored or re-attached.

The following code will help generate these settings to apply after a database restore/attach.

Of course you’ll need to have run this query before you remove the database(s) and only run the appropriate generated code section(s) for the restored/attached database(s).

SELECT 'PRINT ''[' + [name] + '] is_trustworthy_on enabled''; ALTER DATABASE [' + name + '] SET TRUSTWORTHY ON;' AS [run_me] FROM sys.databases WHERE is_trustworthy_on = 1 AND [name] <> 'tempdb'
UNION
SELECT 'PRINT ''[' + [name] + '] is_broker_enabled enabled''; ALTER DATABASE [' + name + '] SET ENABLE_BROKER WITH NO_WAIT;' FROM sys.databases WHERE is_broker_enabled = 1 AND [name] <> 'tempdb'
UNION
SELECT 'PRINT ''[' + [name] + '] is_db_chaining_on enabled''; ALTER DATABASE [' + name + '] SET DB_CHAINING ON;' from sys.databases WHERE is_db_chaining_on = 1 AND [name] NOT IN ('master','tempdb')
UNION
SELECT 'PRINT ''[' + [name] + '] db_owner changed to [' + SUSER_SNAME(owner_sid) + ']''; ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO [' + SUSER_SNAME(owner_sid) + '];' from sys.databases WHERE owner_sid <> 0x01  AND [name] <> 'tempdb'

SQL Server Supported Operating Systems

Below is a quick reference guide of operating systems supported by the various SQL Server versions

Windows

Windows Server20082008 R220122012 R220162019
SQL Server 2005
SQL Server 2008✔ 7✔ 5
SQL Server 2008 R2✔ 6✔ 4
SQL Server 2012✔ 3✔ 2
SQL Server 2014✔ 9✔ 8✔ 1
SQL Server 2016✖ 10
SQL Server 2017
SQL Server 2019
  1. Requires SQL Server 2014 SP1 or later ref: here
  2. Requires SQL Server 2012 SP2 or later ref: here
  3. Requires SQL Server 2012 SP1 or later ref: here
  4. Requires SQL Server 2008 R2 SP2 or later ref: here
  5. Requires SQL Server 2008 SP3 or later ref: here
  6. Requires SQL Server 2008 R2 SP1 or later ref: here
  7. Requires SQL Server 2008 SP3 or later ref: here
  8. Requires Windows Server 2008 R2 SP1 or later ref: here
  9. Requires Windows Server 2008 SP2 or later ref: here
  10. Reporting Services – SharePoint & Reporting Services Add-in for SharePoint products can both be installed on Windows Server 2008 R2 SP2 or later ref: here

Linux


SQL Server 2017SQL Server 2019
Red Hat Enterprise Linux 7.3 or 7.4 Workstation, Server, and Desktop
SUSE Enterprise Linux Server v12 SP2
Ubuntu 16.04LTS
  • SQL Server 2017 ref: here
  • SQL Server 2019 ref: here

Docker

SQL Server 2017SQL Server 2019
Docker Engine 1.8+ on Windows, Mac, or Linux
  • SQL Server 2017 ref: here
  • SQL Server 2019 ref: here

SQL Server Version Feature Selections Reference Guide

Below shows a quick reference guide of the feature selections presented based on the version of SQL Server being installed on Windows.

SQL Server 2019 CTP 3.0

  • Instance Features
    • + Java connector for HDFS data sources

SQL Server 2017

  • First SQL version to support being installed on Linux
  • First SQL version to not include Reporting Services media
    (can be found here)
  • First SQL version to not include Documentation media (now online only here)
  • Instance Features
    • + Machine Learning Services (In-Database)
    • + R
    • + Python
    • – R Services (In-Database)
    • – Reporting Services – Native
  • Shared Features
    • + Machine Learning Server (Standalone)
    • + R
    • + Python
    • + Scale Out Master
    • + Scale Out Worker
    • – R Server (Standalone)
    • – Reporting Services – SharePoint
    • – Reporting Services Add-in for SharePoint Products
    • – Document Components

SQL Server 2016

  • First SQL version to use 64-bit media only
  • First SQL version to not include SSMS media (can be found here)
  • .Net 3.5 no longer required as a prerequisite
  • Instance Features
    • + R Services (In-Database)
    • + PolyBase Query Service for External Data
  • Shared Features
    • + R Server (Standalone)
    • – Management Tools – Basic
    • – Management Tools – Complete

SQL Server 2014

  • First SQL version to not include Data Tools media (can be found here)
  • Shared Features
    • – SQL Server Data Tools

SQL Server 2012

  • Instance Features
    • + Reporting Services – Native
    • – Reporting Services
  • Shared Features
    • + Reporting Services – SharePoint
    • + Reporting Services Add-in for SharePoint Products
    • + Data Quality Client
    • + SQL Server Data Tools
    • + Document Components
    • + Distributed Replay Controller
    • + Distributed Replay Client
    • – Business Intelligence Development Studio
    • – SQL Server Books Online

SQL Server 2008 R2

  • No feature changes from SQL Server 2008

SQL Server 2008

  • First SQL version where Reporting Services is not dependent on IIS
  • First SQL version to not include sample databases as option (various sample media links can be found here)
  • Instance Features
    • + Reporting Services
    • – Notification Services
  • Shared Features
    • + Client Tools Connectivity
    • + Client Tools Backwards Compatibility
    • + Client Tools SDK
    • + Management Tools – Basic
    • + Management Tools – Complete
    • + SQL Client Connectivity SDK
    • + Microsoft Sync Framework
    • – Connectivity Components
    • – Management Tools
    • – Software Development Kit
    • – SQLXML Client Features
    • – Legacy Components
    • – Sample Databases
    • – AdventureWorks Sample OLTP
    • – AdventureWorksDW Sample Data Warehouse
    • – AdevntureWorks Sample OLAP
    • – Sample Code and Applications

SQL Server 2005

continued
continued

Database Mirroring Setup GUI Bug

I came across a bug while testing the setup of Database Mirroring on SQL Server 2016 Standard Edition with SP1 and using the newest version of SQL Server SQL Server Management Studio: v17.8.1
 

Firstly, the preparation went fine.
 
Create the database on the Principal and perform both a Full and Transaction Log Backup of the new database.
 
CREATE DATABASE db1;
BACKUP DATABASE db1 TO DISK = 'C:\SQLBackups\db1.bak';
BACKUP LOG db1 TO DISK = 'C:\SQLBackups\db1.trn';

 

Next restore the backups on the Mirror.
 
RESTORE DATABASE db1 FROM DISK='\\SQL2K16SEP1\SQLBackups$\db1.bak' WITH NORECOVERY;
RESTORE LOG db1 FROM DISK='\\SQL2K16SEP1\SQLBackups$\db1.trn' WITH NORECOVERY;

 

The firewall Inbound Rules for both SQL / Mirroring were added on the Principal and Mirror.
 

Good so all the prep work is done, lets proceed to setup Mirroring using the Wizard on Principal.
 

Go through the Configure Security steps.
 








Bang you get the following 927 error:
Database ‘db1’ cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)
 

So, what is going on here?
 
Looking at Profiler during the setup we see that the GUI issues a use [db1] on the Mirror instance and of course that is going to end badly as the database is not accessible and hence you get the error that ‘db1’ cannot be opened. Microsoft needs to update the GUI to use [master].
 

So how do you resolve this?
 
Well you need to complete the Database Mirroring setup manually and the steps are:
 
1.) On the Mirror use the following to set the principal as a partner:
ALTER DATABASE [db1] SET PARTNER = 'TCP://SQL2K16SEP1:5022';
 

2.) Then finally on the Principal use the following to set the mirror as a partner:
 
ALTER DATABASE [db1] SET PARTNER = 'TCP://SQL2K16SEDR1:5022';
 

Database Mirroring should now be working.
 

I’m sure Microsoft will address this issue in a future release of SSMS, but until then the above should hopefully assist you until then.

August 2018 Critical Patch For SQL Server 2016 / 17

In-case you missed it CVE-2018-8273 | Microsoft SQL Server Remote Code Execution Vulnerability was released yesterday,was marked as Critical and affects SQL Server 2016 & 2017

Further information and downloads can be found here. It should be included in the next SQL Server patching cycle.

If you’re rolling out new servers, you may want to apply it now.

Update: There was an issue with this patch and it has since been replaced

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]’

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, ‘subscriber security meta-data’, @database, @database, ‘sp_vupgrade_replsecurity_metadata’) WITH NOWAIT
END
END
END
ELSE
BEGIN
— “Could not open database %s. Upgrade of replication %s could not be performed. Please rerun %s from %s database once the %s is accessible.”
RAISERROR(21733, 10, -1, @database, ‘security meta-data’, ‘sp_vupgrade_replsecurity_metadata’, ‘[master]’, @database) WITH NOWAIT
END

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

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.dbo.sysdatabases
WHERE name NOT IN ( N’master’ COLLATE DATABASE_DEFAULT,
N’tempdb’ COLLATE DATABASE_DEFAULT,
N’msdb’ COLLATE DATABASE_DEFAULT,
N’model’ COLLATE DATABASE_DEFAULT
)
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]’

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, ‘subscriber security meta-data’, @database, @database, ‘sp_vupgrade_replsecurity_metadata’) WITH NOWAIT
END
END
END
ELSE
BEGIN
— “Could not open database %s. Upgrade of replication %s could not be performed. Please rerun %s from %s database once the %s is accessible.”
RAISERROR(21733, 10, -1, @database, ‘security meta-data’, ‘sp_vupgrade_replsecurity_metadata’, ‘[master]’, @database) WITH NOWAIT
END

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

 

That’s all folks.