Abhilash A

Thursday, July 22, 2021

SQL Server Offerings in Azure

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

Tuesday, June 22, 2021

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 😊

Friday, June 11, 2021

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

Thursday, February 25, 2021

T-SQL to check TLS settings

Manually checking the TLS setting from the registry is a tedious task. We have to open the registry setting and navigate to the path below to determine if legacy protocols are enabled or not on the DB server. We can use either use iiscrypto tool or the T-SQL script to check the setting. The below script works well for 2008R2+ & if the DB instance is clustered, it shows results from the ACTIVE node.

Registry Path: HKEY_LOCAL_MACHINE\SYSTEM\Current Control Set\ Control\ Security Providers\ SCHANNEL\ Protocols\

DECLARE @temp TABLE (
	active_node VARCHAR(20)
	,instance VARCHAR(20)
	,protocol VARCHAR(20)
	,flag INT
	)
DECLARE @regkey VARCHAR(255)
DECLARE @value INT

--Get Status for SSL2.0
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\SSL 2.0\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\SSL 2.0\Server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

--Get Status for SSl3.0
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\SSL 3.0\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\SSL 3.0\Server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

--get Status for TLS1.0
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.0\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.0\Server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

-- get Status for TLS1.1
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.1\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.1\Server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
END

--get status for TLS1.2
SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.2\Client\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
		--select * from @temp
END

SET @regkey = 'SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\\TLS 1.2\server\'

BEGIN
	EXECUTE master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
		,@regkey
		,'Enabled'
		,@value OUTPUT    

	INSERT INTO @temp (
		active_node
		,instance
		,protocol
		,flag
		) (
		SELECT convert(VARCHAR(20), serverproperty('computernamephysicalnetbios'))
		,@@servername
		,'protocol' = SUBSTRING(@RegKey, CHARINDEX('\\', @RegKey) + 2, 14)
		,'status' = @value
		)
END

--display all flags.
SELECT *
FROM @temp

Hope it helps !!

Check Uptime of SQL server

It is important to find out for how long the SQL server is up & running to diagnose any issues. Here is one of the scripts which I use almost every day to find the uptime of the Server.

SET NOCOUNT ON;

DECLARE @UpTime VARCHAR(12)
	,@StartDate DATETIME
	,@sqlmajorver INT
	,@sqlcmd NVARCHAR(500)
	,@params NVARCHAR(500)

SELECT @sqlmajorver = CONVERT(INT, (@@microsoftversion / 0x1000000) & 0xFF);

IF @sqlmajorver = 9
BEGIN
	SET @sqlcmd = N'SELECT @StartDateOUT = login_time, @UpTimeOUT = DATEDIFF(mi, login_time, GETDATE()) FROM master..sysprocesses WHERE spid = 1';
END
ELSE
BEGIN
	SET @sqlcmd = N'SELECT @StartDateOUT = sqlserver_start_time, @UpTimeOUT = DATEDIFF(mi,sqlserver_start_time,GETDATE()) FROM sys.dm_os_sys_info';
END

SET @params = N'@StartDateOUT DATETIME OUTPUT, @UpTimeOUT VARCHAR(12) OUTPUT';

EXECUTE sp_executesql @sqlcmd
	,@params
	,@StartDateOUT = @StartDate OUTPUT
	,@UpTimeOUT = @UpTime OUTPUT;

SELECT @@servername AS ServerName
	,GETDATE() AS [Current_Time]
	,@StartDate AS Last_Startup
	,CONVERT(VARCHAR(4), @UpTime / 60 / 24) + 'd ' + CONVERT(VARCHAR(4), @UpTime / 60 % 24) + 'h ' + CONVERT(VARCHAR(4), @UpTime % 60) + 'm' AS Uptime
	,@@version AS Version
GO

Hope it helps !!

Tuesday, February 2, 2021

Who issued " DROP Database " command ?

Sometimes it is hard to track who dropped a SQL Server database, especially when you are in a large shop managing several databases. It should have been dropped by one of your team members as part of any decommission request or the application users themselves; who have enough privileges on the DB or Server. Here is one quick script I use. If we are lucky enough, we will find an entry in the default trace.😎

DECLARE @FileName VARCHAR(255)

SELECT @FileName = SUBSTRING(path, 0, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1) + '\Log.trc' --'
FROM sys.traces
WHERE is_default = 1;

SELECT TOP 1 LoginName
	,HostName
	,ApplicationName
	,StartTime
	,TextData
FROM sys.fn_trace_gettable(@FileName, DEFAULT) AS gt
WHERE EventClass = 47
	AND DatabaseName = 'Pass DB Name here'
ORDER BY StartTime DESC;

Hope it helps !!

Abhilash A

Thursday, July 22, 2021

SQL Server Offerings in Azure

Tuesday, June 22, 2021

Enable TDE for a database in AlwaysOn Availability Group

Capture Queries Executed on SQL Server RDS

Friday, June 11, 2021

Check Fragmentation of entire DB or specific table

Thursday, February 25, 2021

T-SQL to check TLS settings

Check Uptime of SQL server

Tuesday, February 2, 2021

Who issued " DROP Database " command ?

Extract DB Permission

AWS Solutions Architect

Azure Administrator Associate

Azure Database Administrator

Report Abuse

462: Microsoft SQL Server 2012/2014 Databases

Azure Fundamentals