Although being able to use a GUI tool like SQL Server Management Studio is a nice feature by itself, it's unusual that fixing something in a GUI makes a feature (that hasn't changed) more useable. The "feature" is defaults on SQLCLR procedure parameters.

You can't have defaults on parameters in .NET, but you can specify them in the CREATE PROCEDURE DDL, for example:

CREATE PROCEDURE addwithdefaults (@x int, @z int out, @y int = 5)
AS EXTERNAL NAME sampleasm.StoredProcedures.AddWithDefaults

These work just like defaults in T-SQL procedures; if you don't specify the parameter, the SQLCLR code receives the default value when it is called. Dandy...it's always worked this way.

But, in SQL Server 2005 SSMS (and SMO) the SQLCLR procedure was always displayed as "No Default". Even though there's perfectly good information in the SQL Server metadata about the default. Some folks were convinced that, even though default parameters worked as they should, it was dangerous to use them. Say a DBA was to move the procedure from test to production by scripting the CREATE PROCEDURE statement from SSMS (or SMO). They'd lose the default and when the procedure was defined with the generated script, code that worked in test (depended on the default value) would break in production.

I just noticed in SQL Server 2008 SSMS that defaults ARE displayed and procedures ARE scripted correctly. Great. Let a thousand SQLCLR procedures with default parameters bloom...I guess.

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.

An alternative consists of creating a policy as part of a PolicyCategory. Each Policy is a member of exactly one PolicyCategory. The default PolicyCategory is the only one that "ships with the system", but you can define your own. If you don't specify otherwise in the code (SSMS has a Category dropdown on the Description page of the new Category dialog), your policy is a member of the default category.

Each PolicyCategory has a property that indicates whether it's manditory that a database subscribe to that category. If this property is true, each database has an implicit subscription to the category. If not, a database must explicitly subscribe. The code to define a PolicyCategory is straightforward:

static void CreateCategory(PolicyStore ps)
{
    PolicyCategory cat = new PolicyCategory(ps, "MyNewCategory");
    cat.MandateDatabaseSubscriptions = false;
    cat.Create();
}

To create a Policy that's a member of the PolicyCategory, simply use the aptly-named PolicyCategory property. If you're using a named PolicyCategory you may not want to restrict that policy to a specific database. Here's the changes to the Policy definition.

// No Condition On This One, applies to all databases, but must be subscribed to
//ts1.SetLevelCondition(ts1.GetLevel("Server/Database"), "FinanceDB");

// Name the category
p2.PolicyCategory = "MyNewCategory";

To create a PolicyCategorySubscription (for those categories that are not Manditory), the only interesting part is that you need a SqlSmoObject. You can use a concrete subclass (like Database) or make up a SqlSmoObject by using a URN. Dan Sullivan's and my book "Developer's Guide to SQL Server 2005" covers both ways to make an SqlSmoObject. Here's the code for PolicyCategorySubscription.

static void CreatePolicyCategorySubscription(PolicyStore ps)
{
    Server svr = new Server(); // open a connection to default instance, local server
    Database db = new Database(svr, "pubs");
    PolicyCategorySubscription subs = new PolicyCategorySubscription(ps, db);
    subs.PolicyCategory = "MyNewCategory";
    subs.Create();
}

This means that only the pubs database now follows the policies in "MyNewCategory". BTW, there's currently the PowerShell provider in CTP6 throws an error when attempting list a PolicyCategorySubscription (its in the hierarchy at the same level as Policy). The PolicyCategorySubscription still works as advertised though.

This concludes the series on Programming Policy-Based Management with SMO. Hope it was useful.

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 that this collection is similar to what you'd see for a MultipartName policy in the SSMS designer dialog. The title for it in SSMS is "Against Targets:". We want our policy to apply only to Tables. After fumbling around for a while attempting to define TargetSets and add them to the ObjectSet's collection of them, I found that the seven TargetSets I needed were *already* defined. This reduces enabling only the table's TargetSet to two lines of additional code. Notice that you can reference a specific TargetSet in an ObjectSet by using an indexer. The indexer is an SMO URL.

TargetSet ts1 = os1.TargetSets["Server/Database/Table"];
ts1.Enabled = true;

But how to restrict this policy to a single database? For this we need a Condition to name the database. Because this condition is simple we can use ExpressionNode.Parse(). Here's the Condition code.

// Create a condition to enforce
Condition con = new Condition(ps, "FinanceDB");
con.Facet = "Database";
// Note: Using Parse() treats the string as an SMO URL. Only works for simplest cases
string s = "@Name = 'finance'";    
// try-catch code omitted for brevity
con.ExpressionNode = ExpressionNode.Parse(s);
con.Create();

Back to our TargetSet. We restrict the TargetSet to a specific database by using TargetSet.SetLevelCondition. SetLevelCondition takes a TargetSetLevel, and we get the appropriate TargetSetLevel (which is prepopulated) by.... you guessed it...using a SMO URL. After ts1.Enabled, this limits the policy to a single database defined by our Condition.

// FinanceDB is the name of our Condition that "limit/defines" this policy only to Finance
ts1.SetLevelCondition(ts1.GetLevel("Server/Database"), "FinanceDB");

Having initialized the ObjectSet correctly, we now create the ObjectSet, tie it to the Policy and voila...

os1.Create();
p2.ObjectSet = "CheckFinanceTab_ObjectSet";
p2.Create();

Check this policy by using the following code in a query window:

use finance
go
create table dbo.foo (id int);
go

You get the expected error:
Policy 'CheckFinanceTab' has been violated by '/Server/(local)/Database/finance/Table/dbo.foo'.
This transaction will be rolled back.
Policy description: ''
Additional help: '' : ''.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 50
The transaction ended in the trigger. The batch has been aborted.

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 contains....
3. Condition that specifies a set of database objects to which the policy should be applied.

The first condition is straightforward

Condition con = new Condition(ps, "TablePattern");
con.Facet = "IMultipartNameFacet";
ExpressionNode exp = new ExpressionNodeOperator(OperatorType.LIKE,
    new ExpressionNodeAttribute("Name"),
    new ExpressionNodeConstant("fintbl%")
    );
con.ExpressionNode = exp;
con.Create();

Note that, as with the complex condition we specified previously (part 3), you can't specify 'LIKE' by using the Parse() method, but 'LIKE' does appear when you access the condition by using ToString(). Perhaps, in future, Parse and ToString will be reflexive on an ExpressionNode. Or maybe I'm just missing something.

The first part of the policy is straightforward too:

Policy p2 = new Policy(ps, "CheckFinanceTab");
p2.Condition = "TablePattern";
p2.AutomatedPolicyExecutionMode = AutomatedPolicyExecutionMode.Enforce;
p2.Enabled = true;

But we want this policy to apply to to the TABLES (not views, for example) and only those tables in the finance database. For this, we need an ObjectSet.

ObjectSet os1 = new ObjectSet(ps, "CheckFinanceTab_ObjectSet");
os1.Facet = "IMultipartNameFacet";

Note that ObjectSets reference Facets too. Note also that ObjectSets are a "top-level" object, that is, in the PowerShell provider they appear at the same level as Conditions and Policies. Note that Facets do NOT appear at that level.

Before we go any further, cavaet... there is no function in SSMS to define an ObjectSet standalone. Therefore, no way to delete a "half-baked" ObjectSet from SSMS if you don't want it. To delete an ObjectSet you don't want, you need to go into the PowerShell provider, navigate to SQLSERVER:\SQLPolicy\{server}\{instance}\ObjectSets and delete it. "del MyObjectSet". In SSMS, deleting a policy that refers to an ObjectSet deletes the ObjectSet too. Not sure if you can have multiple policies refer to the same ObjectSet yet, but if you could I assume you'd have to delete all the policies before an ObjectSet would go away.

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, Attribute, and Function. I need: DatabaseMailEnabled (Attribute), Equals (Operator) and false (Function). BTW, ExpressionNodeFunction appears to be what you are programming when you use the "Advanced Functions" dialog in the SSMS Condition dialog.

Putting one expression together first looks like this:

ExpressionNode exp1 = new ExpressionNodeOperator(OperatorType.EQ,
    new ExpressionNodeAttribute("DatabaseMailEnabled"),
    new ExpressionNodeFunction(ExpressionNodeFunction.Function.False)
    );

Note that, unlike in SSMS, you don't need '@' before the attribute name. But I need to specify both DatabaseMail and SqlMail. That's just a little more complex.

ExpressionNode exp1 = new ExpressionNodeOperator(OperatorType.EQ,
    new ExpressionNodeAttribute("DatabaseMailEnabled"),
    new ExpressionNodeFunction(ExpressionNodeFunction.Function.False)
    );
ExpressionNode exp2 = new ExpressionNodeOperator(OperatorType.EQ,
    new ExpressionNodeAttribute("SqlMailEnabled"),
    new ExpressionNodeFunction(ExpressionNodeFunction.Function.False)
    );
ExpressionNode exp_both = new ExpressionNodeOperator(OperatorType.AND, exp1, exp2);

con1.ExpressionNode = exp_both;
con1.Create();

And, nice as you please. Looks just like the one defined in SSMS using the GUI. Make sure that you remove (or comment out) the original ExpressionNode.Parse statement.

Now, on to the policy.

// Create a policy that uses the condition we just created
Policy p1 = new Policy(ps, "OffByDefaultSMO");
p1.Condition = "MailOffSMO";
p1.AutomatedPolicyExecutionMode = AutomatedPolicyExecutionMode.None;
p1.Enabled = false;
p1.Create();

Looks like the one created by the GUI and works like it too.

This is a pretty simple policy, because it can only be applied at the instance level. Next, we'll look at a policy that can be applied to a set of database objects, e.g. All tables in a particular database. This requires that we investigate ObjectSets.

Cavaet. It's pretty easy to make a mistake, sans docs. Some mistakes produce a generic "I can't do this"-type message. So ALWAYS drill into the INNER exception if you get an error. That is:

try
{
    con1.ExpressionNode = exp_both;
    con1.Create();
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    if (ex.InnerException != null)
    {
        Console.WriteLine(ex.InnerException.Message);
    }
}

Stack trace might even be helpful.

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(
                        "@DatabaseMailEnabled = 0 and @SqlMailEnabled = 0");
con1.Create();

The Condition class uses PolicyStore instance (ps) and names the condition (MailOffSMO). You initialize a facet with a string. So where did the string come from? It is named in the SMO library but I "found" it by making an equivalent condition with SSMS and inspecting it.

foreach (Condition c in ps.Conditions)
 // ... look at c in the VS visualizer

ExpressionNode should "define" my condition, and since there is no ExpressionNode constructor, I first tried Parse(). This created the Condition, but it was unusable in SSMS. SSMS wanted "false" not "0". I searched around for how to specify "false" in the parse string for a while, then came upon something better.

ExpressionNode has six subclasses that can be used in combination to specify any set of expressions that you need. These are
  ExpressionNodeAttribute
  ExpressionNodeConstant
  ExpressionNodeChildren (with subclasses)
    ExpressionNodeFunction, ExpressionNodeGroup, and ExpressionNodeOperation

That's next.

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.

Sorry, I know that slogan has been used already. Thanks to everyone who showed up for the SMO/Powershell chalk talk at TechEd Barcelona today. Here, as promised, are the demos. Thanks especially to Jeffrey Snover, the "dad" of Powershell for showing up and showing me some neat shortcuts... as I typed. Many of the scripts were written "on the fly" and really need better argument validation and error checking, but they seem to do the job. Some of these come from the SMO chapter in Dan Sullivan and my "A Developer's Guide to SQL Server 2005 " book. Enjoy!

I'm in Barcelona in TechEd and tomorrow I'm going to be doing a chalk talk on SQL Server Management Objects (SMO). Although I'd usually done my SMO coding in C#, I decided (based on my co-author Dan Sullivan's blog post on the subject) to try out using Powershell along with SMO. So, although when most folks think of SQL "scripts" being lines of code written in T-SQL, with SMO and Powershell the term "SQL scripts" takes on a somewhat different meaning. If you're at TechEd, stop by and we'll write some new-fangled "SQL scripts". Although some things that SMO does actually generate registry changes, the "real" T-SQL equivalents for most things SMO are still available using the SMO scripter.

I've been looking at the SMO (SQL Server Management Objects) model recently. Because the amount of data they retrieve is potentially large and potentially expensive to produce (say sp_spaceused on every database), they use most/all of the *data transfer optimization* techniques: both sparse and eager population, lazy instanciation, caching, deferred/chunky writeback, and locality of reference. Reasonable, documented defaults with progammable overrides.

What's this got to do with object-relational mapping? Well, nothing. And everything. These techniques are data optimization techniques. They are the same whether you talk about them in terms of SQL SELECT statements or objects. The fact that pieces of data in a database are mapped to properties of classes in an object model (data model mapping) is only peripherally related to data access optimization, at best.

Before I go forward, I'll state that I don't consider myself an O/R mapping (or XML-relational or XML-object) bigot on either side, either for it or against it. Really. But many folks, especially when they are bashing mapping, use the data transfer optimization techniques (along with another data access concept - locality of reference) to justify their arguments. That's like me blaming the inventor of the knife and fork for my current extra weight. Data optimization, badly implemented, will kill a data-centric object model. But you can accomplish the same thing with bare SQL statements and DataReaders/Rowsets.

For an easy to picture example, imagine paging through a large resultset in ASP.NET. You can:
1. Cache the entire resultset (eager population + caching)
2. Use TOP queries to SELECT on a per-page basis using a custom ASP.NET pager
3. Send the whole bunch to client for paging (eager population + locality of reference)
4. Fetch entire resultset each time and throw all but one-page away. (bad perf).

You can accomplish all this, including the bad design, with DataReaders only, using DataSets for your cache. Not an object-mapped-to-a-relation in sight. Another easy-to-picture example is the Windows file explorer, with plus signs next to each directory. Sparse population/lazy instanciation. The “show details“ setting: sparse property population choice.

Here's a few more quick examples, using ObjectSpaces (technology on-hold, but this was the latest implementation I'd studied) and SQL statements.

// baseline
ObjectReader<Customers> or = os.GetObjectReader<Customer>("");

SqlCommand cmd = new SqlCommand("select * from customers");
SqlDataReader rdr = cmd.ExecuteReader();

// eager population
ObjectReader<Customers> or = os.GetObjectReader<Customer>("", "Orders, Details");

SqlCommand cmd = new SqlCommand(
 "select a.*, b.*, c.* from customers a
  inner join orders b on a.custid = b.custid
  inner join details c on b.orderid = c.orderid");
SqlDataReader rdr = cmd.ExecuteReader();

// sparse population
// customers contains a customer photo
// sparse population in mapping file, can be overriden on query
ObjectReader<Customers> or = os.GetObjectReader<Customer>("");

// leave customer photo out of the SELECT
SqlCommand cmd = new SqlCommand("select customerid, customer_name... from customers");
SqlDataReader rdr = cmd.ExecuteReader();

SO...is mapping among data models a bad idea, or is not knowing/designing for/using the data optimization techniques a bad idea? Mapping objects to data *badly* (or having a model so narrow there are no choices) is just as bad as writing bad SELECTs. You have to know where your data lives, how expensive it is to produce it, and how you are going to use it in ANY case. O/R mapping DOESN'T ABSOLVE YOU from that responsibility. But bad designs are the result of uninformed designers, not a specific technology. Thinking that a specific technology is a panacea and now you don't have to consider design, is also dangerous.