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 !!