\nI'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.\n<\/p>\n
\nBecause 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.\n<\/p>\n
\nBe aware of the fact that, since this API is so sparsely documented it could change by RTM. Always a consideration.\n<\/p>\n
\nThe new classes live, for the most part, in two libraries:
\n Microsoft.SqlServer.Dmf
\n Microsoft.SqlServer.Management.Sdk.Sfc\n<\/p>\n
\nI also added references in the project to:
\n Microsoft.SqlServer.ConnectionInfo
\n Microsoft.SqlServer.PolicyEnum
\n Microsoft.SqlServer.Smo\n<\/p>\n
\nI 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}.\n<\/p>\n
\nYou 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 \ud83d\ude09 ? No matter. Luckily you can initialize a SqlStoreConnection with a plain old SqlConnection. So, lets connect to the store.\n<\/p>\n
\nSqlConnection conn = new SqlConnection("server=zmv32;integrated security=sspi");
\nPolicyStore ps = new PolicyStore();
\nps.SqlStoreConnection = new SqlStoreConnection(conn);
\nconn.Open();\n<\/p>\n
\nWhere 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,25,29],"tags":[],"class_list":["post-709","post","type-post","status-publish","format-standard","hentry","category-policy-based-management","category-smo","category-sql-server-2008"],"yoast_head":"\n