How to Create and Evaluate Policies in SQL Server - MS SQL Server DBA Tutorial

In this video you will learn how to create and Evaluate Policy in SQL Server using SQL Server Management Studio as well as T-SQL script. It gives you overview of Policy management in SQL Server, you can use this video to setup a policy against SQL Server instance or instances as well as SQL Server objects such as databases, Logins, security and other policies that will keep your production environment intact with your checklist. You will learn how to evaluate on demand Policy as well as how to schedule policy using Policy management in SQL Server.

Script used in the video to Create Policy

DECLARE @object_set_id INT

EXEC msdb.dbo.Sp_syspolicy_add_object_set
  @object_set_name=N'CompatibilityCheck_policy_ObjectSet',
  @facet=N'Database',
  @object_set_id=@object_set_id OUTPUT

SELECT @object_set_id

DECLARE @target_set_id INT

EXEC msdb.dbo.Sp_syspolicy_add_target_set
  @object_set_name=N'CompatibilityCheck_policy_ObjectSet',
  @type_skeleton=N'Server/Database',
  @type=N'DATABASE',
  @enabled=True,
  @target_set_id=@target_set_id OUTPUT

SELECT @target_set_id

EXEC msdb.dbo.Sp_syspolicy_add_target_set_level
  @target_set_id=@target_set_id,
  @type_skeleton=N'Server/Database',
  @level_name=N'Database',
  @condition_name=N'',
  @target_set_level_id=0

GO

DECLARE @policy_id INT

EXEC msdb.dbo.Sp_syspolicy_add_policy
  @name=N'CompatibilityCheck_policy',
  @condition_name=N'CompatibilityLevel',
  @policy_category=N'',
  @description=N'Check compatibility of sql server 2014 databases',
  @execution_mode=0,
  @policy_id=@policy_id OUTPUT,
  @object_set=N'CompatibilityCheck_policy_ObjectSet'

SELECT @policy_id

GO

Create and Evaluate Policies in SQL Server - SQL Server DBA Tutorial