Wayne Sheffield

My blog about SQL Server

Browsing Posts published by Wayne Sheffield

As you already know, SQL Server runs as a service. And services require a service account to run under. While this service account is likely to be a domain service account, it could be an account on the local machine. To follow good security practices you would need to specify that this account:

  1. Has the password frequently changed.
  2. Nobody knows the password.
  3. Cannot be used to login locally.

First of all, changing the password on a normal service account requires someone to know the password. Furthermore, it requires a service restart to pick up the change. A standalone managed service account (sMSA) cannot be used across multiple devices. Additionally, they don’t have a single point of control for password management. Using a group managed service account (gMSA) can solve all of these issues.

Introducing gMSA

A gMSA is a sMSA that can be used across multiple devices, and where the Active Directory (AD) controls the password. PowerShell is used to configure a gMSA on the AD. The specific computers that it is allowed to be used on is configured using some more PowerShell commands. The AD will automatically update the password for the gMSA at the specified interval – without requiring a restart of the service! Because the AD automatically manages the password, nobody knows what the password is.

Not all services support a gMSA – but SQL Server does. During a SQL Server installation you can specify the gMSA account. The SQL Server Configuration Manager (SSCM) tool can be used to change an existing SQL Server instance to use a gMSA. After entering the gMSA account you simply do not enter a password. The server automatically retrieves the password from the AD.

The pitfalls of using a gMSA with SQL Server

As with almost all things, there is inevitably something that doesn’t work correctly. One thing that I found is that when the server is rebooted, the SQL Server services are not restarted. And I’m not the only person that has had this problem. After digging through the Windows event logs, we were able to figure out the issues. The service was not restarting because it was trying to do things before those services had been started. We were able to overcome this issue by creating service dependencies. A service dependency prevents the service (SQL Server) from starting up before other specified services are running.

Creating Service Dependencies

To create these dependencies, you have to use the Registry Editor. In the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLSERVER\DependOnService key, add the W32Time and Netlogon services to the existing KEYISO value:

Now when you open up the service property window and click on the Dependency tab, you will see:

When the server is restarted, the SQL Server service will successfully start up.

While you’re in the service property window, we also set the first and second failure options on the Recovery tab:

You can read more about creating a gMSA here.

In retrospect, this makes sense. The gMSA needs the computer to retrieve the password from AD, so Netlogon would be necessary. gMSA uses Kerberos, and Kerberos requires computers to have their clocks in sync, which requires having W32Time. Since SQL knows that a gMSA is being used (when installing, or in SSCM), you would think that it would just make this registry entry for you. But alas, it doesn’t, so you must.

Back to the Basics of SQL Server

Back to the SQL Server Basics with Wayne

Connecting to a SQL Server instance is one of the first things that you will do after installing SQL Server. There are different ways of connecting to the instance. Certain High Availability features offer other ways to connect. In this Back to Basics article, I’ll cover the various methods of connecting to a SQL Server instance.


The first configuration setting that impacts how you can connect to an instance is which protocols that are enabled. You can examine (and change) the enabled protocols from the SQL Server Configuration Manager (SSCM) tool:

Available Protocols for connecting to a SQL Server instance

Available Protocols for connecting to a SQL Server instance

In the left pane, expand the “SQL Server Network Configuration” node, and you will see a node for each instance of SQL Server installed on the computer. Click on one of the nodes, and you will see the available protocols for that instance. In the above image, you can see that I have three available protocols for the selected instance, and that they are all enabled. In a default installation, the “Named Pipes” protocol is not enabled. Additionally, the “TCP/IP” protocol is disabled for the Development edition of SQL Server. The “TCP/IP” protocol and remote Named Pipes are disabled in SQL Server Express editions. You can read all about these different providers at this Microsoft link.

Remote Connections

The next configuration item that has an impact on how you can connect to the SQL Server instance is whether the instance allows remote connections:

Configuring remote connections

Configuring remote connections

Alternatively, you can use T-SQL to see check this configuration:

See Jason Brimhall’s post on other ways to access the configuration options.

To configure remote access with T-SQL:

This is not an advanced option, so you don’t need to turn on “Show Advanced Options” first.

Connecting with Shared Memory

Shared memory can only connect to a SQL Server instance installed on the same computer. The normal method of connecting with Shared Memory is by the computer name (or computer name\instance name for a named instance). However, there are some alternatives. All of the methods for connecting by Shared Memory are:

  • computer name[\instance name]
  • .[\instance name]
  • localhost[\instance name]
  • (local)[\instance name]

Connecting with Named Pipes

A named pipes connection can be used to connect to SQL Server instances on the local area network. It has the format of \\.\pipe\sql\query, where the “.” has the format of “.[\service name of named instance]”. For my SQL 2012 instance, it would be \\.\pipe\MSSQL$SQL2012\sql\query. As above, the “.” indicates the local computer name. However, the following can be used in place of the “.”:

  • . (*)
  • Computer name
  • Computer’s IP address
  • localhost (*)
  • loopback (*)
  • (*) (loopback / localhost IP address)

(*) – instances on the local computer only

Connecting with TCP/IP

The method that you are most likely to use to connect to a remote SQL Server instance is TCP/IP. With this protocol, it is possible to connect to any SQL Server instance in the world. You can use the following for connecting to a SQL Server instance with the TCP/IP protocol:

  • Computer Name
  • Fully Qualified Name for the computer (domain only)
  • Computer’s IP Address
  • (*) loopback / localhost IP address)
  • loopback (*)

(*) – instances on the local computer only

Connecting to High Availability SQL Servers

If the SQL Server instance is installed on a cluster, then you can connect to the instance on the active node of the cluster by:

  • Cluster Name.
  • Fully Qualified Name for the cluster.
  • Cluster’s IP address.

If the SQL Server is a clustered instance, or an Availability Group (AG), then you can connect to it by:

  • Clustered instance name / AG name.
  • Fully Qualified Name for the clustered instance / AG.
  • IP address for the clustered instance / AG.

Note that the active node of the cluster is not necessarily the active node of the AG.

All of these options are only available through the TCP/IP protocol. Remember that they connect to the instance on the active node. The active node for an AG may be different than the active node for the cluster that the AG is on.

Testing out all of these connections

On my local machine, I have configured a connection using all of the non-domain ways available to me. I used a Registered Server group (see my recent post about Registered Servers), and it looks like this:

Local Connection Methods

Local Connection Methods

As seen by this image, the connections not using the Named Pipes protocol show as connected. This is already a great sign! Now, if I open up a Multi-Server Query and run the following statement, I can see each connection, what @@SERVERNAME returns for that connection, and the protocol used for connecting:

On my system, this returns:

We can see that these connections did in fact connect to the proper instance, and which protocol each connection uses.

Testing on an Availability Group

On a client’s system, I created all the TCP/IP and Shared memory connections. This screen shot shows all the possible connections:

AG Connection Methods

AG Connection Methods

When running the same query, all the new connections for the cluster / AG are under the TCP protocol. The cluster connections connect to Node 2 of the cluster; all the remaining connections connect to Node 1.

How can these help me?

By now, you’re probably saying to yourself “Great – but why should I care about any of this?”. By knowing these various methods, you can do various troubleshooting. Let’s say that you can connect on the local server using any of the Shared Memory protocols, but not the other protocols. You should check that the other protocols are enabled. If you can connect on the local computer only, but not remotely, check out the “Allow Remote Access” option. If you can connect by IP address, but not by names, you need to check out your DNS server.

The Wrap Up

In my first Back to Basics post, we have covered:

  1. How to enable a protocol for a SQL Server instance to communicate on.
  2. Enabling a SQL Server instance for remote communication.
  3. Identified a multitude of ways to establish a connection to a SQL Server instance.
  4. Various methods of connecting to a High Availability SQL Server instance.
  5. And finally, a demonstration showing all of the various ways of connecting to a SQL Server instance, and the protocol used.


Wayne's Tips and Tricks for SSMS

Wayne’s Tips and Tricks for SSMS

SQL Server Management Studio (SSMS ) is a program that many just launch daily (or only when they restart their computer), and they use it to navigate to various SQL Servers to manage them, either through the GUI or through a query. I’ve found that most people are just not aware of all that SSMS can do to help you be more productive. Therefore, I’m going to be posting a SSMS tip or trick daily for the month of January. I just couldn’t help myself – there’s so many more things that can be done in SSMS that I’m going to keep on adding to this series. It won’t be daily, but keep an eye out for more tips! Let me know if you would also like to for me to cover tools that integrate with SSMS.

During this month-long blog series, I’ll show many tips that SSMS can do that will help you improve your productivity. These tips do not require any third-party tools. I’ll be using this post as a landing page for the series, and I’ll be updating it daily. So, come back every day to get your next SSMS tips fix. To make it even easier, you can use this link to return to this page: bit.ly/MonthOfSSMS. And because I like giving you options, you can also use ssms.waynesheffield.com to jump to this page also.

Bonus Tip: Did you know that SSMS is now distributed independently from SQL Server? It is a free download, and can be installed in parallel with other versions of SSMS. It is compatible with SQL Server 2008+. While it doesn’t block access to SQL Server 2000 or 2005, some features might not work correctly.

Day One: SSMS Solutions
Day Two: Split Screens
Day Three: Tab Groups
Day Four: Pinned Tabs
Day Five: Working with Query Files
Day Six: Vertical Scroll Bar Map Mode
Day Seven: Template Variables
Day Eight: SQLCMD Mode
Day Nine: Multi-Server Queries
Day Ten: Regular Expressions
Day Eleven: Block Select/Replace
Day Twelve: Changing case
Day Thirteen: The SSMS Super Clipboard
Day Fourteen: Object Explorer Drag-N-Drop
Day Fifteen: Query Shortcuts in SSMS
Day Sixteen: Assign a keyboard shortcut to menu items
Day Seventeen: Add color to connections
Day Eighteen: Template Explorer
Day Nineteen: Use Snippets in SSMS to insert blocks of code
Day Twenty: Object Explorer Filtering
Day Twenty-One: Object Explorer Details
Day Twenty-Two: Outlining
Day Twenty-Three: Quick Editing Tips
Day Twenty-Four: Using Bookmarks to navigate code in SSMS
Day Twenty-Five: Using the SSMS Web Browser
Day Twenty-Six: Working with Comments
Day Twenty-Seven: Comparing Query Plans
Day Twenty-Eight: Saving SSMS Settings
Day Twenty-Nine: Presenting with SSMS
Day Thirty: Reports in SSMS
Day Thirty-One: SSMS Activity Monitor
Day Thirty-Two: Controlling what appears in the status bar and query tabs

When you run a query in SSMS, you are probably aware that the status bar contains many pieces of information about it. Things like the instance name that you are connected to, the login name used, and a few others. You can see them here:

Likewise, the tab that you are on has some information in it also:

Most of this information is the same in these two locations. However, did you know that you can control what all appears in the status bar and on the tab? These are all configurable in the SSMS Options (Tools menu | Options):

Not only can you control what appears in these locations, you can also control the color of the status bar. You can choose to not have duplicated information by putting some data in one place only. Or you can just de-clutter the tabs / status bar.

This post is for day thirty-two of my blog series “A Month of SSMS Tips” (Yes, I’ve gone beyond a month – there’s just so many things that SSMS can help you with!). I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.

SSMS provides an Activity Monitor, a process that displays various information about what all is going on with the instance. This is available by right-clicking on the server and selecting Activity Monitor, or by selecting the toolbar icon for it.

Either of these options will bring up the Activity Monitor in a new tab.

In this tab, you can get a quick overview of what is happening on this instance, in 4 specific areas. The “Processes” section shows you information about the current connections to the server. “Resource Waits” shows you what SQL Server is waiting on, grouped into categories. “Data File I/O” shows the activity in the database files, and “Recent Expensive Queries” shows the expensive queries in the plan cache. All of this information is readily available for you to drill into and investigate further.

In Options | Environment | Startup, you can configure Activity Monitor to automatically start when you run SSMS.

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.

This post is for day thirty-one of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.

SSMS has a lot of reports available for you to use. Most of the reports in SSMS are off of either the server node, or the database nodes. Simply right-click the node, and select reports. The server level reports are:

And the database level reports are:

Each of these reports provide useful, timely information.

Once you have used one of the reports, that report will be at the bottom of the reports menu in addition to being in the Standard Reports submenu:

In addition, you can add your own reports to these. The Custom Reports option allows you to build your own special reports. Jes Borland has a great article here for how to create your own custom reports.

This post is for day thirty of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.

If you are presenting with SSMS, then there are some extra steps that you need to take. These steps are all for the purpose of ensuring that everyone (especially those in the back of the room) can clearly see your demo in SSMS. The fonts used in SSMS and a few other settings need adjusted.

There are a few settings that I focus on: The Environment Font (for Object Explorer and other non-query SSMS windows), the Text Editor Font (for query windows), the Grid Results Font and the Text Results Font. Note that changing the Grid Results Font requires restarting SSMS to pick it up. These fonts are all changed on Options  Environment | Fonts and Colors, and select the appropriate item for “Show settings for”:

A few optional settings to change are the highlighted text (Item Background on the Text Editor setting- Selected Text item). If the presentation screen is lower resolution, then you may want to set the query results to open in a new tab (Options | Query Results | SQL Server | Results to Grid), and to switch to this grid after the query executes. Otherwise, it may be too difficult to see both the query and the results.

To ensure that you know where you are at in the event of questions being asked, set the “Include the query in the result set” option. If your demo requires the code to be run in a specific order, this would be a pretty important setting to use. (Options | Query Results | SQL Server | Results to Grid).

It can be beneficial to turn on word wrap and line numbers also (Options | Text Editor | All Languages).

Starting in SSMS 17.x, there is a presenter mode that is available. In the Quick Launch toolbar, type “fonts”:

The PresentOn will adjust the fonts for presentations. PresentEdit will allow you to edit the XML file, and RestoreDefaultFonts will restore all of the fonts to default values. By default, the Presenters Mode XML file looks like:

You can edit this to add the other items (Grid / Text results, etc.):

However, keep in mind that the Grid Results option still requires restarting SSMS to pick it up. You just won’t be warned about it in this manner.

I just wish that there was an option for configuring my setup. This only allows switching between the default fonts and what is specified for presenter mode – not my normal font setup. I have a customized setup, and I’d like to be able to return to those easily. See yesterday’s post for how to export your settings. After presenting you can import this file to reset all of your settings.

Additionally, the query window has a zoom option. Just click on the down arrow and select your desired zoom percentage. If your mouse has a scroll wheel, you can push the wheel and then rotate the wheel to adjust the zoom.

Finally, if you are presenting, don’t forget to use (and to become proficient with) ZoomIt and/or the Windows Magnifier. It really helps the audience for you to be able to zoom in, and to outline parts of your code.

This post is for day twenty-nine of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.

Over the last several weeks, we have been making several changes to various settings in SSMS. Now that you have SSMS configured exactly to your liking, you need to save all of these configuration settings. You know, in case you have to start over with a fresh install. Thankfully, saving SSMS settings is very easy. Under the Tools menu is an option to import and export settings.

This brings up a dialog to either export or import settings, or to reset all of the settings:

Just follow the prompts for exporting, importing or resetting the settings.

This post is for day twenty-eight of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.

When you are performance tuning code, you will frequently examine the execution plans to see what is happening. As you change the code, you will look at the execution plan again. However, comparing all the changes is troublesome. Fortunately, comparing query plans has become easy in SSMS (starting in 2016). Just save off the first (original) plan (using the .sqlplan extension). When you have some changes that you want to compare, get the actual execution plan. Then right-click on it and select “Compare Showplan”. Finally, select the saved plan.

After you have selected the first plan, both plans will open up in a new tab. The Properties tab will have properties for both execution 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:

If there are multiple statements involved in the query, you can select which one you want to compare in the Multi Statement tab:

This post is for day twenty-seven of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.

We all know that we should liberally document our code with comments, so that others (and even yourself) will know what the code is doing down the road. I like to start all of my code off with my classic remark block:

Note that this is a SQL Prompt snippet, not a snippet from within SSMS. The SQL Prompt snippet allows the use of tokens to format / do certain things. For instance, the $DATE(yyyy-MM-dd)$ token fills in the current date, and the $SELECTIONSTART$ / $SELECTIONEND$ token will select that area of text once the snippet is applied. This makes it as easy (for me) as possible to write comments about the code.

Another use for comments is to selectively run certain portions of code.

There are two kinds of comments in TSQL – individual line comments (–) and a block comment (/*  */) which can span multiple lines. Generally, I prefer to use the block comment – if those comment characters are the only thing on a line, it makes it easier to select the text between them to run.

For the single-line comment, everything on the same line after the two dashes (–) is treated as a comment. For the multi-line comment, after encountering the leading /*, everything through the ending */ is treated as a comment. Interestingly, if the single-line comment immediately precedes the starting multi-line comment block marker, then that marker is treated as a comment and not as a multi-line comment block. Which allows for some interesting use of the various remark styles to control things even better.

Consider the following code:

When the beginning multi-line comment block marker is immediately preceded by a single-line comments, that block marker becomes a comment. However, the ending block marker is now not valid, so it needs to be remarked out. This allows the code to be run.

To remark out this block of code so that it can be easily switched between being commented out and being run, all you need to do is to remove the single-line comment at the beginning of the multi-line comment:

Add the single-line comment to the beginning, and you can run that code. Take it off, and the code is remarked out.

This post is for day twenty-six of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.

%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.