How to Create Policy Conditions in SQL Server - Microsoft SQL Server DBA Tutorial

This video illustrates how to create policy condition in SQL Server using SQL Server Management Studio as well as T-SQL script. you will learn overview of different Facets available in SQL Server and how to create condition using Facets in SQL Server, best practices of creating condition based on available Facets in SQL Server. How to create conditions to create Policy in SQL Server using Policy Management feature available in SQL Server.

Script used in the video to Create Policy Condition

DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'CompatibilityLevel', @description=N'This condition will check Compatibility Level Of all databases in SQL Server Iinstance if it is SQL Server 2014?', @facet=N'Database', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>EQ</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>Numeric</TypeClass>
    <Name>CompatibilityLevel</Name>
  </Attribute>
  <Function>
    <TypeClass>Numeric</TypeClass>
    <FunctionType>Enum</FunctionType>
    <ReturnType>Numeric</ReturnType>
    <Count>2</Count>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>Microsoft.SqlServer.Management.Smo.CompatibilityLevel</Value>
    </Constant>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>Version120</Value>
    </Constant>
  </Function>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id
GO
'



How to Create Policy Conditions in SQL Server - SQL Server DBA Tutorial