Muscle Memory And CTRL+F

2017-01-26 0 Comments

I experienced a handful of "firsts" this month. I installed Windows 10, followed soon after by installing SQL Server 2016 and SSMS 2016. It didn't take long for me to hit the CTRL+F keyboard shortcut in SSMS, which of course opens the "Find" dialogue. In the particular version of the Visual Studio Shell that SSMS is built upon, the Find dialogue is quite different than in previous of the shell. But this wasn't a shock to me. I'd seen in before in other Microsoft products including Visual Studio Express and Visual Studio Community.


Restoring An Encrypted Backup Of [master]

2017-01-18 0 Comments

Restoring a backup of [master] from one instance to another is challenging. The source and target instances have to be the same version. And after [master] is restored, the instance won't start if it can't find the files for [model] or the path for [tempdb] files. Those are just a few concerns that I can think of off the top of my head. Here's another one I've added to my list: encryption. No, I'm not using TDE, but I am using native backup encryption.


A (Very Small) Piece of Celtics History

2017-01-17 0 Comments

On this date 27 years ago, the Boston Celtics came to Orlando for the first time--well, it was the first time they played against the Orlando Magic on the road. I was a very tiny part of that history. Way back in January of 1990, I was just starting the Spring semester of my third year at UCF. Monday the 15th was Martin Luther King Day, and there were no classes that day.


T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

2017-01-10 0 Comments

For T-SQL Tuesday #86, Brent Ozar (b|t) asks us to "Find the most interesting bug or enhancement request..." (on ", and write a blog post about it."

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.


SQL Server Extended Event Handling via Powershell

2017-01-09 0 Comments

Dave Mason - SQL Server Event Handling: Extended Events Watcher via PowerShell

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.


SQL Server Event Handling: Extended Events

2017-01-05 0 Comments

Dave Mason - SQL Server Event Handling: Extended Events

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#.


Hide & Seek With Extended Events

2017-01-02 0 Comments

Dave Mason SQL Server Extended Events

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.