The Denali XEvent SMO library

I've been looking at the Extended Event library and PowerShell provider "directory" in Denali and decided that I like it. It's a traditional SMO set of classes, which means there's not a whole lot new to learn.

All the SMO libraries connect to a "store" using either an integrated security default connection or an SqlStoreConnection. SfcStoreConnection has a constructor that takes a SqlConnection. All the stores expose collections of database objects and each object may have its own collection of objects (e.g. tables contain columns, XEvent sessions contain events). Each object has Create, Drop, and Alter methods that represent CREATE, ALTER, and DROP in SQL DDL.

The XEvent library reflects the XEvent metadata views and DMVs and is divided into metadata about the event system (these are the "Info" classes, e.g.SessionInfo) and information about the store's event system objects (e.g. Sessions, Actions, etc). Reference Mike Wachal's blog post for a nice diagram of the object model.

The XEvent library continues on a path started if I recall, by the PBM libraries. There is a method on the XEStore to CreateSessionFromTemplate and a static method, SaveSessionToTemplate. These load and save session definitions from XML files. Although this likely corresponds to template files in SQLProfiler, it also means that XEvent sessions can be loaded into the store in different different ways: SQL DDL, direct SMO programming in .NET languages (like PowerShell), and importing XML files. I kind of like the "import XML" way as an easy way to move Session or Policy definitions among instances. Although there's a few examples of using PowerShell to create/manipulate XEvent sessions in BOL, I'd likely choose DDL over this method. With PBM, the choice between system sprocs and PowerShell is more of a tossup.

The biggest user of SMO has always been SQL Server Management Studio. Although the SSMS interface is currently limited to Session enumeration Import/Export Session definition, the model is set to allow much more.

@bobbeauch

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.