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 thought I'd take a try at programming it using the new SMO libraries.
Because the docs are sparse (there are listing of the new classes but no description of what they do in BOL), I figured I'd start by writing C# code, because I like the visualizers (those components that allow you to drill into a heavily nested structures while debugging) in Visual Studio. Later on, I'll port these to PowerShell. I can also use PowerShell reflection capabilities and the new SQL Server provider to get a quick look a the structures.
Be aware of the fact that, since this API is so sparsely documented it could change by RTM. Always a consideration.
The new classes live, for the most part, in two libraries:
Microsoft.SqlServer.Dmf
Microsoft.SqlServer.Management.Sdk.Sfc
I also added references in the project to:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.PolicyEnum
Microsoft.SqlServer.Smo
I mean to start by replicating the two examples in the PBM (is that its new acronym?) books online tutorial. But first, we need a starting point. It's the PolicyStore class. The PolicyStore is also the machine-root of the PowerShell provider drive SQLSERVER:SQLPolicy\{machine}\{instance}.
You can initialize the PolicyStore's connection with an instance of SqlStoreConnection from the …Management.Sdk.Sfc namespace. Not sure what Sfc stands for, but being an old C++ programmer, perhaps its SQL Foundation Classes 😉 ? No matter. Luckily you can initialize a SqlStoreConnection with a plain old SqlConnection. So, lets connect to the store.
SqlConnection conn = new SqlConnection("server=zmv32;integrated security=sspi");
PolicyStore ps = new PolicyStore();
ps.SqlStoreConnection = new SqlStoreConnection(conn);
conn.Open();
Where to go from here? In the PowerShell provider, the subdirectories of the policy store are Conditions, Policies, ObjectSets, PolicyCategories, and PolicyCategorySubscriptions. To create the first policy (MailOffByDefault) we need a Condition and a Policy. That's next.