SQL Server Policy Based Management (PBM)

Policy Based Management is a system that allows the DBA to manage and control on a granular scale how to best apply rules and standards to the entire server of specific objects

Benefits of Policy-Based Management

  • Allows the DBA to develop and enforce common, enter­prise-wide standards for the SQL Server
  • Allows the DBA to prevent, enable, and disable certain conditions of objects
  • Allows the DBA to propagate a set of rules and conditions to the whole server or objects and find inconsistencies
  • Allows the DBA to automatically check each instance for compliance on a regular basis rather than manually

Policy-Based Management Terms

Facets:

A facet is a collection of pre-defined properties of an object that describe some functionality of SQL Server. For example, the Database (Auto Close, Auto Shrink)

Conditions.

Once you have selected a facet that you want to create a policy for, you must specify a condition for that facet.  For example, if you want the Auto Shrink property of the Database Options facet set to “false,” you must create a condition for this facet

PROCEDURE:

  • FIND THE FACET YOU WANT TO APPLY THE CONDITION ON
  • SET THE CONDITION
  • APPLY THE POLICY
--EXAMPLE 1 : CREATE 10 DATABASE, SOME WITH AUTOSHRINK PROPERTY OF THE FACET (DATABASE) SET TO 'ON'

--FIND ALL THE DATABASE WITH THE AUTOSHRINK PROPERTY SET TO 'ON' AND SET THEM TO 'OFF'

--SOLUTIONS:  CREATE A POLICY BASED CONDITION

USE [master]
GO

CREATE DATABASE T1
GO

ALTER DATABASE [T1] 
SET AUTO_SHRINK ON 
WITH NO_WAIT
GO

CREATE DATABASE T2
GO

CREATE DATABASE T3
GO

CREATE DATABASE T4
GO

ALTER DATABASE [T4] 
SET AUTO_SHRINK ON 
WITH NO_WAIT
GO

CREATE DATABASE T5
GO


CREATE DATABASE T6
GO

ALTER DATABASE [T6] 
SET AUTO_SHRINK ON 
WITH NO_WAIT
GO

CREATE DATABASE T7
GO

CREATE DATABASE T8
GO

CREATE DATABASE T9
GO

CREATE DATABASE T10
GO


--USE [master]
--GO
--ALTER DATABASE [T1] 
--SET AUTO_SHRINK OFF 
--WITH NO_WAIT
--GO

--DROP PROCEDURE POLICY_BASED
--------------------------------------------

--EXAMPLE 2:  PREVENT A USER FROM CREATING A STORED PROCEDURE THAT DOES NOT USE A 'USP_' PREFIX

--FACET = MULTIPART NAME

USE SQL2
GO

CREATE PROCEDURE POLICY_BASED  --<< NOTE THAT THE PREFIX 'USP_' HAS NOT BEEN USED, AND AS SUCH SPROC WILL NOT BE CREATED 
AS
SELECT * FROM [dbo].[People2]

--AS SOON AS THE SPROC HAS THE PREFIX 'USP_', IT'S CREATED

USE SQL2
GO

CREATE PROCEDURE USP_POLICY_BASED  --<< NOTE THAT THE PREFIX 'USP_' HAS NOT BEEN USED, AND AS SUCH SPROC WILL NOT BE CREATED 
AS
SELECT * FROM [dbo].[People2]
-- Find all databases with AutoShrink turned on. 


declare @DatabaseName sysname

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare cur cursor local fast_forward for
        select name   as 'Database Name'
          from sys.databases
         where is_auto_shrink_on = 1
           and state_desc = 'ONLINE'
           and database_id > 4

    open cur
    fetch next from cur into @DatabaseName

    while @@fetch_status = 0
    begin
        print @DatabaseName
        fetch next from cur into @DatabaseName
    end

    close cur
    deallocate cur
end
else
begin
    declare cur cursor local fast_forward for
        select name   as 'Database Name'
          from master..sysdatabases
         where status & 4194304 = 4194304
           and status & 512 = 0
           and dbid > 4

    open cur
    fetch next from cur into @DatabaseName

    while @@fetch_status = 0
    begin
        print @DatabaseName
        fetch next from cur into @DatabaseName
    end

    close cur
    deallocate cur
end

A very common pattern to begin is the creation of conditions to avoid the policy check against system databases and system tables. Let’s do a step-by-step walkthrough to achieve this.

Avoiding System Databases

We need two conditions for this: One to check system databases and another to check system tables. Let’s start:

  1. Using SSMS, right-click the ‘Conditions’ folder, below ‘management’->’Policy Management’ and click the ‘new condition’ menu item to open the ‘Create new condition’ window.
  2. In the ‘Create new condition’ window, type ‘Not System Database’ in the ‘Name’ textbox. It will be the name of our first condition
  3. In the ‘Create new condition’ window, in the ‘Facet’ drop-down list, select ‘Database’, so we can create a condition against database properties.Each ‘Facet’ has several fields. Each field represents information that we can check in the object. There is a field in the ‘Database’ facet named ‘@IsSytemObject’ that we can use to identify whether the database is a system database. In our case, we need the condition to be true if the database is NOT a system database, so the policy check will continue
  4. In the ‘Create new condition’ window, in the ‘Field’ column inside the ‘Expression’ grid, select ‘@IsSystemObject’
  5. Select ‘=’ in the ‘Operator’ column inside the ‘Expression’ grid
  6. Choose ‘False’ in the ‘Value’ column inside the ‘Expression’ grid
  7. In the ‘Create new condition’ window, click the‘Ok’ button

Creating the ‘Auto Shrink’ condition

The main condition of our first policy will need to check whether the ‘Auto Shrink’ database option is enabled. Let’s build it:

  1. Using SSMS, right-click the ‘Conditions’ folder, below ‘management’->’Policy Management’ and click the ‘new condition’ menu item to open the ‘Create new condition’ window.
  2. In the ‘Create new condition’ window, type ‘AutoShrink’ in the ‘Name’ textbox: It will be the name of our first condition
  3. In the ‘Create new condition’ window, in the ‘Facet’ drop-down list, select ‘Database’, so we can create a condition against database properties.
  4. In the ‘Create new condition’ window, in the ‘Field’ column inside the ‘Expression’ grid, select ‘@AutoShrink’
  5. Select ‘=’ in the ‘Operator’ column inside the ‘Expression’ grid
  6. Choose ‘False’ in the ‘Value’ column inside the ‘Expression’ grid. The Auto Shrink option should be disabled, so our policy will pass if ‘@AutoShrink’ is false.
  7. In the ‘Create new condition’ window, click ‘Ok’ buttonThe two conditions that we’ve created so far will appear in SSMS like the image below:

Creating the first policy: Check for ‘Auto Shrink’ option

Let’s use our condition in a new policy.

  1. Right-click the ‘Policies’ folder, below ‘management’->’Policy Management’ and click the ‘new policy’ menu item to open the ‘Create new policy’ window.
  2. In the ‘Create new policy’ window, type ‘AutoShrink’ in the ‘Name’ textbox, it will be the name of our first policy
  3. In ‘Check condition’ drop-down list, select ‘AutoShrink’, the main condition of our policy
  4. In the ‘Against Target’ box, click the arrow besides ‘Every Database’ and select ‘Not System Database’, so our policy won’t be checked for system databases.
  5. Click ‘Ok’ button
  6. Right-click the ‘AutoShrink’ policy and click the ‘Evaluate’ context menu item
  7. Check the result, you will be able to see if any database has the ‘Auto Shrink’ option enabled, then click ‘Ok’ button.

Avoiding policy checks on system tables

We need just one more condition that will filter the tables for which our policies will be checked. We don’t want our policies checking system tables.

Let’s name our new condition as ‘Not System table’. The condition will use the ‘Table’ facet, because it will be executed to check each table. The ‘Table’ facet also has the ‘@IsSystemObject’ field, so we can use it in the same way we did with the previous condition.

  1. Right-click the ‘Conditions’ folder, below ‘management’->’Policy Management’ and click ‘new condition’ menu item to open ‘Create new condition’ window.
  2. In the ‘Create new condition’ window, type ‘Not System Table’ in the ‘Name’ textbox, it will be the name of our first condition
  3. In the ‘Create new condition’ window, in the ‘Facet’ drop-down list, select ‘Table’, so we can create a condition against database properties.
  4. In the ‘Create new condition’ window, in the ‘Field’ column inside the ‘Expression’ grid, select ‘@IsSystemObject’
  5. Select ‘=’ in the ‘Operator’ column inside the ‘Expression’ grid
  6. Choose ‘False’ in the ‘Value’ column inside the ‘Expression’ grid
  7. In the ‘Create new condition’ window, click ‘Ok’ button