SQL Server Agent Alerts- Part 1

We can use the below script to create SQL Server Agent alerts to notify the operator defined.  The script is adapted from the post written by GLENN BERRY. The script creates alerts for high severity errors (from 19 to 25), alert for Error 832, and for Errors 855, 856 & few Alwayson Alerts (if enabled)

USE [msdb];
GO

-- Change @OperatorName as needed
DECLARE @OperatorName SYSNAME = N'DBAOperator';
-- Change @CategoryName as needed
DECLARE @CategoryName SYSNAME = N'SQL Server Agent Alerts';

IF NOT EXISTS (
		SELECT *
		FROM msdb.dbo.sysoperators
		WHERE name = @OperatorName
		)
BEGIN
	RAISERROR (
			'There is no SQL Operator with a name of %s'
			,18
			,16
			,@OperatorName
			);

	RETURN;
END

IF NOT EXISTS (
		SELECT *
		FROM msdb.dbo.syscategories
		WHERE category_class = 2 -- ALERT
			AND category_type = 3
			AND name = @CategoryName
		)
BEGIN
	EXEC dbo.sp_add_category @class = N'ALERT'
		,@type = N'NONE'
		,@name = @CategoryName;
END

-- Get the server name
DECLARE @ServerName SYSNAME = (
		SELECT @@SERVERNAME
		);
-- Alert Names start with the name of the server 
DECLARE @Sev19AlertName SYSNAME = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource';
DECLARE @Sev20AlertName SYSNAME = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process';
DECLARE @Sev21AlertName SYSNAME = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process';
DECLARE @Sev22AlertName SYSNAME = @ServerName + N' Alert - Sev 22 Error: Fatal Error: Table Integrity Suspect';
DECLARE @Sev23AlertName SYSNAME = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
DECLARE @Sev24AlertName SYSNAME = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error';
DECLARE @Sev25AlertName SYSNAME = @ServerName + N' Alert - Sev 25 Error: Fatal Error';
DECLARE @Error823AlertName SYSNAME = @ServerName + N' Alert - Error 823: The operating system returned an error';
DECLARE @Error824AlertName SYSNAME = @ServerName + N' Alert - Error 824: Logical consistency-based I/O error';
DECLARE @Error825AlertName SYSNAME = @ServerName + N' Alert - Error 825: Read-Retry Required';
DECLARE @Error832AlertName SYSNAME = @ServerName + N' Alert - Error 832: Constant page has changed';
DECLARE @Error855AlertName SYSNAME = @ServerName + N' Alert - Error 855: Uncorrectable hardware memory corruption detected';
DECLARE @Error856AlertName SYSNAME = @ServerName + N' Alert - Error 856: SQL Server has detected hardware memory corruption, but has recovered the page';

-- Sev 19 Error: Fatal Error in Resource
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev19AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName
		,@message_id = 0
		,@severity = 19
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev19AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 20 Error: Fatal Error in Current Process
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev20AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName
		,@message_id = 0
		,@severity = 20
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000'

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev20AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 21 Error: Fatal Error in Database Process
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev21AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName
		,@message_id = 0
		,@severity = 21
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev21AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 22 Error: Fatal Error Table Integrity Suspect
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev22AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName
		,@message_id = 0
		,@severity = 22
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev22AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 23 Error: Fatal Error Database Integrity Suspect
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev23AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName
		,@message_id = 0
		,@severity = 23
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev23AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 24 Error: Fatal Hardware Error
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev24AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName
		,@message_id = 0
		,@severity = 24
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev24AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Sev 25 Error: Fatal Error
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Sev25AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName
		,@message_id = 0
		,@severity = 25
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Sev25AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Error 823 Alert added on 8/11/2014
-- Error 823: Operating System Error
-- How to troubleshoot a Msg 823 error in SQL Server	
-- http://support.microsoft.com/kb/2015755
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error823AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName
		,@message_id = 823
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error823AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Error 824 Alert added on 8/11/2014
-- Error 824: Logical consistency-based I/O error
-- How to troubleshoot Msg 824 in SQL Server
-- http://support.microsoft.com/kb/2015756
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error824AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName
		,@message_id = 824
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error824AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Error 825: Read-Retry Required
-- How to troubleshoot Msg 825 (read retry) in SQL Server
-- http://support.microsoft.com/kb/2015757
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error825AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName
		,@message_id = 825
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error825AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Error 832 Alert added on 10/30/2013
-- Error 832: Constant page has changed
-- http://www.sqlskills.com/blogs/paul/dont-confuse-error-823-and-error-832/
-- http://support.microsoft.com/kb/2015759
IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error832AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error832AlertName
		,@message_id = 832
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 900
		,@include_event_description_in = 1
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error832AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error832AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

-- Memory Error Correction alerts added on 10/30/2013
-- Mitigation of RAM Hardware Errors	 		
-- When SQL Server 2012 Enterprise Edition is installed on a Windows 2012 operating system with hardware that supports bad memory diagnostics, 
-- you will notice new error messages like 854, 855, and 856 instead of the 832 errors that LazyWriter usually generates.
-- Error 854 is just informing you that your instance supports memory error correction
-- Using SQL Server in Windows 8 and Windows Server 2012 environments
-- http://support.microsoft.com/kb/2681562
-- Check for SQL Server 2012 or greater and Enterprise Edition
-- You also need Windows Server 2012 or greater, plus hardware that supports memory error correction
IF LEFT(CONVERT(CHAR(2), SERVERPROPERTY('ProductVersion')), 2) >= '11'
	AND SERVERPROPERTY('EngineEdition') = 3
BEGIN
	-- Error 855: Uncorrectable hardware memory corruption detected
	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @Error855AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @Error855AlertName
			,@message_id = 855
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 900
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000';

	-- Add a notification if it does not exist
	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @Error855AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error855AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	-- Error 856: SQL Server has detected hardware memory corruption, but has recovered the page
	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @Error856AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @Error856AlertName
			,@message_id = 856
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 900
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000';

	-- Add a notification if it does not exist
	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @Error856AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error856AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END
END

DECLARE @IsHadrEnabled AS SQL_VARIANT

SET @IsHadrEnabled = (
		SELECT SERVERPROPERTY('IsHadrEnabled')
		)

IF @IsHadrEnabled = 1
BEGIN
	--AG
	DECLARE @ErrorAG1480AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 1480: AG Role Changed';
	DECLARE @ErrorAG35264AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 35264: AG data Movement Suspended';
	DECLARE @ErrorAG35265AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 35265: AG data Movement Resumed';
	DECLARE @ErrorAG41404AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 41404: AG is offline';
	DECLARE @ErrorAG41405AlertName SYSNAME = @ServerName + N' Prod-Alert - Error 41405: AG not ready for Automatic failover';

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG35264AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG35264AlertName
			,@message_id = 35264
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG35264AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG35264AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG35265AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG35265AlertName
			,@message_id = 35265
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG35265AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG35265AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG41404AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG41404AlertName
			,@message_id = 41404
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG41404AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG41404AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG41405AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG41405AlertName
			,@message_id = 41405
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG41405AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG41405AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END

	IF NOT EXISTS (
			SELECT name
			FROM msdb.dbo.sysalerts
			WHERE name = @ErrorAG1480AlertName
			)
		EXEC msdb.dbo.sp_add_alert @name = @ErrorAG1480AlertName
			,@message_id = 1480
			,@severity = 0
			,@enabled = 1
			,@delay_between_responses = 600
			,@include_event_description_in = 1
			,@category_name = @CategoryName
			,@job_id = N'00000000-0000-0000-0000-000000000000'

	-- Add a notification if it does not exist
	IF NOT EXISTS (
			SELECT *
			FROM dbo.sysalerts AS sa
			INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
			WHERE sa.name = @ErrorAG1480AlertName
			)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @ErrorAG1480AlertName
			,@operator_name = @OperatorName
			,@notification_method = 1;
	END
END
GO

Hope it helps !!

SQL Server Offerings in Azure

The below image shows the various SQL Server DB offerings in Azure. 

Enable TDE for a database in AlwaysOn Availability Group

In this blog, we will explore how we can enable TDE for a database that is already part of an AlwaysOn Availability Group.

Step 1:  We need to create  Master Key,  Certificate, and Database Encryption Key. The script below needs to be executed on the Primary replica. Modify the password and location as required.

 

USE MASTER
GO

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'Password@123';

--Backup Key
BACKUP MASTER KEY TO FILE = 'D:\TDE\MK_backup.key' ENCRYPTION BY PASSWORD = 'Password@456';
GO

-- create certificate
CREATE CERTIFICATE TDECertificate
	WITH SUBJECT = 'TDE_CERT';
GO

-- backup Cert
BACKUP CERTIFICATE TDECertificate TO FILE = 'D:\TDE\EncryptionCert.cert'
WITH PRIVATE KEY (
		FILE = 'D:\TDE\EncryptionCert.key'
		,ENCRYPTION BY PASSWORD = 'Password@123'
		);
GO

USE < DB Name FOR which we have TO enable TDE >
GO

-- Create a Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
	WITH Algorithm = AES_128 ENCRYPTION BY SERVER Certificate TDECertificate;

Step 2:  Below script needs to be executed on Secondary Replicas, which creates the Master Key & the same CERTIFICATE which we created on Primary.

USE MASTER
GO

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'Password@123';

--Backup Key
BACKUP MASTER KEY TO FILE = 'D:\TDE\MK_backup.key' ENCRYPTION BY PASSWORD = 'Password@456';
GO

-- Copy the CERTIFICATE taken on Primary Replica(in step 1) to all secondary Replicas & paste it on drive : D:\TDE\
CREATE CERTIFICATE TDECertificate
FROM FILE = 'D:\TDE\EncryptionCert.cert'
WITH Private KEY (
		FILE = 'D:\TDE\EncryptionCert.key'
		,Decryption BY Password = 'Password@123'
		);

-- backup Cert
BACKUP CERTIFICATE TDECertificate TO FILE = 'D:\TDE\EncryptionCert_secondary.cert'
WITH PRIVATE KEY (
		FILE = 'D:\TDE\EncryptionCert_secondary.key'
		,ENCRYPTION BY PASSWORD = 'Password@123'
		);
GO

Step 3:  Connect to Primary Replica again & issue the below command after creating the certificates and keys on the secondary replica(s).

ALTER DATABASE < DB Name FOR which we have TO enable TDE >

SET ENCRYPTION ON

Step 4: Monitor the progress of TDE using the below script

SELECT DB_NAME(database_id) AS DatabaseName
	,encryption_state
	,encryption_state_desc = CASE encryption_state
		WHEN '0'
			THEN 'No database encryption key present, no encryption'
		WHEN '1'
			THEN 'Unencrypted'
		WHEN '2'
			THEN 'Encryption in progress'
		WHEN '3'
			THEN 'Encrypted'
		WHEN '4'
			THEN 'Key change in progress'
		WHEN '5'
			THEN 'Decryption in progress'
		WHEN '6'
			THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
		ELSE 'No Status'
		END
	,percent_complete
	,encryptor_thumbprint
	,encryptor_type
FROM sys.dm_database_encryption_keys

Step 5: Once the database is encrypted, we have to resume the Data Movement. Connect to the server instance that hosts the secondary replica whose database we want to resume by issuing the below command.

ALTER DATABASE database_name

SET HADR RESUME

Done!! I hope this helps 😊

Capture Queries Executed on SQL Server RDS

In this blog, we will explore how we can set up a server-side trace against AWS DB instance. Extended events are now supported in all version starting from SQL server 2012

Step 1: The script below starts a server-side trace and captures queries executed on RDS 

in the D:\rdsdbdata\Log directory - including all rollover files such as RDSTrace_1.trc, RDSTrace_2.trc, etc.( this path can't be changed). We will be capturing RPC: Completed, SP: StmtCompleted &  SQL: BatchCompleted events

 

/****************************************************/
/* Created by: SQL Server 2019 Profiler          */
/* Date: 06/14/2021  03:40:30 PM         */
/****************************************************/
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT

SET @maxfilesize = 100

EXEC @rc = sp_trace_create @TraceID OUTPUT
	,0
	,N'D:\RDSDBDATA\Log\RDSTrace'
	,@maxfilesize
	,NULL

IF (@rc != 0)
	GOTO error

-- Set the events
DECLARE @on BIT

SET @on = 1

EXEC sp_trace_setevent @TraceID
	,10
	,1
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,3
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,11
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,12
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,13
	,@on

EXEC sp_trace_setevent @TraceID
	,10
	,35
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,1
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,3
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,11
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,12
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,13
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,28
	,@on

EXEC sp_trace_setevent @TraceID
	,45
	,35
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,1
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,3
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,11
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,12
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,13
	,@on

EXEC sp_trace_setevent @TraceID
	,12
	,35
	,@on

-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT

EXEC sp_trace_setfilter @TraceID
	,11
	,0
	,6
	,N'loginName' --pass login name
	-- Set the trace status to start

EXEC sp_trace_setstatus @TraceID
	,1

-- display trace id for future references
SELECT TraceID = @TraceID

GOTO finish

error:

SELECT ErrorCode = @rc

finish:
GO

Step 2: Find the trace ID of the previously created trace by issuing the below command

SELECT id
	,path
FROM sys.traces
WHERE id > 1

Step 3: Specify the trace ID captured in Step 2 to stop the trace

DECLARE @TraceID INT;

SET @TraceID = 2;-- specify value from Step 2 

EXEC sp_trace_setstatus @traceid = @TraceID
	,@status = 0;-- stop trace

Step 4:  Load the results of all files named RDSTrace.trc in the D:\rdsdbdata\Log directory, including all rollover files like RDSTrace_1.trc, into a table named Queries in the current database; which can be queried later

SELECT *
INTO Queries
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', DEFAULT);

Step 5: Delete the trace 

DECLARE @TraceID INT;

SET @TraceID = 2;-- specify value from Step 2

EXEC sp_trace_setstatus @traceid = @TraceID
	,@status = 2;-- delete trace

Done!! I hope this helps 😊

Check Fragmentation of entire DB or specific table

We can use the below script to find the fragmentation of the entire DB or specific table. The script is adapted from Ola hallengren maintenance solution to perform Index maintenance. 

DECLARE @DatabaseID INT

SET @DatabaseID = DB_ID()

DECLARE @objectid INT

SELECT @objectid = (
		SELECT OBJECT_ID('NULL')
		)

--Select @objectid = (select OBJECT_ID('PUT TABLE NAME HERE'))
SELECT DB_NAME(@DatabaseID) AS DatabaseName
	,schemas.[name] AS SchemaName
	,objects.[name] AS ObjectName
	,indexes.[name] AS IndexName
	,objects.type_desc AS ObjectType
	,indexes.type_desc AS IndexType
	,dm_db_index_physical_stats.partition_number AS PartitionNumber
	,dm_db_index_physical_stats.page_count AS [PageCount]
	,dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats(@DatabaseID, @objectid, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id]
	AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN (
		'U'
		,'V'
		)
	AND objects.is_ms_shipped = 0
	AND indexes.[type] IN (
		1
		,2
		,3
		,4
		)
	AND indexes.is_disabled = 0
	AND indexes.is_hypothetical = 0
	AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
	AND dm_db_index_physical_stats.index_level = 0
	AND dm_db_index_physical_stats.page_count >= 1000

Hope it helps !!