TDE Transparent database encryption

Start encrypting your data folks, better be safe than sorry and TDE is a simple way of keeping at least the mdf files and backups safe from snooping eyes.

In more technical terms TDE encrypts data at rest, so data that is on the disk is encrypted, it is decrypted when read into memory, so if you have access to the database through a SQL Server login you can see the decrypted data just as usual. Also the backups are encrypted so an attacker can’t just copy a .bak file and run with it, they’ll need the certificate to be able to decrypt it.

Better than nothing and you can set it up in AlwaysOn too, I have documented a simple way to do it below.

IMPORTANT!!!

Keep your passwords and certificates safe, preferably NOT on the DB servers. Ok? Good 🙂

Run this on a test AO a couple times until you are sure of all the steps.

--Primary
USE Master;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='reallystrongpassword';
GO

-- Create certificate
CREATE CERTIFICATE TEST_Cert
WITH 
SUBJECT='Test Cert';
GO

-- Backup to somewhere on disk
BACKUP CERTIFICATE TEST_Cert
TO FILE = 'Z:\dbbackup\TEST_Cert'
WITH PRIVATE KEY (file='Z:\dbbackup\TEST_Cert_Private',
ENCRYPTION BY PASSWORD='alsoreallystrongpassword')

--Secondary
USE Master;
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='reallystrongpassword'; --does not need to match primarys master key password
GO

CREATE CERTIFICATE TEST_Cert
FROM FILE='Z:\dbbackup\TEST_Cert'
WITH PRIVATE KEY (
FILE = 'Z:\dbbackup\TEST_Cert_Private',
DECRYPTION BY PASSWORD='alsoreallystrongpassword')


--Now check on both servers that we have the certificate installed
SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date
FROM sys.certificates;

--Also check with AlwaysOn dashboard that the databases are in sync

--Primary
USE DB_To_Be_Encrypted  -- I am sure your databases have better names...
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TEST_Cert;
GO

ALTER DATABASE DB_To_Be_Encrypted --This will take a while even if the command returns immediately
SET ENCRYPTION ON;
GO

--On primary we can run some commands to check logspace used, encryption progress and such
USE MASTER;
GO

dbcc sqlperf(logspace)
go

sp_helpdb DB_To_Be_Encrypted
go

-- Check encryotion process
SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,
dm.key_algorithm,dm.key_length
FROM sys.databases db
LEFT OUTER JOIN 
sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO

 

How do we remove TDE? Easy, below will clean up what we added above.

--Primary
alter database DB_To_Be_Decrypted set encryption off

-- When it has finished decrypting
use DB_To_Be_Decrypted
drop database encryption key


--On both primary and secondary
use master 
drop certificate TEST_Cert
drop master key

 

 

 

Local Server Groups

There is a cool feature in SQL Server Management Studio I’m not sure every DBA knows about. It’s the possibility to save groups of servers in SSMS and either just using this to open a connection to a single server faster or to run SQL code against the whole group, yes, awesome feature!

So open SSMS, and under View click on Registered Servers.

On Local Server Groups you can start by adding a group of SQL Servers, lets call them TEST, so create New Server Group called ‘TEST’

Now on TEST we right click and choose New Server Registration, add 2 or more test servers and make sure they are under TEST group. Ok? Cool!

To run SQL on them all you just right click on the TEST group and choose New Query. In the query window you will see that the bottom part is now a pink-ish colour, that means you are connected to a whole group of servers.

Try running some code, like

select name from sys.databases

The output will be a list of database names, (Duh!), but also a column showing which server these belong to, so SSMS took your code and ran it on each of the servers in the group, received the output from each and printed it on your Results pane.

I use this feature to easily run some sanity checks on all databases on all servers, like below script where I check if a database has auto_close or auto_shrink on, if page verify is NOT checksum or if it is lacking an owner, simple and basic checks.  We can expand this to check for missing backups or errors or whatever you check regularly.

SELECT s.name, s.is_auto_close_on, 
       s.is_auto_shrink_on, s.owner_sid as 'Owner', 
       s.page_verify_option_desc
FROM sys.databases AS s
WHERE s.is_auto_shrink_on = 1
or s.page_verify_option_desc <> 'CHECKSUM'
or s.is_auto_close_on = 1
or s.owner_sid  is null

 

 

Is a database being used?

Kinda tricky to find out sometimes, easiest is just to ask the application owner, but if that’s not an option below are a couple of ways to try to find out if it is being used.

Check with index stats

WITH LastActivity (ObjectID, LastAction) AS 
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION 
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
on so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

 Processes or plans being updated?

Run each query below and see if it is clear whether a database is being used or not.

SELECT @@ServerName AS server
,NAME AS dbname
,COUNT(STATUS) AS number_of_connections
,GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME

SELECT @@ServerName AS SERVER
,NAME
,login_time
,last_batch
,getdate() AS DATE
,STATUS
,hostname
,program_name
,nt_username
,loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE database_id NOT BETWEEN 0 AND 4 
AND loginame IS NOT NULL

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name like 'Transactions/sec%'
and instance_name like 'MSSQL%';


SELECT
SUBSTRING(tx.[text],
(qs.statement_start_offset / 2) + 1,
(CASE WHEN qs.statement_end_offset =-1 THEN DATALENGTH(tx.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)
/ 2 + 1) AS QueryText,
case when pl.query_plan LIKE '%<MissingIndexes>%' then 1 else 0 end as [Missing Indexes?],
qs.execution_count,
qs.total_worker_time/execution_count AS avg_cpu_time,
qs.total_worker_time AS total_cpu_time,
qs.total_logical_reads/execution_count AS avg_logical_reads,
qs.total_logical_reads,
qs.creation_time AS [plan creation time],
qs.last_execution_time [last execution time],
CAST(pl.query_plan AS XML) AS sqlplan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS pl
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS tx
WHERE pl.query_plan LIKE '%[DATABASENAME YOUR LOOKING FOR]%'
ORDER BY execution_count DESC OPTION (RECOMPILE);

Last login time per login

SELECT DB_NAME(r.database_id),max(login_time),login_name,max(last_request_end_time)
FROM sys.dm_exec_requests AS R JOIN sys.dm_exec_sessions S ON R.session_id=S.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ST
WHERE plan_handle IS NOT NULL
GROUP BY DB_NAME(r.database_id),login_name

And if you still have no clue whether it is used or not just shut down the instance…. JUST KIDDING!!!!!! 🙂

 

Number of virtual log files per database

Here is an easy way to get the number of virtual log files (VLF) per database. If using AlwaysOn or mirroring make sure you run it on the primary as it will fail on the closed secondary databases.

Create Table #stage(
RecoveryUnitId int
, FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);

Create Table #results(
Database_Name sysname
, VLF_count int 
);

Exec sp_msforeachdb N'Use ?; 
Insert Into #stage 
Exec sp_executeSQL N''DBCC LogInfo(?)''; 

Insert Into #results 
Select DB_Name(), Count(*) 
From #stage; 

Truncate Table #stage;'

Select * 
From #results
Order By VLF_count Desc;

Drop Table #stage;
Drop Table #results;

So why do we want to know how many VLF’s we have? It’s actually quite important as it can have a large effect on performance and log backups.

Long story short, try to keep it below 1000 for large databases and for small above 10 at least. It has to do with how the log file is extended, for each extension we create new VLF’s and if we have too many there is an overhead managing these during backup/restores.

If you know your database is going to grow than it will probably also use more log, so change from the default 10% or 1MB extension to a more sensible value, disk is cheap these days and you don’t want to run out of log, the database will grind to a halt.  What’s a sensible value? Well for a 100GB database I use approx 10GB log file and set extend to 1GB if we have fast disks.

Make sure to grant Local Security Policy ‘Perform volume mainenance tasks’ to the SQL Server service user. Otherwise extending the file will take some time. Read more here https://www.sqlshack.com/perform-volume-maintenance-tasks-security-policy/

Database size and type

Simple script, shows database name, type of file and size of that file.

It does not count the system databases not any database that has a ‘snapshot’ in its name. Customize as you like 🙂

select sysdb.name,
CASE sysmaster.type_desc
WHEN 'ROWS' THEN 'DATA'
WHEN 'LOG' THEN 'LOG'
END as filetype,
sum((sysmaster.size/128)) as MB
from sys.databases sysdb
join sys.master_files sysmaster on sysdb.database_id = sysmaster.database_id
where sysdb.name not like '%snapshot%'
and sysdb.name not in ('tempdb','master','model','msdb')
group by sysdb.name, sysmaster.type_desc

 

What is holding the transaction log from shrinking?

If the database transaction log is ever growing, or you are trying to shrink it and it just sits there stubbornly refusing, run the below script and check the log_reuse_wait column. If it is saying anything other than LOG_BACKUP or NOTHING than you have to continue investigating.

select name, log_reuse_wait_desc from sys.databases

If it is LOG_BACKUP than just run one or more LOG backups, normally that will resolve it. You may have a problem that the active part of the log is at the end of the log file. You can check that with

dbcc loginfo()

Scroll down to the bottom, looking at the Status column, whenever there is a 2 in there it means that part of the log file is currently used, you can never shrink past a status 2 block counting from the bottom.

Check if you have any open transactions with

dbcc opentran()

or you can use the below script that does a more thourough check for long running transactions.

SELECT 	db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
		SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
		(CASE WHEN r.statement_end_offset = -1
				THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
		ELSE 
				r.statement_end_offset
		END -r.statement_start_offset)/2) AS stmt_executing,
	s.login_name, r.percent_complete, r.start_time
	,CASE s.transaction_isolation_level
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'ReadUncomitted'
		WHEN 2 THEN 'ReadComitted'
		WHEN 3 THEN 'Repeatable'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
	END as 'Isolation level'
	,	s.HOST_NAME, 	s.PROGRAM_NAME,	s.host_process_id, 
	r.status, 	r.wait_time,	wait_type, 	r.wait_resource,
	r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
	, (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
			sys.dm_exec_sessions s
WHERE r.session_id > 50 
and r.session_id=s.session_id
and r.session_id <> @@SPID
ORDER BY r.cpu_time desc

If log_reuse_wait is anything but LOG_BACKUP or NOTHING than have a look here at Microsoft site about factors that can delay log truncation.

If the log disk is full first investigate WHY it is filling up before just extending the file/disk, if it for example is because your log backups are failing than it will never stop growing and you are just wasting disk instead of trying to solve the real problem.

Get SQL Server port number with TSQL

Normally you’d go through SQL Server Configuration Manager and look at the TCP/IP settings but if you want to use just TSQL then try this

DECLARE @InstName VARCHAR(16)
DECLARE @RegLoc VARCHAR(100)
SELECT @InstName = @@SERVICENAME

IF @InstName = 'MSSQLSERVER'
  BEGIN
    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
  END
 ELSE
  BEGIN
   SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
  END

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'

Get SQL Server basic info

select 'Domain' as 'Parameter', DEFAULT_DOMAIN() as 'Value'
UNION ALL
SELECT 'Productversion', SERVERPROPERTY('productversion')
UNION ALL
SELECT 'Productlevel', SERVERPROPERTY ('productlevel')
UNION ALL
SELECT 'Edition', SERVERPROPERTY ('edition')
UNION ALL
SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

DBCC Checkdb on SQL Server Express Edition

Express edition is a really good database engine although it has its limits like 10GB max size, no AlwaysOn and other stuff. But what is really annoying is that SQL Server Agent is not there. How are we supposed to run backups, checkdb, index maintenance, update statistics and so on without it? Not to mention that the business will want to run SSIS jobs and/or simple T-SQL scripts?

To get around this limitation I created simple Powershell scripts for maintenance purposes, they can be run from Windows Task Scheduler. I’ll start with the DBCC CHECKDB jobs as they are very important and later upload scripts for the other maint tasks.

This is run on the SQL Server host and you need to define a catalog where you want to log the results.

It needs to be run as a sysadmin in SQL Server Express, that is, you need to know what to do if CHECKDB throws an error, most often it involves calling Microsoft as fast as you can, or just restoring a recent good backup and trying to puzzle together the missing data.

The script writes to Windows event log so you need to pick up errors from there, it also sends all failed tests to an email you set  in the function, via your smtp server which you also have to set.

It optionally sends successful checks, that is the $sendOnlyFailed parameter, set it to 0 and it will send an email everytime it has done a checkdb.

Now it has been tested but I may have changed something while writing this, maybe the server is full of gremlins or my texteditor has a bug in it, whatever the reason, test this thoroughly on your test servers before putting it on a production system, I will not take any responsibility if something bad happens. Your servers are your responsibility.

# Run checkdb on all databases
$ServerInstance = "localhost"
$DateTimestring = Get-Date -Format yyyyMMdd
$logFilePath = "W:\SQLBackup\scripts\CHECKDBOutput\"

$sendOnlyFailed = 1 # Set to 0 to send status for all checks

# Send status email
Function StatusEmail {
    Param([string]$mailstring)
    Send-MailMessage -SmtpServer "YOU SMTP SERVER" -From "SERVERNAME OR SIMILAR" -To @('LIST OF EMAIL ADRESSES') -Subject "DBCC CHECKDB" -Body $mailstring
}

$sqldatabases = "select name from sys.databases"
$dbs = (Invoke-Sqlcmd -Serverinstance $ServerInstance -Query $sqldatabases).name

foreach ($db in $dbs){
    $logFile = $logFilePath+"CHECKDB_Log_"+$db+"_"+$DateTimestring+".txt"
    $sqlcheckdb = "dbcc checkdb($db) with all_errormsgs"
    Invoke-Sqlcmd -Serverinstance $ServerInstance -Query $sqlcheckdb -QueryTimeout 0 -Verbose *> $logFile

    #Read logfile and if we do not have "CHECKDB found 0 allocation errors and 0 consistency errors in database" somewhere we have an error
    $searchForError = "CHECKDB found 0 allocation errors and 0 consistency errors in database"
    if(Select-String -Pattern $searchForError -Path $logFile) {
        $parameters = @{
        'LogName'  = 'Application'
        'EventId'  = 8957
        'EntryType'  = 'Information'
        'Source' = 'MSSQL$SQLEXPRESS'
        'Message'  = 'DBCC CheckDB success'
        }
        Write-EventLog @parameters
        if($sendOnlyFailed -eq 0) {
            StatusEmail "DBCC CHECKDB for database $db succeeded"
        }
    }else{
        $parameters = @{
        'LogName'  = 'Application'
        'EventId'  = 8957
        'EntryType'  = 'Error'
        'Source' = 'MSSQL$SQLEXPRESS'
        'Message'  = 'DBCC CheckDB failed, please check SQL error log'
        }
        Write-EventLog @parameters
        StatusEmail "DBCC CHECKDB for database $db failed, investigate!"
    }
}

# Delete old log files, older than 15 days
$limit = (Get-Date).AddDays(-15)
Get-ChildItem -Path $logFilePath -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item 

 

AlwaysOn Readable secondary

Set up AlwaysOn to use readable secondaries and have the readers automatically connect to the secondary.

You need a couple of things done, nope it’s not totally automatic, first the developers have to decide which SQL they want/can run on a readonly database, only SELECT’s will work so they have to be careful.

When they have found the SQL’s that will work the connections for them will have to have an added parameter applicationintent=readonly

The thing you as a DBA have to do is configure AlwaysOn to actually route the readonly connections to the secondary.

See below for an example:

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER1' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER1' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER1.domain.com:1433'));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER2' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER2' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER2.domain.com:1433'));

ALTER AVAILABILITY GROUP [SQLAG] 
MODIFY REPLICA ON
N'SERVER1' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER2','SERVER1')));

ALTER AVAILABILITY GROUP [SQLAG] 
MODIFY REPLICA ON
N'SERVER2' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER1','SERVER2')));
GO

Check that the routing list looks ok with

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",

 rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",

 ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url

 FROM sys.availability_read_only_routing_lists rl

 inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id

 inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id

 inner join sys.availability_groups ag on ar.group_id = ag.group_id

 ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

I’ll write more later on how to detect how far behind the secondary is.