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

http://blogs.msdn.com/b/sqlpbm/

 

Introduction:-

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

 

Expression

 

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

PS SQLSERVER:\>

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

Declarative Management Framework (DMF)

Microsoft.SqlServer.Management.Dmf.PolicyStore

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.dmf(SQL.100).aspx

http://blogs.msdn.com/b/sethus/archive/2008/06/16/sql-2008-powershell-script-for-creating-a-policy-and-saving-to-file.aspx

 

1. Create a connection object from sql server

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

$Conn.ConnectionString=”Server=”server”;Database=master;IntegratedSecurity=True”

 

2. 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)

4. 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%’”)

$condition.ExpressionNode=$operator

$condition.Facet=’StoredProcedure’

$condition.create()

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.Facet=”StoredProcedure”

 

$objectSet.Create() | Out-Null

#Create policy

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

$sp_policy.Condition=$condition.Name

$sp_policy.ObjectSet=$objectSet.Name

$policy.AutomatedPolicyEvaluationMode=”Enforce”

$policy.Enabled=1

$policy.Create() | Out-Null

 

To evaluate the policy

Invoke-policyevaluate

http://msdn.microsoft.com/en-us/library/cc645987.aspx

 

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