Feature synergy in SQL Server 2008

SQL Server 2005 introduced some new features that left “traditional” SQL folks puzzled. What’s the hidden use for SQLCLR? Or the XML data type? Or Service Broker? Besides being available to you as a programmer, these are all used internally. 

I once had a conversation with a database person (not SQL Server) who, when asked about the viability of certain features in his database product for the general database developer, responded that “although there are only a handful of users using feature X, we use it internally in the product.” So its always interesting to observe the SQL Server team using its own features to implement other features. Here’s a couple of examples from SQL Server 2008.

SQL Server 2008 includes PowerShell integration and a PowerShell provider for SQL Server that allows you to navigate the database and policy object models using a file paradigm. Where else are they using PowerShell in the product?

A glance at the BOL reveals that there are a few new SMO (SQL Server Management Object) libraries, with names ending in DMF (e.g. Microsoft.SqlServer.Management.Dmf). These are used to setup and manage policy. According to this Policy-Based Management webcast, the libraries are used both internally (via SQLCLR) and externally (hosted by PowerShell) to define and check policy. Remember DMF stands for declarative management framework, the original name for the feature now known as Policy-Based Management.

Another use of PowerShell in SQL Server 2008 is as a subystem for SQL Agent jobs. In addition to the SQL Server 2005 Agent subsystems, you can use the PowerShell subsystem with jobs. Create a new job and jobstep to see PowerShell as a choice.

These (PowerShell and Policy) are also used together when you use an “on-schedule” policy execution mode. Choosing this mode schedules a SQL Agent job that uses a job step hosted by PowerShell that invokes a PowerShell cmdlet named Evaluate-Policy. The help file for this cmdlet says it “supports the SQL Server 2008 infrastructure”. By the way, “On change – log only” policy execution mode uses Service Broker for its implementation.

Another example of synergy between new features involves the extended event feature. Extended events are implemented via an event engine built into SQL Server. Individual events are lightweight to fire and the infrastructure is amazingly flexible. Events can be associated with actions that pick up addition info, filtered via predicates, and dispatched synchronously or asynchronously to a variety of event targets.

Besides the events,actions,target, etc used for diagnosis, Extended Events are also used to implement to new SQL Server 2008 Auditing feature. Auditing has its own package of events,actions,data types, and targets which are used when you define an AUDIT in DDL, but because they are just “ordinary” Extended Event objects, you can also use them in conjunction with any of the other Extended Event facilities.

That’s just a couple of examples of “feature synergy” in SQL Server 2008. I’ve blogged about others in SQL Server 2008 (e.g. SQLCLR UDTs for Spatial data types and hierarchyID) and I’ll blog about more examples as I come across them.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.