If you've seen some of my recent posts, you know that Extended Events have been on my radar. So let's look at some Connect items for XEvents.
Powershell takes center stage for this post. Previously, I showed how to handle a SQL Server Extended Event in C# by accessing the event_stream target. We can do the same thing in PowerShell. The code translates mostly line-for-line from C#. Check out the last post if you want the full back story. Otherwise, continue on for the script and some Posh-specific notes.
It took me a while to make the transition from SQL Profiler to Extended Events. Eventually I got comfortable enough with it to use it 100% of the time. As I read more about the XEvents architecture (as opposed to just "using" XEvents), I gained a deeper appreciation of just how great the feature is. My only gripe is that there isn't a way to handle the related events from within SQL Server using T-SQL. DDL triggers can't be created for XEvents. And they can't be targeted to Service Broker for Event Notifications (not yet, anyway). For now, the one way I know of to handle an XEvent is by accessing the event_stream target via the .NET framework. I'll demonstrate with C#.
Many of the SQL Server Extended Events Targets are asynchronous. If you are new to Extended Events, this may be a little confusing, if not off-putting. And that's understandable. What's one to think when certain events have occurred, but you can't "see" any evidence of them? Most often, this is addressed by changing one or more of the Event Session options, such as MAX_DISPATCH_LATENCY. But there's another potentially confusing issue I want to take a look at: an inconsistency between the event_stream target and other targets. If you use the "Watch Live Data" grid within SSMS, this will affect you.
2016 was my first full year of blogging. I was hoping to average one post per week, but came up short. 40 posts for the year still seems respectable. The slowest month was December with just one post. No big surprise there. I think most of us are less productive during the holidays. I went crazy in April and cranked out six posts.
For T-SQL Tuesday #85, Kenneth Fisher (b|t) says "let’s hear it for backup and recovery!". I'm sure if you follow along with the others who participate, you'll see plenty of articles that reinforce the importance of database backups. I'll be right there with them in agreement. But I want to take this in a different direction. This may sound like heresy, but restoring a backup can be a bad idea. I'll explain why, and present some alternatives.
In the last Event Handling post, I looked at handling SQL Server events synchronously with DDL triggers. However, there are many events that can't be handled synchronously within the scope of a transaction--DDL triggers can't be used for those. Take the AUDIT_CHANGE_DATABASE_OWNER event, for instance. If you attempt to create a DDL trigger for it, you'll get this error:
I ran into a problem with a DBCC command recently. It involved an application running index maintenance on a daily basis. I can appreciate what the software vendor was trying to do. They probably sell their product to a lot of organizations that don't
My personal experience with PASS events is somewhat modest. I've attended the PASS Summit conference twice, I've only been to one SQL Saturday, and attendance at my local PASS chapter spans less than two years. In spite of my limited experience, I've come to this conclusion: code samples and/or live demos are better than PowerPoint slides. Now, this may be a contentious topic. I want to stress that this is my personal opinion. You may decide I'm dead wrong. So keep that in mind and please continue reading.
PASS Summit 2016 is coming very soon. If 2016 is going to be your first time at the conference, you may be looking for (or have already received) some advice. I've been seeing quite a bit of advice myself, much of it quite good. I do suspect that most of the advisers are seasoned veterans with many years of conference experience under their belts. I'd like to offer a different perspective. 2015 was my first PASS Summit, so as a sophomore, my point of view might be a little different. Hopefully, it will be relatable to you, the first-timer.