SQL Server Policy-Based Management the hidden configuration baseline.

Policy-Based Management

Policy-Based Management is a policy based system for managing one or more instances of SQL Server. This has been around in SQL Server since 2008 but is often overlooked for both its primary function in administration as well as its ability to store baselines.

If you open up SQL Server Management Studio (SSMS) and navigate to the Management/Policy Management node you can see that there are three sub-folders which are as follows.

  • Policies
  • Facets
  • Conditions

A facet is a logical grouping of properties. A condition is a question or check that we apply to our facet. Finally a policy contains a group of facets that have been selected to run checks against.

An example implementation could check that the Server Facet property backupdirectory is equal to ‘c:\backups’. This would then confirm that the default location for backups is the same all instances.

This policy is stored on the instance that we created on, however we could also export this to a file and run in straight from the file-system.

Now the important point to gain from running through the above is that Facets have access to configuration data and it is this that can be used to get a configuration baseline.

Baseline

Right click on the root node of the server, click facets. We can see that there are multiple facets and the values contained represent the current state. These can be exported as policies stored on an instance or as xml files providing a point in time snapshot baseline of the configuration. Also be aware that facets also exist at the database node level.

Automating that export process with PowerShell.

Import-Module –Name SqlServer;

<#
Get-Facets
#>
function Get-Facets {
[CmdletBinding()]
Param(
[Parameter(Position=0,
Mandatory=$true,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[System.Data.SqlClient.SqlConnection]$SqlConnection,

[Parameter(Position=1,
Mandatory=$true,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[string]$Directory,

[Parameter(Position=2,
Mandatory=$false,
ValueFromPipeline,
ValueFromPipelineByPropertyName)]
[string[]]$DatabaseList
)
begin {

}
process {
$SqlStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SqlConnection);
$PolicyStore = New-Object Microsoft.SQLServer.Management.DMF.PolicyStore($SqlStoreConnection);

$XmlWriterSettings = New-Object System.Xml.XmlWriterSettings;
$XmlWriterSettings.Indent = [System.Xml.Formatting]::Indented;
$XmlWriterSettings.OmitXmlDeclaration = $true; #strip of declaration

#Server Facet
$ServerFacets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets |?{$_.TargetTypes -contains [Microsoft.SqlServer.Management.Smo.Server]};
$DataSource = $SqlConnection.DataSource;
$SfcQueryExpression = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SfcQueryExpression("Server");

If(($ServerFacets.Count -gt 0) -and ($SfcQueryExpression)){
$ServerFacets|%{
$Guid = [Guid]::NewGuid();
$Policy = $_.Name;
$Name = "$($SqlConnection.DataSource.Replace('\','$'))_$($Policy)_$($Guid).xml"
$FullName = $(Join-Path -Path $Directory -ChildPath $Name);
$XmlWriter = [System.Xml.XmlWriter]::Create($FullName,$XmlWriterSettings);

$PolicyStore.CreatePolicyFromFacet($SfcQueryExpression,$($_.Name),$($_.Name),$($_.Name),$XmlWriter);

$XmlWriter.Flush();
$XmlWriter.Close();
$XmlWriter.Dispose();
[Void]$SqlStoreConnection.Disconnect();
$SqlConnection.Close();
}
}

#Database Facet
if($DatabaseList.Count -ge 1){
$DatabaseFacets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets|?{$_.TargetTypes -contains [Microsoft.SqlServer.Management.Smo.Database]};
Foreach($Database in ($DatabaseList | select -uniq))
{
If($Database.Length -gt 0)
{
$SfcQueryExpression = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SfcQueryExpression("Server/Database[@Name='$Database']");
If(($DatabaseFacets.Count -gt 0) -and ($SfcQueryExpression)){
$DatabaseFacets|%{
$Guid = [Guid]::NewGuid();
$Policy = $_.Name;
$Name = "$($SqlConnection.DataSource.Replace('\','$'))_$($Policy)_$($Database)_$($Guid).xml"
$FullName = $(Join-Path -Path $Directory -ChildPath $Name);
$XmlWriter = [System.Xml.XmlWriter]::Create($FullName,$XmlWriterSettings);

$PolicyStore.CreatePolicyFromFacet($SfcQueryExpression,$($_.Name),$($_.Name),$($_.Name),$XmlWriter);

$XmlWriter.Flush();
$XmlWriter.Close();
$XmlWriter.Dispose();
}
}
}
}
}

}
}

This code then be run via a command such as the following. I have chosen to export master database facets as well as the server level facets in the example.

$Server = "$env:COMPUTERNAME";
[System.Data.SqlClient.SqlConnection]$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=$Server;Integrated Security=SSPI;Application Name=Discovery");

$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlConnection;
$DatabaseList = $SmoServer.Databases.Name;
#$DatabaseList = @("master","model","tempdb");
$DatabaseList = @("master");
$Directory = 'C:\temp'

Get-Facets $SqlConnection $Directory $DatabaseList;

Regain access to SQL Server via inject service

One way to regain access to SQL Server is to use a simple inject technique which overrides the current Image Path for the SQL Writer service.

#http://jongurgul.com/blog/regain-access-sql-server-inject-service
If(([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")){
$You = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name;
$ImagePath = $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;
#"C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe";
$SQLCMDPaths = $(Get-ChildItem -Path "C:\Program Files\Microsoft SQL Server\" -include SQLCMD.exe -Recurse | Select-Object FullName,Directory,@{Name="Version";Expression={$_.Directory.ToString().Split("\")[-3]}} | Sort-Object Version -Descending);
$SQLCMDPath = $SQLCMDPaths[0].FullName;
$SQLCMDPath;

If(Test-Path $SQLCMDPath){
$InjectedImagePath = "$SQLCMDPath -S . -E -Q `"CREATE LOGIN [$You] FROM WINDOWS;EXECUTE sp_addsrvrolemember @loginame = '$You', @rolename = 'sysadmin'`"";

#Stop SQLWriter
Get-Service -Name SQLWriter | Stop-Service -ea SilentlyContinue;

#Inject
Set-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath -Value $InjectedImagePath;
Write-Host $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;
Get-Service -Name SQLWriter | Start-Service -ea SilentlyContinue;

#Restore
Set-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath -Value $ImagePath;
Write-Host $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;

#Restart SQLWriter
Get-Service -Name SQLWriter | Start-Service -ea SilentlyContinue;
}Else{"Check SQLCMDPath";}

}Else{"Not Administrator"};

 

PsExec can alternatively be used to access SQL Server as shown in the post below. This relies on the NT AUTHORITY\SYSTEM account having been granted system administrator on the instance. This will be true for anything before SQL Server 2012.

Running as local system to allow administrators in to SQL Server

Additional notes:

 

“In SQL Server 2008 and later, the local Windows Group BUILTIN\Administrator is no longer provisioned as a login in the SQL Server sysadmin fixed server role by default at SQL Server setup install.
As a result, box administrators cannot login to the new SQL Server 2008 and SQL Server 2008 R2 instance by default.”

https://support.microsoft.com/en-gb/help/2184138/guidelines-on-granting-sql-server-sysadmin-privileges

 

 

In SQL Server 2012: “BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role.”

https://technet.microsoft.com/en-us/library/bb500459(v=sql.110).aspx

 

Microsoft SQL Server 2016 build

Here is a quick post for the recommended patches to deploy for Microsoft SQL Server 2016.

After deploying Microsoft® SQL Server 2016 I would recommend immediately patching to 13.00.1708 by applying the MSVCRT patch. (Unless planning to apply a later CU or SP)

This ODBC patch is needed to stop SQL Agent connection issues and “Failed to open loopback connection” issues.

Then deploy the most up to date patch:

 

Backup History

If you want to see more than just when the last backup occurred. Here is a script for backup history with two bonus features.

  • Backup compression ratio – CompressionRatio
  • Backup speed – Backup_MiB_S
--http://jongurgul.com/blog/backup-history/
SELECT
 bus.[database_name] [DatabaseName]
,bus.[type] [Type]
,bus.[backup_start_date] [BackupStartDate]
,bus.[backup_finish_date] [BackupFinishDate]

,CONVERT(DECIMAL(15,3),(bus.[backup_size]/1048576)) [Size_MiB]
,CONVERT(DECIMAL(15,3),(bus.[compressed_backup_size]/1048576)) [CompressedSize_MiB] --SQL2008
,LTRIM(STR((bus.[backup_size])/(bus.[compressed_backup_size]),38,3))+':1' [CompressionRatio] --SQL2008

,DATEDIFF(SECOND,bus.[backup_start_date],bus.[backup_finish_date]) [Duration_S]
,CONVERT(DECIMAL(15,3),(bus.[backup_size]/COALESCE(NULLIF(DATEDIFF(SECOND,bus.[backup_start_date],bus.[backup_finish_date]),0),1))/1048576) [Backup_MiB_S]
,bumf.[physical_device_name]

,bus.[first_lsn] [FirstLSN]
,bus.[last_lsn] [LastLSN]
,bus.[checkpoint_lsn] [CheckpointLSN]
,bus.[database_backup_lsn] [DatabaseBackupLSN]
,bus.[is_copy_only] [IsCopyOnly]
,bus.[differential_base_guid] [DifferentialBaseGUID]
,bus.[differential_base_lsn] [DifferentialBaseLSN]
,bus.[first_recovery_fork_guid] [FirstRecoveryForkID]
,bus.[last_recovery_fork_guid] [LastRecoveryForkID]
,bus.[fork_point_lsn] [ForkPointLSN]

,bus.[user_name] [UserName]
,bus.[compatibility_level] [CompatibilityLevel]
,bus.[database_version] [DatabaseVersion]
,bus.[collation_name] [CollationName]
--SELECT *
FROM [msdb].[dbo].[backupset] bus
INNER JOIN [msdb].[dbo].[backupmediafamily] bumf 
ON bus.[media_set_id] = bumf .[media_set_id]
ORDER BY bus.[backup_start_date] DESC

SQL Server and PowerShell (SQLPS) Starter

The following two snippets of code are two ways to achieve the same outcome, which is the $Server object containing the default instance.

$Server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)";

Or when opening a SQL PS (Powershell) prompt at the default location. e.g. PS SQLSERVER:\SQL\SB01\DEFAULT>

$Server = (Get-Item .)

I mention this because I was asked what the simplist entry point to PowerShell for SQL person was. I can think of nothing simpler than opening the SQL PS shell from SSMS and typing the folllowing:

(gi .)

Note that gi is shorthand for Get-Item.
For example now that we have a reference to out instance we can then start to explore.

(gi .).Databases.FileGroups.Files|Select @{Name="UsedSpace_MiB";Expression={($_.UsedSpace/1KB)}},Name

 

(gi .).Databases|Select Name,LastBackupDate

 

(gi .).Databases

 

(gi SQLSERVER:\SQL\$ENV:ComputerName\DEFAULT).Databases

Using the default system_health Extended Event

The default extended event system_health appeared in SQL 2008 and provides several key monitors, which can prove useful when investigating issues. In the past I have mentioned this in the capturing deadlocks post as it is one of the monitors automatically in place and it is an easy way to grab the deadlock xml.

However deadlocks are not the only thing captured. An overview of what is inside system_health  can be found here:

  • The sql_text and session_id for any sessions that encounter an error with severity >=20
  • the sql_text and session_id for any sessions that encounter a “memory” type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
  • A record of any “non-yielding” problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
  • Any deadlocks that are detected
  • The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
  • The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
  • The callstack, sql_text, and session_id for any session that have waited for an extended period of time for “external” waits or “pre-emptive waits”.

I going to single out errors (error_reported) and provide a query to parse the data. Note I have commented out the additional where filters, but have left them in to show how this can be done.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
DECLARE @target_data XML
SELECT @target_data = CAST([target_data] AS XML)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'

SELECT
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM (SELECT @target_data) [target_data]([target_data])
CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1
--AND x.y.exist('.//data[@name="severity"]/value/text()[. = "20"]') = 1
--AND x.y.value('(@timestamp)[1]','DATETIME') = '2015-10-21 16:29:00.000'

The above query I have used a variable to store @target_data, this could obviously be removed and the queries combined. When I am writing these xml queries I tend to keep a static copy of target_data so as not too impact the instance more than needed.

DECLARE @target_data XML
SELECT
CAST([target_data] AS XML) [target_data] INTO #target_data
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'
SELECT @target_data = [target_data] FROM #target_data

Finally for completeness, as well as the ring_buffer there is also another target for system_health and that is to file.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
SELECT 
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM
(
	SELECT 
	CAST([event_data] AS XML) as [target_data],*
	FROM sys.fn_xe_file_target_read_file('*system_health*.xel',NULL,NULL,NULL)
) e CROSS APPLY [target_data].nodes('/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1

Creating a Deadlock

Previously we have looked at how to capture a deadlock, but how do we go about creating a deadlock?

I think the following is the simplest method:

1. Create two tables with a single row in each and update a row in the first table (note transaction is started with BEGIN TRAN, but not committed.)

USE [tempdb]
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J1')
DROP TABLE [J1];
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J2')
DROP TABLE [J2];
CREATE TABLE [J1]([ID] INT);
CREATE TABLE [J2]([ID] INT);
INSERT INTO [J1]VALUES (1);
INSERT INTO [J2]VALUES(1);
GO
BEGIN TRAN
UPDATE [J1] SET [ID] = 1
--UPDATE [J2] SET [ID] = 1

2. Open a query in a new window and run:

USE [tempdb]
BEGIN TRAN
UPDATE [J2] SET [ID] = 1
UPDATE [J1] SET [ID] = 1

3. Finally in the query window opened in step 1. run the commented code by highlighting everything after the comment marks then pressing F5.

--UPDATE [J2] SET [ID] = 1

This will result in our first window being the victim of a deadlock:

Msg 1205, Level 13, State 45, Line 13
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Note that the second query will have an open transaction, which you can either ROLLBACK or COMMIT.

In step 1 (spid 53) a lock was taken on the row of table J1
In step 2 (spid 54) a lock was taken on the row in table J2, and then it tries to take a lock on J1 but has to wait as the first query already has it locked.
In step 3 (spid 53) the first query attempts to lock the row that the second query has already locked, while holding a lock on the row that the second query wants.

ExampleDeadlock.xdl save this file to your desktop stripping off the .txt extension to leave .xdl which will allow opening in SSMS.

Most Recent SQL Query

Here is another query that is useful to have when you need to find out what everyone is up to. Or in some cases when we need to recover what was on someones ssms before they accidentally closed without saving work. 🙂

--http://jongurgul.com/blog/most-recent-sql-query
SELECT
 DB_NAME(est.[dbid]) [DatabaseName]
,es.[session_id] [SessionID]
,est."text" [StatementText]
,es.[host_name] [ConnectionHostName]
,es.[login_name] [ConnectionLoginName]
,es.[program_name] [ConnectionProgramName]
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.[session_id] = ec.[session_id]
OUTER APPLY sys.dm_exec_sql_text(ec.[most_recent_sql_handle]) est