Powershell Day 28

Day 27 SQL Policy

I highly recommand you all to go through the Policy –based management (BCM)’s official site to learn more about PBM




Policy-Based Management (PBM).is introduced from sql server 2008 onwards. Policy means making a sql standard to follow and restrict the user/developer/dba to follow it. That way the system will be more systematic and stable and easy to manage. Using PBM we can maintain the system compliance  as well.

More detail here: http://blogs.msdn.com/b/sqlpbm/archive/2008/04/20/policy-based-management-overview.aspx




Below link Don Jones explains different terms for policy


Eg.. if we want to keep all our stored procedure name to be start with “usp_%” we can restrict it using policy


PBM Terms:

Policy: compliance or restriction .

Condition : here our condition is stored procedure name should be like “usp[_]%”

Facet: Stored procedure name /multi task names. There are several facets available in sql server

Target Type :Stored procedures

Here is a good example detail how to create a policy using GUI:http://blogs.msdn.com/b/sqlpbm/archive/2008/04/26/creating-a-policy-with-the-gui.aspx

Here is another one to enforce recovery model to FULL : http://www.sqlcoffee.com/SQLServer2008_0002.htm


PBM using Powershell:

Now you might have got an idea about what is PBM and how to configure it using SSMS. As this blog series is about Powershell a day. Lets start how can we work PBM using Powershell. As discussed about mini shell (Sqlps) in our previous blog Day 16 and Day 17. Sql Powershell also support SQLPolicy folder

When we open sqlps we will get the prompt


In this prompt when we see dir, we get several sub folders which includes “SQLPolicy”

As you gone though the links above how to create the PBM using GUI. For Powershell also you may have to go with the same steps

  1. Create a connection object from sql server

$Conn=New-Object System.Data.SqlClient.SqlConnection



  1. create sdk connection for PolicyStore

$sConn=New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($Conn)

  1. Create object for policystore – Microsoft.SqlServer.Management.Dmf.PolicyStore

$Pstore=New-Object Microsoft.SqlServer.Management.Dmf.PolicyStore ($sConn)

  1. Create a condition object with policystore

$condition=New-Object Microsoft.SqlServer.Management.Dmf.Condition ($Pstore,‘SP2’)


$operator=New-Object Microsoft.SqlServer.Management.Dmf.ExpressionNodeOperator(“LIKE”, “@Name”, “’usp%’”)




Now once condition is created. Associate that condition into obectset for all the sp .

$objectSet=New-Object Microsoft.SqlServer.Management.Dmf.ObjectSet($Pstore,‘SP_ ObjectSet’)



$objectSet.Create() | Out-Null

#Create policy

$SP_policy=New-Object Microsoft.SqlServer.Management.Dmf.Policy ($Pstore,”SP_Policy”)





$policy.Create() | Out-Null


To evaluate the policy




 *Reference : Microsoft.SQL.Server.2008.Administration.with.Windows.PowerShell–by MAK & Yan

This entry was posted in Powershell and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s