database

MSSQL Configure Database Encryption

Configure database encryption and backup keys en certificates. This manual shows the complete chain from Service Master Key to the Database Encryption Key. Please remember to at least backup the Server Certificate (marked: “[*** IMPORTANT ***]”) and store these files in a secure location. Without this certificate you cannot access your data when it’s moved or restored to another server. You should substitute all (random generated) passwords with your own.

STEP 1 – Backup and restore (for verification purposes) Service Master Key.

USE master;
GO

-- Backup Service Master Key...
BACKUP SERVICE MASTER KEY TO FILE = 'X:\Path\To\ServiceMaster.key' 
ENCRYPTION BY PASSWORD = 'ooxuRai4shaid0AvnieLoh6t'

-- Restore Service Master Key...
RESTORE SERVICE MASTER KEY FROM FILE = 'X:\Path\To\ServiceMaster.key' 
DECRYPTION BY PASSWORD = 'ooxuRai4shaid0AvnieLoh6t' FORCE

STEP 2 – Create, backup and restore (for verification purposes) Database Master Key and Server Certificate.

USE master;
GO

-- Create Database Master Key...
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah';
GO

-- Backup Database Master Key...
BACKUP MASTER KEY TO FILE = 'X:\Path\To\Master.key'
ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO

-- Drop Database Master Key for restore test...
DROP MASTER KEY
GO

-- Restore Database Master Key...
RESTORE MASTER KEY FROM FILE = 'X:\Path\To\Master.key' 
DECRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO

-- Open key and let Service Master Key decrypt (for transparent encryption)...
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

-- Create Server Certificate...
CREATE CERTIFICATE ServerCertDefault WITH SUBJECT = 'Server Certificate Default'
GO

-- Backup Server Certificate and Key [*** IMPORTANT ***]...
BACKUP CERTIFICATE ServerCertDefault TO FILE = 'X:\Path\To\ServerCertDefault.cer'
WITH PRIVATE KEY ( FILE = 'X:\Path\To\ServerCertDefault.key', 
ENCRYPTION BY PASSWORD = 'Eeh3aeb0EiTh2ohZneil8ueS' );
GO

-- Remove Server Certificate for restore test....
DROP CERTIFICATE ServerCertDefault
GO

-- Restore Server Certificate with Key...
CREATE CERTIFICATE ServerCertDefault 
FROM FILE = 'X:\Path\To\ServerCertDefault.cer'
WITH PRIVATE KEY ( FILE = 'X:\Path\To\ServerCertDefault.key' , 
DECRYPTION BY PASSWORD = 'Eeh3aeb0EiTh2ohZneil8ueS' );
GO

STEP 3 – Turn on database encryption in database.

USE [Database]
GO

-- Create Database Encryption Key...
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertDefault
GO

-- Enable Encryption...
ALTER DATABASE [Database] SET ENCRYPTION ON
GO

-- Check if database encryption is turned on...
SELECT name, is_encrypted FROM sys.databases WHERE is_encrypted = 1

Results:
name                                                           is_encrypted
-------------------------------------------------------------- ------------
DatabaseName                                                   1

MSSQL Create Database With Multiple Filegroups (QuickRef)

Query to create a database with multiple filegroups.

CREATE DATABASE [DatabaseName]
ON PRIMARY
  ( Name = 'DBNamePFG', FILENAME = 'D:\DEF\DBNamePFG.mdf', SIZE = 100 MB, FILEGROWTH = 100 MB ),
FILEGROUP [FileGroup2]
  ( Name = 'DBNameFG2a', FILENAME = 'E:\DEF\DBNAmeFG2a.ndf', SIZE = 100 MB, FILEGROWTH = 100 MB ),
  ( Name = 'DBNameFG2b', FILENAME = 'F:\DEF\DBNAmeFG2b.ndf', SIZE = 100 MB, FILEGROWTH = 100 MB )
LOG ON
  ( Name = 'DBNameLog', FILENAME = 'L:\DEF\DBNameLog.ldf', SIZE = 1 GB, FILEGROWTH = 500 MB )

PowerShell Auto Add Database to SQL AllwaysOn Availability Group

This script adds all user databases which are not yet replicated to a specific SQL Server AlwaysOn Availability Group. After that a check procedure is started to check if replication state for all databases exists and are healthy. Alerts can be send to an e-mail address or to the Windows Eventlog (or both).

This script uses custum functions fnSendMailfnWriteEventLog to send alert messages and fnSQLCmd for handeling SQL statements.

# Import DBA modules (functions)...
Import-Module 'AdminFunctions.psm1'

# Global config settings...
[string]$cAOGroupName = "AO Group Name (LISTENER)"
[string]$cAOListenerName = "LISTENER\INSTANCE"
[string]$cDatabaseBackupLocation = "\\servershare$\_AlwaysOnTempDir"
[string]$sSMTPServer1 = "smtp.example.com"
[string]$sToAddress1 = "admin@example.com"

# Functions en Procedures...
function fnAddDatabase2AOGroup()
{
 param (
   [string]$sAOGroupName,
   [string]$sDatabaseName,
   [string]$sPrimaryServerName,
   [string]$sSecondaryServerName,
   [string]$sDatabaseBackupLocation
 )

 # Create a timestamp...
 [string]$sDate = Get-Date -UFormat %Y%m%d%H%M

 # Concat strings to create path...
 [string]$sDatabaseBackupFile = $sDatabaseBackupLocation + "\" + $sDatabaseName + "_" + $sDate + ".bak"
 [string]$sDatabaseLogFile = $sDatabaseBackupLocation + "\" + $sDatabaseName + "_" + $sDate + ".trn"
 [string]$sAOPrimaryPath = "SQLSERVER:\SQL\" + $sPrimaryServerName + "\AvailabilityGroups\" + $sAOGroupName
 [string]$sAOSecondaryPath = "SQLSERVER:\SQL\" + $sSecondaryServerName + "\AvailabilityGroups\" + $sAOGroupName

 # Backup Data + Log on primary...
 Backup-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseBackupFile -ServerInstance $($sPrimaryServerName)
 Backup-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseLogFile -ServerInstance $($sPrimaryServerName) -BackupAction 'Log'

 # Restore Data + Log on secondary...
 Restore-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseBackupFile -ServerInstance $($sSecondaryServerName) -NoRecovery
 Restore-SqlDatabase -Database $($sDatabaseName) -BackupFile $sDatabaseLogFile -ServerInstance $($sSecondaryServerName) -RestoreAction 'Log' -NoRecovery

 # Add Database to Availability Group...
 Add-SqlAvailabilityDatabase -Path $sAOPrimaryPath -Database $($sDatabaseName)
 Add-SqlAvailabilityDatabase -Path $sAOSecondaryPath -Database $($sDatabaseName)
}

function fnSetAllDatabases2FullRecovery()
{
 param (
         [string]$sDBServerName
       )
  
  [string]$sQuery9 = "-- Declarations and temptables... `
                      DECLARE @iNumRecs int, @iRecNum int = 1, @vCommand nvarchar(max) `
                      CREATE TABLE #tCommands (ID int IDENTITY(1,1), Command nvarchar(max)) `
                      -- Insert Commands into temptable... `
                      INSERT INTO #tCommands (Command) `
                      SELECT N'ALTER DATABASE [' + name + '] SET RECOVERY FULL WITH NO_WAIT' `
                      FROM [master].[sys].[databases] `
                      WHERE recovery_model_desc = 'SIMPLE' AND name NOT in ('master', 'msdb', 'tempdb') `
                      -- Execute commands from temptable... `
                      SELECT @iNumRecs = COUNT(*) FROM #tCommands `
                      WHILE @iRecNum <= @iNumRecs `
                      BEGIN `
                        SELECT @vCommand = Command FROM #tCommands `
                        WHERE ID = @iRecNum `
                        -- Execute command and increase record number... `
                        -- EXECUTE(@vCommand) --Alternative, Not preferred... `
                        EXECUTE sys.sp_executesql @stmt = @vCommand `
                        SET @iRecNum += 1 `
                      END `
                      -- Cleanup... ` 
                      DROP TABLE #tCommands"
  fnSQLCmd -sInstance $($sDBServerName) -sQuery $sQuery9 -sDatabase "master" 
}


### Start main script... ###

# First we set all databases to full recovery...
fnSetAllDatabases2FullRecovery -sDBServerName $($cAOListenerName)

# Retrieve AlwaysOn information from listener...
[string]$sQuery0 = "-- Retrieve AllwaysOn information and status... `
                    SELECT nim.ag_name, arcs.replica_id, arcs.replica_server_name, arcs.join_state, `
                    arcs.join_state_desc, ars.is_local, ars.role, ars.role_desc, `
                    ars.operational_state, ars.operational_state_desc, ars.recovery_health, `
                    ars.recovery_health_desc, ars.synchronization_health, ars.synchronization_health_desc `
                    FROM [master].[sys].dm_hadr_availability_replica_states ars `
                    INNER JOIN [master].[sys].dm_hadr_availability_replica_cluster_states arcs ON ars.replica_id = arcs.replica_id `
                    INNER JOIN [master].[sys].dm_hadr_name_id_map nim ON arcs.group_id = nim.ag_id"
if ($oResult0) { Remove-Variable -name oResult0 }
# Force in object so we can always count...
[Object[]]($oResult0) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery0 -sDatabase "master"

# Retrieve primary and secondary nodes for group...
foreach($rows0 in $oResult0) {
  if (($rows0["ag_name"] -eq $($cAOGroupName)) -and ($rows0["role"] -eq "1")) { 
    [string]$cPrimaryServerName = $rows0["replica_server_name"] 
    [string]$cPrimaryReplicaID = $rows0["replica_id"]
  }
  if (($rows0["ag_name"] -eq $($cAOGroupName)) -and ($rows0["role"] -eq "2")) { [string]$cSecondaryServerName = $rows0["replica_server_name"] }
}

# Retrieve database names...
[string]$sQuery1 = "-- Return all unreplicated databases without `
                    SELECT name FROM [master].[sys].[databases] `
                    WHERE name NOT IN ('master','tempdb','model','msdb','SSISDB') `
                    AND replica_id IS NULL"
if ($oResult1) { Remove-Variable -name oResult1 }
[Object[]]($oResult1) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery1 -sDatabase "master" 

# Process the rows returned...
foreach($rows1 in $oResult1) {
  if (($cPrimaryServerName) -and ($cPrimaryServerName)) {
  fnAddDatabase2AOGroup -sAOGroupName $cAOGroupName -sDatabaseName ($rows1["name"]) -sPrimaryServerName $cPrimaryServerName -sSecondaryServerName $cSecondaryServerName -sDatabaseBackupLocation $cDatabaseBackupLocation
  } 
}

# Check database replication state...
Start-Sleep -m 10000
[string]$sQuery2 = "-- Check database replication state... `
                    SELECT db.name, db.replica_id, synchronization_state, synchronization_health_desc `
                    FROM [master].[sys].[databases] db `
                    LEFT OUTER JOIN [master].[sys].[dm_hadr_database_replica_states] dbrs ON db.database_id = dbrs.database_id `
                    WHERE name NOT IN ('master','tempdb','model','msdb', 'SSISDB') `
                    AND (db.replica_id IS NULL OR synchronization_state <> 2)"
# Force in object so we can always count...
if ($oResult2) { Remove-Variable -name oResult2 }
[Object[]]($oResult2) = fnSQLCmd -sInstance $($cAOListenerName) -sQuery $sQuery2 -sDatabase "master"

# Send report if sync state <> 2 or database not replicated... 
if ($oResult2.Count -gt 0) {
  # Build HTML body...
  [string]$sHTML_Body1 = ""
  [string]$sHTML_Body1 += $oResult2 | ConvertTo-Html name, replica_id, synchronization_health_desc -Fragment
  [string]$sHTML_Body1 += ""
 
  # Build TEXT body...
  [string]$sResult2 = $oResult2 | Out-String
  [string]$sTEXT_Body1 = $sResult2

  # Send messages...
  [string]$sSubject1 = ($cAOListenerName.Replace("\", "_")) + ":Database(s) not replicated..."
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sHTML_Body1 -swIsBodyHTML

  # Write to Eventlog...
  $sTEXT_Body1 = $sSubject1 + $sTEXT_Body1
  fnWriteEventLog -sNode ($cAOListenerName.Replace("\", "_")) -sMessage $sTEXT_Body1
}

PowerShell SQL Query Function

The function below can be used to a query to a database. The script returns any error as a result (2 lines).

function fnSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  # Register SQPPS Module...
  $oSQLPSModule = Get-Module | Where {$_.name -eq "SQLPS" }
  if ($oSQLPSModule.Count -eq 0) {
    Import-Module "SQLPS" -WarningAction SilentlyContinue
  }

  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}