SQL Agent Multi-Server Administration

Let's say you are a SQL Server database administrator working for a big corporation, which has hundreds of server instances. You have a bunch of agent jobs like the Ola's maintenance solution that you want to deploy on all the server instances in your company, and you like to manage those jobs across all the instances in a centralized master. Most database administrators believe it can only be done by 3rd party management tools. In fact, SQL Server Agent service has a built-in feature named Multi-server Administration (a.k.a. MSX/TSX) which was overlooked by most of you. In this blog post I would like to demonstrate how to use this feature to deploy and manage agent job in multiple target servers. For the sake of simplicity, I setup 3 server instances in my local PC: the default instance which will be the master server (MSX), while the other 2 instances named TargetSvr1 and TargetSvr2 will be the target servers (TSX). Let's start to see how to do it.

In the MSX, right-click SQL Server Agent >  Multi Server Administration > Make this a Master.

Click Next in the welcome page, specify an operator email if you like, or just click Next to skip it.

Add the target servers, then click Next to continue.

It will check the server compatibility, click Close once all targets passed the checking.

Let this wizard to create the login in your master server if necessary. Next.

Review the configuration. Click Finish.

Oops! There's an error.

In order to overcome it, for simplicity let's modify the registry key as stated in the online document, for each of your target servers, set them to zero.

Do the above steps again. You should success.

Now we can try to create a multi-server job. In order to proof the job really being executed in all the target servers, let's create a simple table in a testing database on all the target servers first.
USE master
GO
CREATE DATABASE TestingDB
GO
USE TestingDB
GO
CREATE TABLE TestTable (col varchar(50));
GO

Refresh the SSMS Object Explorer, SQL Server Agent in master server becomes MSX, and those in target servers become TSX.

And there's a new folder, Multi-Server Jobs, inside the master server. Let's create a job there which insert a row into the testing table. Here's the job body:
DECLARE @s varchar(50) = CAST(GETDATE() AS varchar(50));
INSERT TestingDB..TestTable VALUES (@s);

In the target tab of the new job window, specify the target servers of this job.

The default polling interval of multi-server job is one minute, which means jobs will be deployed and executed in the target servers one minute after the deployment and job firing in master.

After one minute, refresh the object explorer, you will see the job is deployed in all targets.

Let's execute the job in the Master server.

After one minute, run a multi-server query in your targets, you should see the new row exists in all target servers.

You can also check the job history in the master server.

Please be reminded that MSX/TSX doesn't guarantee the job executions are at the same time among the targets.

How to make a Table Non-Updatable

It's a common business requirement that some data needed to be unchangeable once it entered into the database, such as payments, bank transfers, bets, etc. Unfortunately, SQL Server has no built in declarative support for immutable columns, but we database administrators and sql developers can make it ourselves by writing triggers which rollback or skip the update operations. In order to tighten it up, we can obscure the body text of the trigger using WITH ENCRYPTION option in the create trigger statement. Furthermore, we can create SQL Server Audit on SCHEMA_OBJECT_CHANGE_GROUP event class, in order to trace any alter/drop/disable operations on the triggers. Let's see a simple demo:

USE TestDB
GO
-- Create a demo Table, with one primary key column, one ordinary column, and one immutable column
CREATE TABLE DemoTable (pk int PRIMARY KEY, col1 varchar(50), immutableCol varchar(50));
GO
-- Create an encrypted trigger to prevent update on the immutable column
CREATE TRIGGER tr_DemoTable_Upd ON DemoTable
WITH ENCRYPTION
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(immutableCol) BEGIN ROLLBACK; RETURN; END
END
GO
-- Create a server audit object
USE master
GO
CREATE SERVER AUDIT Audit_Demo
TO FILE (
FILEPATH = N'D:\temp',
MAXSIZE = 50 MB,
MAX_ROLLOVER_FILES = 1000
)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT Audit_Demo WITH (STATE = ON);
GO
-- Create database audit specification to trace schema changes in the user database
USE TestDB
GO
CREATE DATABASE AUDIT SPECIFICATION DbAuditSpec_SchemaChg
FOR SERVER AUDIT Audit_Demo
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

Now we have done all the widgets, let's have a test on it.
-- add a testing row
INSERT DemoTable (pk, col1, immutableCol) VALUES (1, 'ABC', 'Immutable value');
GO
SELECT * FROM DemoTable;
GO
-- try updating the immutable column, which should be fail
UPDATE DemoTable SET immutableCol = 'Update it';
GO
SELECT * FROM DemoTable;

Let's try to disable the trigger, then check the audit log to see this action has been logged.
DISABLE TRIGGER tr_DemoTable_Upd ON DemoTable;

Although this approach cannot stop someone with sysadmin right to modify the data, it can deter them from attacking it.

Change Data Capture (CDC) for tracking data change

SQL Server provides two features that track changes to table data: Change Data Capture (CDC) and Change Tracking (CT). While CT is a synchronous mechanism and less overhead, it only captures the event that rows in a table were changed, but does not capture the actual data. On the other hand, CDC relies on SQL Server Agent jobs to asynchronously capture changes for a table, both the fact that changes were made and the actual data. Before SQL Server 2016, CDC was an enterprise edition only feature. Starting from SQL Server 2016, CDC is also available in standard edition. Below picture is obtained from online SQL Docs illustrating the architecture of CDC.

Source tables are the user tables in your database that enabled CDC. When DML statements applied on user tables, the database modifications made are recorded on transaction log. A capture agent job is created when the first table in the database is enabled for CDC, this capture job periodically scans the transaction log and adds information about changes into change tables. SQL developers are provided with built-in functions to enumerate the captured changes.

Let's see a demo on how to use CDC.

1. Enable CDC for the current database.
USE TestDB;
GO
EXEC sys.sp_cdc_enable_db; 

2. Create a new table with primary key.
CREATE TABLE TestTable (ID int, col varchar(50), col2 varchar(50), CONSTRAINT PK_TestTable PRIMARY KEY (ID));

3. Enable CDC for the user table.
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'TestTable', @role_name = NULL;
Two Agent jobs are created with each CDC enabled database: one that is used to capture changes, another is responsible for change table cleanup.

The capture job is started immediately and runs continuously. By default, it captures a maximum of 1000 transactions per cycle with a wait of 5 seconds between cycles. The cleanup job runs once daily at 2AM, and retains change table entries for 4320 minutes. You can check these configurations by executing sys.sp_cdc_help_jobs stored procedure.

4. List the tables which enabled CDC, get the capture_instance name.
EXEC sys.sp_cdc_help_change_data_capture;

By default, the capture instance name is derived from the source schema name plus the source table name in the format schemaname_sourcename, in our example, it's dbo_TestTable.

5. Apply some DML on the table, then uses the fn_cdc_get_all_changes function to get the changes.
DECLARE @begin binary(10), @end binary(10);
SET @begin = sys.fn_cdc_get_min_lsn('dbo_TestTable');
SET @end = sys.fn_cdc_get_max_lsn();
SELECT CASE __$operation WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'PreUpdate' WHEN 4 THEN 'PostUpdate' ELSE 'Unknown' END AS Operation, *
    FROM cdc.fn_cdc_get_all_changes_dbo_TestTable(@begin, @end, N'all update old') ORDER BY __$start_lsn, __$seqval;
GO
As you can see, rolled back DML won't be captured

After inserted one row, the enumerate function returns one row stating an insert for a new row:

When multiple rows were inserted within one transaction, those inserts have the same __$start_lsn:

You can also use fn_cdc_get_net_changes function to get the changes, which only returns one net change row for each source row changed within the specified LSN range.

SQL Server provides quite a few functions and stored procedures that you can use to enumerate and manipulate CDC change tables according to your application requirement. Please refer to the online SQL Docs for all of them.

Transform JSON data into rowset

JSON is a popular data format used for storing unstructured data. Many RESTful web services return results and accept inputs in JSON format. Starting from SQL Server 2016, it has native support on parsing JSON data. This blog post demonstrates how to transform JSON data into a rows and columns, and joining the result with table data.

Firstly, let us create a table named Products, which stores the product details into a text column with JSON format:

CREATE TABLE Products (
    id int NOT NULL PRIMARY KEY,
    [name] varchar(50) NOT NULL,
    detailsInJson nvarchar(4000) NOT NULL
);

Then populate some sample rows into it:

INSERT Products (id, [name], detailsInJson) VALUES (1, 'Toy Car',     '{"size" : "10cm x 20cm x 30cm", "color" : "red", "type" : "mini"}');
INSERT Products (id, [name], detailsInJson) VALUES (2, 'Teddy Bear', '{"color" : "brown", "texture" : "cloth"}');

Let's double check the table data:

As you can see, each row has its own set of attributes in the JSON data.
We can retrieve the JSON data using OPENJSON function as below:

SELECT id, [name], D.*
FROM Products
CROSS APPLY OPENJSON (detailsInJson) WITH (
    color varchar(50) '$.color',
    size varchar(50) '$.size',
    [type] varchar(50) '$.type',
    [texture] varchar(50) '$.texture'
) AS D;

Below is the query result:

You can also check the execution plan of this query, OPENJSON is essentially a Table Valued Function which do the transformation:

SQL Server 2016 introduces various new built-in functions to process JSON data, which can be found in Microsoft SQL Docs.

Striped Database Backup

SQL Server allows you to backup your database or transaction log in a striped media set. A striped set is a set of disk files on which backup data is divided into blocks and distributed in a fixed order. Most database administrators overlook this feature. It can be used to speed up the backup process by distributing the backup workload into multiple storage devices.

Below scripts demonstrate how to make a striped backup set:
BACKUP DATABASE [StackOverflow2013]
TO
DISK='D:\DbBackup\StackOverflow2013_1.bak',
DISK='D:\DbBackup\StackOverflow2013_2.bak',
DISK='D:\DbBackup\StackOverflow2013_3.bak',
DISK='D:\DbBackup\StackOverflow2013_4.bak'
WITH STATS = 10
GO
I only used the same drive D:\ on above script, but you can specify different drives for each DISK.
Here is the result:

Below script demonstrate how to restore from a striped backup:
RESTORE DATABASE [StackOverflow2013]
FROM
DISK='D:\DbBackup\StackOverflow2013_1.bak',
DISK='D:\DbBackup\StackOverflow2013_2.bak',
DISK='D:\DbBackup\StackOverflow2013_3.bak',
DISK='D:\DbBackup\StackOverflow2013_4.bak'
WITH STATS = 10
GO

The major downside of striped backup is that if one backup file is corrupted, you cannot restore it.

Auditing Stored Procedure Execution

In the previous blog post, I demonstrated how to use SQL Server Audit feature to keep track of login and logout events. This post shows how to keep track of stored procedure executions. Below script creates the audit:

USE [master]
GO
CREATE SERVER AUDIT [Audit_SP_Exec]
TO FILE
(    FILEPATH = N'D:\SqlAudit'            -- *** Modify the File Path ***
    ,MAXSIZE = 50 MB
    ,MAX_ROLLOVER_FILES = 2000
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 5000
    ,ON_FAILURE = CONTINUE
)
GO

USE [TestDB]        -- *** your application DB ***
GO
CREATE DATABASE AUDIT SPECIFICATION [DbAuditSpec_SP_Exec]
FOR SERVER AUDIT [Audit_SP_Exec]
ADD (EXECUTE ON OBJECT::[dbo].[uspTest] BY [public]),
ADD (EXECUTE ON OBJECT::[dbo].[uspTest2] BY [public])
GO
ALTER DATABASE AUDIT SPECIFICATION [DbAuditSpec_SP_Exec] WITH (STATE = ON)
GO

USE [master]
GO
ALTER SERVER AUDIT [Audit_SP_Exec] WITH (STATE = ON)
GO

Then you can view the audit log by right-click the server Audit in SSMS like below:

How to Audit Login and Logout for specific logins

Starting from SQL Server 2008, we can use SQL Server Audit feature to record numerous actions occurred on our SQL Server. SQL Server Audit uses Extended Events under the hood, which impose lesser loading than the plain old SQL Trace.
In this blog post, I would like to show you how to audit login and logout events for specific logins, which can only be done by setting a filter on the Server Audit.

USE [master]
GO

CREATE SERVER AUDIT [Audit_sysadm_login]
TO FILE
(    FILEPATH = N'D:\SqlAudit\'
    ,MAXSIZE = 10 MB
    ,MAX_FILES = 100
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 5000
    ,ON_FAILURE = CONTINUE
)
WHERE ([server_principal_name]='sa' OR [server_principal_name]='peter.lee')
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec_sysadm_login]
FOR SERVER AUDIT [Audit_sysadm_login]
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO

ALTER SERVER AUDIT [Audit_sysadm_login] WITH (STATE = ON)
GO

* Please be reminded that ON_FAILURE is a critical argument on the CREATE SERVER AUDIT statement. If your SQL Server service online is more important than the auditing needs, set this argument to CONTINUE.

The simplest way to check the audit log is using the Log File Viewer, which can be opened in Object Explorer > Security > Audits > right-click the audit > View Audit Logs.