Using PowerShell to program SQL Server’s Policies and Data Collection

I received a question today about whether I'd converted my Policy-Based Management examples using SMO (see the multi-part "Programming Policy-Based Management with SMO" series, starting here) from C# to PowerShell yet. I did do this a while ago; they're available as a script download on the SQLskills website (look on the "Past Conferences" page under TechEd […]

Programming Policy-Based Management with SMO – Part 6 – Categories, Subscriptions, Wrapup

This is the last part of a series on programming policy-based management. The series starts here. In the previous installment, I created a policy that was constrained to a single database. To accomplish this, I used a Condition that called out the database by name, and tied it to the TargetSet using TargetSet's SetLevelCondition method. […]

Programming Policy-Based Management with SMO – Part 5 – TargetSets and TargetSetLevels

This post is part of a series on programming policy-based management. The series begins here. So, we were working with a policy that required an ObjectSet. ObjectSets contain TargetSets. For example, the ObjectSet for the naming policy (IMultipartName) we were working on needs a TargetSet for Procedure, Synonym, Table, Function, Type, View, and XmlSchemaCollection. Note […]

Programming Policy-Based Management with SMO – Part 4 – Introducing ObjectSets

This is part of a series on programming policy-based management. The series starts here. Now, we'll tackle programming a little bit more complicated policy. The table-naming standard that applies to a set of database objects. For this, we need three items: 1. Condition for defining the policy itself. 2. Policy that uses the condition and […]

Programming Policy-Based Management with SMO – Part 3 – ExpressionNode and Policy

This is part three of a series on programming policy-based management. The series starts here. So, to initialize my Condition's ExpressionNode I need more than just a string. It doesn't look like, at this time, every ExpressionNode CAN initialized with a string. But we can use the subclasses. The ones I need here are Operator, […]

Programming Policy-Based Management with SMO – Part 2 – Conditions

This is the second part in a series about programming policy-based management. The series starts here. To build our MailOffByDefault policy we need:    Condition that specifies properties and settings    Policy that uses the condition Condition first. This looks pretty straighforward. Condition con1 = new Condition(ps, "MailOffSMO"); con1.Facet = "ISurfaceAreaFacet"; con1.ExpressionNode = ExpressionNode.Parse(                         […]

Programming Policy-Based Management with SMO – Part 1 – Intro

I've been looking at the new Policy-Based Management (was: Declarative Management Framework) in SQL Server BOL. All of the BOL examples use the SSMS user-interface to define and maintain the policy store. While there will likely be 3 ways to configure PBM as there is with, say, replication (SSMS, SMO, and system stored procs), I […]