Handling DDL on Change Data Capture (CDC) enabled Table

SQL Server does not automatically track certain DDL changes in the Change Data Capture log after CDC was enabled on the table. This includes adding new columns to a table after CDC was enabled for the table. To make the CDC log reflects the DDL changes on the source table, it must disable CDC and re-enable CDC on the CDC enabled table. Here comes an example:

USE master

GO

-- Create a database

CREATE DATABASE DbTestCDC;

GO

USE DbTestCDC;

GO

-- Create a table

CREATE TABLE TableTestCDC

(

    ID int NOT NULL PRIMARY KEY,

    FirstName varchar(30),

    LastName varchar(30),

    Email varchar(50)

);

-- Enable CDC for the current database

EXEC sys.sp_cdc_enable_db;

GO

SELECT name, is_cdc_enabled FROM sys.databases;

GO

-- Enable CDC for the specified table in the current database

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

@role_name     = NULL,

@supports_net_changes = 1

GO

-- Check CDC enabled on that table

EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'TableTestCDC';

-- Check CDC retention (default 4320 minutes = 3 days)

SELECT DB_NAME(database_id), * FROM msdb.dbo.cdc_jobs;

-- Testing

INSERT INTO TableTestCDC Values (1, 'Jorge', 'Ramos', 'ramos@yahoo.com');

INSERT INTO TableTestCDC Values (2, 'Andrew', 'Strudwick', 'andrew@yahoo.com');

INSERT INTO TableTestCDC Values (3, 'Thomas', 'Tuchel', 'tuchel@gmail.com');

DELETE FROM TableTestCDC WHERE ID = 1;

UPDATE TableTestCDC SET LastName = 'ABC', FirstName = 'XYZ' WHERE ID = 2;

DELETE FROM TableTestCDC WHERE ID = 3;

SELECT * FROM TableTestCDC;

GO

WAITFOR DELAY '00:00:05';

GO

-- Query CDC data

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_TableTestCDC');

SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS changeTime,

CASE [__$operation] WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'before update' WHEN 4 THEN 'after update' END AS operation,

*

FROM cdc.fn_cdc_get_all_changes_dbo_TableTestCDC(@from_lsn, @to_lsn, N'all update old');

GO

-- ALTER TABLE on the source table, e.g. add a new column

ALTER TABLE dbo.TableTestCDC ADD NewCol varchar(50) NULL;

GO

-- Disable CDC on that table, and then re-enable CDC to reflect the table DDL changes

EXECUTE sys.sp_cdc_disable_table   

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

    @capture_instance = N'all';

GO

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

@role_name     = NULL,

@supports_net_changes = 1

GO

EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'TableTestCDC';

-- Testing (with New column)

INSERT INTO TableTestCDC Values (4, 'Jorge', 'Ramos', 'ramos@yahoo.com', 'NewCol 1');

INSERT INTO TableTestCDC Values (5, 'Andrew', 'Strudwick', 'andrew@yahoo.com', 'NewCol 2');

INSERT INTO TableTestCDC Values (6, 'Thomas', 'Tuchel', 'tuchel@gmail.com', 'NewCol 3');

DELETE FROM TableTestCDC WHERE ID = 4;

UPDATE TableTestCDC SET LastName = 'ABC', FirstName = 'XYZ', NewCol = 'New Col X' WHERE ID = 5;

DELETE FROM TableTestCDC WHERE ID = 6;

SELECT * FROM TableTestCDC;

GO

-- Query CDC data

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_TableTestCDC');

SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS changeTime,

CASE [__$operation] WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'before update' WHEN 4 THEN 'after update' END AS operation,

*

FROM cdc.fn_cdc_get_all_changes_dbo_TableTestCDC(@from_lsn, @to_lsn, N'all update old');

GO

Get the Last Known Actual Execution Plan

SQL Server 2019 introduces sys.dm_exec_query_plan_stats new DMV which returns the equivalent of the last known actual execution plan for a previously cached query plan (All plans are estimated plans. "Actual" plan is essentially = Estimated Plan + Runtime Metrics). You can turn this new feature on at the server level using trace flag 2451 or at database level with LAST_QUERY_PLAN_STATS a new database scoped configuration. For example:

USE [<DB_Name>];

GO

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

GO

Then you can query the last known actual execution plan likes below:

SELECT cp.objtype, DB_NAME(st.dbid) AS DB, OBJECT_NAME(st.objectid, st.dbid) AS ObjectName, st.[text], qps.query_plan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps

--WHERE OBJECT_NAME(st.objectid, st.dbid) = '<stored proc/function name>'

;

In the result, you can click on the query_plan XML to show the graphical plan.

As you see, the plan includes actual runtime metrics.

Dedicated Administrator Connection (DAC)

SQL Server Dedicated Admin Connection (DAC) allows database administrator to connect to SQL Server when it is in emergency. This diagnostic connection allows us to execute diagnostic queries and troubleshoot problems even when SQL Server isn't responding to standard connection requests. By default, the connection is only allowed from a local client running on the server. To guarantee resource available for DAC, only one DAC is allowed per instance of SQL Server. If a DAC connection is already active, any new DAC request is denied with error. Remote connections are disallowed unless being configured by using sp_configure 'remote admin connections option. SQL Server listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon server startup. The error log contains the port number the DAC is listening on. On Failover Cluster Instance (FCI), the DAC will be off by default, but Microsoft recommends enabling remote admin connections on FCI.

Below screenshot shows the mssql error log entry indicating the DAC port of a default instance, most likely TCP 1434.

If there are multiple instances, any subsequent named instances will get a dynamically allocated port in the range 49152 to 65535 as their DAC port.

DAC are not supported via SSMS as it establishes multiple connections by design.

Instead, you can open a new query window in SSMS by pressing the "New Query" button, specifying DAC by ADMIN:<ServerName>.

Then you can run below query to confirm you are in a DAC connection:

SELECT session_id, program_name, P.name AS endpoint_name, is_admin_endpoint

FROM sys.dm_exec_sessions S JOIN sys.tcp_endpoints P ON S.endpoint_id = P.endpoint_id;

If you try to open one more DAC connection, you will get the following error.

For FCI, run the follow SQL command to enable remote DAC:

EXEC sp_configure 'remote admin connections', 1;

GO

RECONFIGURE;

GO
It will take effect immediately.

Export Schema and Table Data using Powershell

$servername = "servername\instancename";

$currentDate = Get-Date -Format "yyyyMMdd";

#Variables - details of the connection, stored procedure and parameters

$connectionString = "server=$servername;trusted_connection=true;";

$sqlQuery = "SELECT [name] FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB');";

#$param1Value = "SomeValue";

#SQL Connection - connection to SQL server

$sqlConnection = new-object System.Data.SqlClient.SqlConnection;

$sqlConnection.ConnectionString = $connectionString;

#SQL Command - set up the SQL call

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;

$sqlCommand.Connection = $sqlConnection;

$sqlCommand.CommandText = $sqlQuery;

#$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);

#SQL Adapter - get the results using the SQL Command

$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter ;

$sqlAdapter.SelectCommand = $sqlCommand;

$dataSet = new-object System.Data.Dataset;

$recordCount = $sqlAdapter.Fill($dataSet) ;

#Close SQL Connection

$sqlConnection.Close();

#Get single table from dataset

$data = $dataSet.Tables[0];

#Loop through each row of data

foreach($row in $data)

{

    #BEGIN - Export Schema

    $dbName = $row.name;

    $schemaQuery = "SELECT SCHEMA_NAME(tab.[schema_id]) AS [schema], tab.[name] AS table_name, col.column_id, col.[name] AS column_name, t.[name] as data_type, col.max_length, col.[precision], col.scale FROM sys.tables AS tab JOIN sys.columns AS col ON tab.[object_id] = col.[object_id] LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id ORDER BY [schema], table_name, column_id;";

    $outFile = "D:\DWExport\Schema $dbName $currentDate.bcp";

    $bcpSchemaCmd = "bcp `"$schemaQuery`" queryout `"$outFile`" -T -w -S $servername -d $dbName";

    Invoke-Expression $bcpSchemaCmd;

    #END - Export Schema

    #BEGIN - Export Table Data

    $connectionString = "server=$servername;database='$dbName';trusted_connection=true;";

    $sqlQuery = "SELECT s.name+'.'+t.name AS table2PartName FROM sys.tables AS T JOIN sys.schemas AS S ON T.schema_id=S.schema_id;";

    #SQL Connection - connection to SQL server

    $sqlConnection = new-object System.Data.SqlClient.SqlConnection;

    $sqlConnection.ConnectionString = $connectionString;

    #SQL Command - set up the SQL call

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand;

    $sqlCommand.Connection = $sqlConnection;

    $sqlCommand.CommandText = $sqlQuery;

    #$parameter = $sqlCommand.Parameters.AddWithValue("@param1",$param1Value);

    #SQL Adapter - get the results using the SQL Command

    $sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter ;

    $sqlAdapter.SelectCommand = $sqlCommand;

    $dataSet = new-object System.Data.Dataset;

    $recordCount = $sqlAdapter.Fill($dataSet) ;

    #Close SQL Connection

    $sqlConnection.Close();

    #Get single table from dataset

    $data = $dataSet.Tables[0];

    #Loop through each row of data

    foreach($row in $data)

    {

        $table2PartName = $row.table2PartName;

        $outFile = "D:\DWExport\$dbName.$table2PartName $currentDate.bcp";

        $bcpTableQueryCmd = "bcp $table2PartName out `"$outFile`" -T -w -S $servername -d $dbName";

        Invoke-Expression $bcpTableQueryCmd;

    }

}

BCP export data and schema from all tables in all databases

 EXEC master.dbo.sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1;

RECONFIGURE WITH OVERRIDE;

GO

SET NOCOUNT ON;

DECLARE @bcpSchema nvarchar(4000), @db sysname;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

    SELECT [name] FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB');

OPEN cur

WHILE 1 = 1

BEGIN

    FETCH cur INTO @db;

    IF @@FETCH_STATUS <> 0 BREAK;

SET @bcpSchema = N'bcp "SELECT SCHEMA_NAME(tab.[schema_id]) AS [schema], tab.[name] AS table_name, col.column_id, col.[name] AS column_name, t.[name] as data_type, col.max_length, col.[precision], col.scale FROM sys.tables AS tab JOIN sys.columns AS col ON tab.[object_id] = col.[object_id] LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id ORDER BY [schema], table_name, column_id;" queryout "D:\DWExport\Schema ' + REPLACE(@@SERVERNAME, '\', '_') + ' ' + @db + ' ' + CONVERT(varchar(8), CAST(GETDATE() AS date), 112) + '.bcp" -T -w -S ' + @@SERVERNAME + ' -d ' + @db;

EXEC master..xp_cmdshell @bcpSchema;

END

CLOSE cur

DEALLOCATE cur

/* EXPORT SCHEMA FINISH */

/* EXPORT TABLE DATA START */

DECLARE @allTables table (completeTableName nvarchar(4000));

DECLARE @bcpTableData nvarchar(4000), @completeTableName nvarchar(4000);

INSERT @allTables (completeTableName)

EXEC sp_msforeachdb 'USE [?];

IF DB_NAME() NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''ReportServer'', ''ReportServerTempDB'')

BEGIN

SELECT ''?''+''.''+s.name+''.''+t.name

FROM sys.tables AS T JOIN sys.schemas AS S ON T.schema_id=S.schema_id;

END';

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

SELECT completeTableName FROM @allTables ORDER BY completeTableName;

OPEN cur

WHILE 1 = 1

BEGIN

FETCH cur INTO @completeTableName;

IF @@FETCH_STATUS <> 0 BREAK;

SET @bcpTableData = N'bcp ' + @completeTableName + ' out "D:\DWExport\' + REPLACE(@@SERVERNAME, '\', '_') + ' ' + @completeTableName + ' ' + CONVERT(varchar(8), CAST(GETDATE() AS date), 112) + '.bcp" -T -w -S ' + @@SERVERNAME;

EXEC master..xp_cmdshell @bcpTableData;

END

CLOSE cur

DEALLOCATE cur

Database Backup Report

CREATE OR ALTER PROC DbBackupReport

AS

BEGIN

SELECT @@SERVERNAME AS serverName, D.[name] AS databaseName, D.recovery_model_desc,

MAX(CASE WHEN B.[type] = 'D' THEN B.backup_finish_date ELSE NULL END) AS LastFullBackup,

MAX(CASE WHEN B.[type] = 'I' THEN B.backup_finish_date ELSE NULL END) AS LastDiffBackup,

MAX(CASE WHEN B.[type] = 'L' THEN B.backup_finish_date ELSE NULL END) AS LastLogBackup

INTO #Result

FROM master.sys.databases D LEFT JOIN msdb.dbo.backupset B ON D.[name] = B.[database_name]

WHERE D.[name] NOT IN ('tempdb', 'model')

GROUP BY D.[name], D.recovery_model_desc;

SELECT serverName, databaseName, recovery_model_desc,

ISNULL(LastFullBackup, '19000101') AS LastFullBackup, LastDiffBackup, LastLogBackup,

CASE WHEN recovery_model_desc <> 'SIMPLE' AND ISNULL(LastFullBackup, '19000101') < DATEADD(day, -7, GETDATE()) THEN 'NO' ELSE 'YES' END AS NonSimpleModeFullBackupin7Days,

CASE WHEN ISNULL(LastFullBackup, '19000101') < DATEADD(hour, -24, GETDATE()) AND

ISNULL(LastDiffBackup, '19000101') < DATEADD(hour, -24, GETDATE())

THEN 'NO' ELSE 'YES' END AS FullOrDiffBackupIn24Hours,

CASE WHEN recovery_model_desc <> 'SIMPLE' AND ISNULL(LastLogBackup, '19000101') < DATEADD(hour, -24, GETDATE()) THEN 'NO' ELSE 'YES' END AS NonSimpleModeLogIn24Hours

INTO #Output FROM #Result;

SELECT *, CASE WHEN 'NO' IN (NonSimpleModeFullBackupin7Days, FullOrDiffBackupIn24Hours, NonSimpleModeLogIn24Hours) THEN 'Red' ELSE 'Black' END AS HighLight FROM #Output ORDER BY HighLight DESC;

END

GO

EXEC DbBackupReport;

You can use the "HighLight" column values in the stored procedure result, to make a custom formatting in the SSRS report, to highlight the databases that need your concern.

Database Audit in AlwaysOn Availability Group

To configure Database Audit in AlwaysOn Availability Group (AAG), we cannot just simply create the SERVER AUDIT and the DATABASE AUDIT SPECIFICATION by using SSMS GUI, otherwise the database audit specification will lost its linkage to the server audit after the AAG failover back and forth. This is because how the database audit specification being mapped to a server audit is by an AUDIT_GUID uniqueidentifier, the GUID will be different among AAG replicas even the server audit name is the same when you create them using SSMS GUI (referring to sys.server_audits DMV and sys.database_audit_specifications DMV). To overcome this situation, you should create the server audit by specifying the same AUDIT_GUID in all AAG replicas, and then create the database audit specification by specifying the server audit name as usual. Below are the procedures:

1. In AAG Primary node, create the Server Audit using SSMS GUI.

2. Generate the creation script for the above server audit, so you got the AUDIT_GUID.

3. Execute the above server audit creation script on other AAG replicas.

4. In primary node, create the database audit specification as usual.

5. Run the following queries to see the database audit specification is correctly mapped to the server audit guid. Failover the AAG to other nodes to see the audit mapping still there.
SELECT * FROM sys.database_audit_specifications;
SELECT * FROM sys.server_audits;

According to the Microsoft documentation:

"To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID found in the mirrored database. The GUID can't be modified after the audit is created."

You can see Microsoft doesn't clearly mention that you should pay attention on it. But actually it's critical if you rely on sql server audit to meet security compliance in AAG.