Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to create Policies using PowerShell scripts

By Jayakumar Krishnan,

Introduction

As you know that SQL Server 2008 supports Windows PowerShell and is a powerful scripting shell that helps administrators to automate and built robust server administration scripts. In my previous articles I’ve written about Using SQL Server 2008 PBM, how to monitor Free Space of data file(s) and How to Evaluate Policies on Multiple Instances using PowerShell scripts. In this article I’m going to walk you through how to create policies using PowerShell script.

In SQL 2008, policies can be created in two ways. One is using SSMS (SQL Server Management Studio) wizard and second is using PowerShell script. In my scenario I’ve been requested to create this script to deploy/create policies as a POST installation step. Suppose if you need to use this script for unattended SQL installations then make sure that this script called only after a complete SQL installation done on a server, because you need a SQL policy store to create your policies.

To simplify the code for readers I’ve divided the script in to five parts and described them as below

0. Load required assemblies - If you need to run script from windows command line. When you run/call the PowerShell script from Windows command line prompt and if the script using any assemblies that need to be loaded before it used. But this step is not required if you run/call in the PowerShell command window.

1. Policy & Condition declaration - In the script the Policy and Condition name definitions are passed through parameters that would be lot easier to update them later if needed.

2. Create Policy Store Connection - Before you create a policy or condition on SQL Server of course you need to connect to the policy store, in this part of code connecting the SQLDBOps instance to create your policy.

3. Create Conditions - Through a policy if you want to do an audit/validate a target in SQL server you need to specify the condition which achieves your goal.

4. Create Objectset - Objectset is a condition specifies a set of objects to which the policy should be applied. Which means in a SQL server there are numerous objects but through the condition you have to narrow down to which object needs to be audited/evaluated. Here you are evaluating data files and a SQL server may have many databases and each database may have several files and file groups. Here I’m using an object set to evaluate this policy only on user databases.

5. Create Policy - This script will create the policy "DataFile_FreeSpace_Check" on SQLDBOps server.

Here is the script.

#0. Load required assemblies if you need to run script from windows command line.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Management.Sdk.Sfc')

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Dmf')

# 1.Policy & Condition declaration

$policyName = "DataFile_FreeSpace_Check"

$conditionName = "DataFile_Free_Space"

$conditionName2 = "UserDatabasesOnly"

$ObjectSetName = "UserDatabasesOnly_ObjSet"

$ManagementServer="SQLDBOps"

#2. Create Policy Store Connection

$conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$ManagementServer;Trusted_Connection=true");

$PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);

#3. Create Conditions

$condition = New-Object ('Microsoft.SqlServer.Management.Dmf.Condition')($conn, $conditionName)

#Make sure there is space between words, otherwise you have to break your head (Error 1)

$condition.Facet = 'DataFile'

$condition.ExpressionNode = '@AvailableSpace >= Multiply(@Size, 0.1)'

$Condition.Create()

$condition = New-Object ('Microsoft.SqlServer.Management.Dmf.Condition')($conn, $conditionName2)

$condition.Facet = 'Database'

$condition.ExpressionNode = '@ID >= 4'

$Condition.Create()

#4. Create Objectset

$ObjectSet = New-Object ('Microsoft.SQLServer.Management.Dmf.ObjectSet')($Conn, $ObjectSetName)

$ObjectSet.Facet = 'DataFile'

$targetSet = $ObjectSet.TargetSets["Server/Database/FileGroup/File"]

$targetSet.Enabled = 1

$targetSet.SetLevelCondition($targetSet.GetLevel("Server/Database"),$conditionName2)

$objectSet.Create()

#5. Create Policy

$Policy = New-Object Microsoft.SQLServer.Management.Dmf.Policy ($Conn, $policyName)

$Policy.Condition=$conditionName

$Policy.AutomatedPolicyEvaluationMode="None"

$Policy.Objectset = $ObjectSetName

$Policy.Create()

Save the above script in a .ps1 (PowerShell) file and run it on PowerShell window as below example.

.\CreatePolicy.ps1

If you want to call the script on windows command line then call the script file as below example.

PowerShell –File “C:\Foldername\CreatePolicy.ps1”

Also make sure that load assembly script exists in your script file otherwise you will get the below errors.

New-Object : Cannot find type [Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection]: make sure the assembly containing this type is loaded.

New-Object : Cannot find type [Microsoft.SqlServer.Management.DMF.PolicyStore]: make sure the assembly containing this type is loaded.

[Special thanks to Sethu Srinivasan at Microsoft to figure out the above issue]

Conclusion

I hope this script will help DBA’s to create policies from PowerShell prompt / windows command line. Based on the above method you can create other polices with conditions for your environment.

References

Resources:

Howto_create_policies_using_Powershell_script1.docx
Total article views: 3781 | Views in the last 30 days: 9
 
Related Articles
ARTICLE

Creating a Basic Policy in SSMS - SQL School Video

In SQL Server 2008, we have a new way of enforcing rules on our servers. This video has MVP Brian Kn...

FORUM

SQL 2005 Policy tsql scripts

TSQL Scripts to evaluate security policies and set security settings

FORUM

SQL Server 2008 Policy Management

SQL Server 2008 Policy Management - Server Roles

FORUM

SQL Server 2008 - Policy

SQL Server 2008 - Policy

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones