Remote DAC And Vulnerability Assessments

Max Vernon points out a SQL Server Management Studio Vulnerability Assessment check which seems somewhat incomplete:

Certainly, you’d want to ensure the port for the DAC is not available to the Internet, but hopefully if you’re reading this blog you already know how silly it would be to open SQL Server to the Internet.

Assuming you don’t have the port open to the Internet, it’s very likely the DAC will not be of any use at all if you disable Remote Admin Connections as advised in the Vulnerability Assessment. My advice is to ignore this warning completely and configure the DAC to allow remote connections. Microsoft Technet has documentation about using the DAC, and says to configure it for remote connections by logging onto the server locally first, then configuring SQL Server to allow remote DAC connections, which seems a bit like putting the cart before the horse.

Read the whole thing.  I agree with Max’s assessment that if there are some basic controls around your instance (like not letting SQL Server be Internet-accessible, putting SQL Server instances in a protected subnet, etc.), remote DAC is definitely useful enough to keep running.

Victimless Deadlocks And SSMS

Michael J. Swart shows a scenario where the deadlock graph fails to open in SQL Server Management Studio:

I recently got this error in Management Studio when trying to view a deadlock graph that was collected with an extended events session:

Failed to initialize deadlock control.
Key cannot be null.
Parameter name: key

I found this error in a session that included the xml_deadlock_report event.

Read on for more information, and do check the comments where Lonny Niederstadt points out that even a victimless deadlocking scenario can have an ultimate victim:  performance.

Full-Screen SSMS

Wayne Sheffield has another SSMS tip for us:

Do you ever find yourself working on a query and realize that you need just a bit more real estate in the SSMS window? Or perhaps you find that all the toolbars, menus, etc. are cluttering things up? To solve these issues, you can toggle the full screen mode in SSMS on. It will remove all that clutter and maximize the query window. Below, you can see a cluttered SSMS with two rows of buttons, and toolbars on both sides of it.

Click through to see how to enable full-screen mode.

Classifying Data In SSMS

Steve Jones gives SQL Server Management Studio 17.5 a spin and tries to classify some data:

There’s a getting started link, which takes me to the SQL Server Security Blog. I suspect that’s an incorrect link. I think it should go here: SQL Data Discovery and Classification.

Below this, I see a list of the recommendations. This has grabbed tables that appear to continue to contain some data that might be sensitive and require classification. One of the tenets of the GDPR  is that you know your data. You aren’t allowed to figure this out later, but rather you must proactively know what data you are collecting and processing.

It’s a good overview of the feature.  Like Steve mentions, I appreciate this data being stored as extended properties:  that way, third party and custom-built tools can make use of it.  You can also script them out for migration.

SSMS 17.5 Released

Alan Yu announces SQL Server Management Studio 17.5:

SSMS 17.5 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.5 comes with several exciting new features:

  • Data Discovery and Classification is a new feature for discovering, classifying, labeling and reporting sensitive data in your databases.
  • Query Editor now has support for a SkipsRows option for the Delimited Text External File Format for Azure SQL Data Warehouse.
  • Showplan has enabled the display of estimated plan button for SQL Data Warehouse.

SSMS 17.5 also includes key bug fixes to Showplan, Query Editor, SMO, and Templates, which can be found in the Release Notes.

Aside from data discovery and classification, it’s probably not a major update for most people, but this does look like an interesting tool.

More Keyboard Shortcuts

Andy Mallon has a great shortcuts cheat sheet for SQL Server Management Studio and SQL Operations Studio:

Nearly two year ago, I first published my Shortcuts cheat sheet. Since then, thousands of people have downloaded it. I’ll be the first to admit that I didn’t expect it to be as much of a hit as it has been. When I give my one-hour talk in person, I bring card stock handouts of my cheat sheets, too. I also ask people for their favorite shortcuts, and I’ve learned some great new hidden gems.

I’ve been working on some updates, and the updated version is ready to go. I’ve added a bunch more shortcuts, and even added shortcuts for SQL Operations StudioIt’s two pages now, for double the fun!

That’s great stuff.  Learning these keyboard shortcuts will provide a nice marginal benefit to your productivity.

Wrapping Up SSMS Tricks

Wayne Sheffield wraps up his 31 SSMS tricks in 31 days series.  First, he shows off the presentation settings in SSMS.  Presenter mode isn’t nearly as full-featured as I’d like to see it be, but it’s an improvement over the old version, at least.

He then shows standard reports built into SSMS.

The last post in the series is all about the Activity Monitor:

The Activity Monitor can be useful for seeing a mile-high view of a SQL Server instance. However, leaving it running can be as big a drag on the instance as is the use of SQL Profiler. The Resource Waits section of Activity Monitor, which would have been one of the strongest features, has been dumbed down by the filtering of wait types, and that many others are grouped together into categories. Sure, using the Activity Monitor convenient – but spend the time to develop your own scripts or XE sessions to get this information in a more efficient, with less impact. Overall, refrain from using it… and especially don’t set up SSMS to open it automatically on startup.

Agreed.  For all of Wayne’s tips and tricks, check out his index page.

Finding Errors In A SQL Query Using SSMS

Bert Wagner points out a useful feature of SSMS:

While all of these error messages point me in the right direction, I’m too lazyto try and remember how each version of SSMS refers to the error location. This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.

There is one feature that works consistently though that makes navigating to the specific error lines easily.

Click through for that answer.  It doesn’t work for dynamic SQL, but it can be quite helpful when it does work.

Goodbye, SQLPS

Max Trinidad notes that SSMS 17.4 gets rid of the SQLPS module:

After SSMS Version 17.4 was release back in December, SQLPS module is no longer available. So, if you try to use the “Start PowerShell” from any of the database object, you’ll get the message “No SQL Server cmdlets found…” popup message.

And good riddance.  Even in 2008, the SQLPS method of dealing with Powershell was obsolete, as Powershell modules were supposed to be snap-ins rather than independent shells.  The SQL Server Powershell module is a major improvement in that regard.

More SSMS Tips And Tricks

Wayne Sheffield has another batch of SSMS tips and tricks for us.  First, he provides some helpful hints with comments.  Then comes a useful addition to SSMS 2016, comparing query plans:

Notice that various options have a colored non-equals icon. Here you can quickly see the various values that are different between the two execution plans.

At the bottom of the execution plans is a Showplan Analysis window. This window has color-coded keys for various sections of the plan:

He also shows how to import and export your SSMS configuration settings.  This makes it easier to migrate to a different machine or keep your desktop and laptop looking the same.


March 2018
« Feb