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
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
No comments:
Post a Comment