Wayne Sheffield

My blog about SQL Server

Browsing Posts published by Wayne Sheffield

One of the things that you will no doubt end up experiencing as a DBA is that you will see new SQL Server instances come into existence. Going right along with this is that you will see old instances die off. However, they don’t just disappear from the SSMS connection dialog. While you might have removed them from your registered servers (or CMS server), they tend to stick around in the SSMS connection dialog. This post will show you how to remove them from the connection dialog.

This screen shot shows my SSMS connection dialog. Note the highlighted entry for my SQL 2000 instance. While I still have that VM around, I don’t run it very much, so I want to remove it from the connection dialog.

SSMS connection dialog with server to remove

Removing Connections from the SSMS connection dialog

Starting in SSMS 2012, it has become easy to remove these connections. Just highlight it (as shown above), and press the delete key – it’s removed from the list.

SSMS connection dialog with server removed

If you have several entries with different logins (say, one with Windows Authentication, one with sa, others with various SQL logins), first select the one and verify the authentication method / login. Then go back into the drop down and delete it.

If you are on an older version of SSMS, the task becomes harder. My suggestion is to just get the latest SSMS – it is now distributed independently from SQL Server. It is a free download, and you can install it in parallel with other versions of SSMS.

If you can’t do that, then you need to either delete or modify the SqlStudio.bin file. If you delete the file, it also wipes out all of the custom settings that you have set up. Depending on the version of SSMS, it will be located at various places:

2005: C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

2008: C:\Documents and Settings\<USER>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

To modify the SSMS 2008 file, use the SSMSMRU utility.

This post is for day thirty-six 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 or at ssms.waynesheffield.com. Please visit this page for an easy place to quickly view all of the other tips in this series.

SSMS (and other SQL Server utilities) utilize a batch separator to, well, separate batches of T-SQL statements. Before we go jump into batch separators, let’s first talk about what a batch is.


Microsoft explains exactly what a batch is at this link:

A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution.

This link also has a few rules pertaining to batches:

  • CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
  • A table cannot be changed and then the new columns referenced in the same batch.
  • If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

Batch Separator

That first rule above is what causes us to need a batch separator. What this rule doesn’t state is that this applies to the ALTER format of these statements also.

Frequently, a script (especially an involved change script) will contain many T-SQL based objects which are created by running all of these as one file. However, they need to be in separate batches. So, these utilities implement a batch separator. The batch separator is NOT a T-SQL command; it is a SQL client command. You are probably used to seeing this implemented as “GO”. The complete documentation for this is at this link.

Since this is a SQL client command (and not a T-SQL command), the clients have some leeway with how to work with this. These utilities allow you to set what you want to use for the batch separator. In SSMS, this is performed in the options screen:

If you’re a fan of Captain Picard, you know that he would cause his orders to be executed with the command “Make It So”.

We can make SSMS behave this way also. Let’s change the batch separator to “MakeItSo”:

Now when we open up a new query window, we can use that to separate batches. In the following screen shot, I declare the same variable in two different batches. If these were in the same batch, this would generate an error. Also note that Intellisense is not warning about the unknown word.

When the statements in the query window are run, both print statements are executed without errors.


If you look at the syntax for the batch separator, there is an optional count argument to this command. This will cause the preceding batch to be executed the specified number of times. To see this in action, let’s add “MakeItSo 5” to the end of the last batch above:

We can see that the print statement was executed 5 times. Additionally, the messages show the start and stop of the loop.

Other clients

Other client utilities (sqlcmd, osql) allow you to change the batch separator with the “-c” option. It appears that this only works when using an input file. When saving the above script and executing it from sqlcmd, you can see:

This post is for day thirty-five 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 or at ssms.waynesheffield.com. Please visit this page for an easy place to quickly view all of the other tips in this series.

Database Code Smells

I was recently reviewing a newly created T-SQL stored procedure. This procedure was verifying temporary table existence with the following code:

Seeing this takes me back to one of my favorite presentations, where I compare Temporary Tables and Table Variables. In this, I go over several of the methods that I have seen for how code found on the internet actually does this task… and I show why they are all doing it wrong.

Let’s start by looking at a few other examples that I’ve seen on the internet to check for temporary table existence:

The code that I found is similar to the first two methods above – except that this newly created procedure is using the ancient (SQL 2000) sysobjects view instead of the more modern sys.objects view. While that doesn’t make this usage wrong by itself, it is enough to reject the code in a code review. Read on for why this just doesn’t work to verify temporary table existence.

So, what’s wrong with these methods?

What’s wrong with these methods is that they don’t consider the scope of a temporary table. Books Online states:

Local temporary tables are visible only in the current session

So, let’s test out the above statements with the scope of the temporary table in mind.

Let’s start by creating a local temporary table:

Now, in a different connection (session), run any of the above statements.

When you run the first two statements (and remember, these are like the one in the stored procedure), what you get is the result back from the select statement. This would indicate that the temporary table does exist.

When you run the third statement, it errors out.

Invalid object name ‘#TestTable’.

While the fourth statement doesn’t return a result set. This just returns:

Commands completed successfully.

Explain this please

Since the temporary table and the statements are in different connections, the temporary table isn’t in scope to the connection running the statements.

In the first two statements, the system views are queried to see if a row exists where the name starts with the name of the temporary table. This is because a local temporary table does not keep the name assigned… there is a suffix appended to it. Let’s look at just the stored name:

Which returns:

We can see that the name uses underscores to pad it out, followed by a sequential hexadecimal sequence. For local temporary tables, the name is always 128 characters long. My assumption is that this is so that the suffix is always in the same spot, making some kind of optimization possible. However, the point is that every temporary table has more to the name than what you specified. Temporary tables can be used inside stored procedures, which could be called from many connections at the same time. This means that SQL Server needs a way to differentiate all the different tables from each other, and to associate it with the proper connection / session that created it. The suffix handles this differentiation.

What the above two queries are actually doing is checking to see if there is any temporary table that starts with this name present. It’s not restricted to the current session. And it’s not really checking out the name properly either. Have you ever seen several temporary tables created along the line of #Temp1, #Temp2, etc.? The above code would find an entry for #TestTable1 also. To search for only #TestTable, it would have to include at least one underscore in the comparison. But that still doesn’t handle that the query can return tables that aren’t visible to the current connection.

What about the other statements?

Okay, let’s go over the last two statements. The third statement generated an error because it tried to run a select statement against the temporary table. This table is not in scope for this session.

The fourth statement generated no result for two reasons. The first is that the statement wasn’t scoped to the tempdb database. The current database did not have an object in it starting with the name of the temporary table, so the if statement’s true condition wasn’t run. However, even if tempdb is the current database, the statement still would not have generated a result… because the query is looking for the exact table name, not the name with something after it (the underscores and hex identifier).

So, what is the proper way to check for temporary table existence?

After showing these ways that don’t work, I need to leave you with the only method (that I know of) that does work. That method utilizes the OBJECT_ID function:

This method takes into account the scoped temporary table, and gets its object_id from tempdb. If it exists, you will get a non-null value. A null value means that it doesn’t exist. Even the remarks section of the OBJECT_ID function shows using this method.

Short, simple, but most importantly… it works correctly.


This post talks about having primary replica jobs. That is, jobs that will only run on the primary replica of an availability group.


An Availability Group (AG) takes one or more databases, and copies all changes to those databases from the primary replica to all the secondary replicas. However, anything that is not part of those databases are not copied over. Microsoft documents all of these in this article. This includes jobs that run against a database in an AG. Such jobs might include index or statistics maintenance, partition switching, etc. In short, any job that modifies the database needs to run against the primary replica only.

If the AG fails over to another replica, you want these processes to just keep on running. This means that you need these jobs installed on all the replicas, in the same state (enabled) and with the same schedule. However, trying to do these actions will cause the job to fail on the secondary replicas, since it can’t write to the database. So we need a way so that the job always runs, but it only does the work if the current SQL Server instance is the primary replica for this AG. Synchronizing jobs, and the ability of those jobs to run, is beyond the scope of this article.

Creating primary replica jobs

Obviously, what we need to do is to create the job so that it is aware of whether it is running on the instance that is the primary replica, and only proceed if it is. This last part “only proceed if it is” gives us a clue of what we need to do. We need to put the check and the work into separate job steps, and only run the work if the check is successful.

Do you see the next clue here? “if the check is successful”. We need to run a check to determine whether this is a primary replica. If successful, run the next part of the job. If not, stop. While there isn’t a way to tell a job whether or not to continue from within a job step, there are a few ways that we can stop the job completely if we need to.

Stopping the job, method 1

The first method for how to stop a job is to just raise an error. This method requires that the job step be configured to stop the job successfully on a failure. The text of the job step is simply:

And the job step’s properties will look like this:

Job Step configuration

When the job runs, if it’s not the primary replica for the specified AG, then the job step fails. Because the job step does not fail the job, the job is successful. If you view the job history, it will look like:

Job history

What I like about this method is the simplicity and that everything is in the one job. What I don’t like is seeing yellow and red when viewing the job history. When it does run on the primary replica, the job history will be green, so you can tell when it was the primary replica.

Stopping the job, method 2

A second way to stop the job is to execute the msdb.dbo.sp_stop_job stored procedure. This requires you to know either the job name or the job id. While we could hard code this, I like to make things dynamic when possible.

There is a way to know the job_id of the job dynamically. That is by using a SQL Agent token. Note that although the documentation says that JOBNAME and STEPNAME are valid, I (and others) cannot get those to work. However, the JOBID token does work. The job step will look like this:

If this step stops the job, then the job history reports being cancelled. If you view the job history, it will look like:

Stopping job with sp_stop_job

What I like about this method is the simplicity and that everything is in the one job. What I don’t like is seeing red when viewing the job history. When this does run on the primary replica, the red will (should) be green, so you can tell when it was the primary replica. This method is slightly more complex than the other methods, but not enough to really make a difference.

Start another job

Another way to handle this is to have this job start another job. In this case, the job with the test has the schedule attached to it, and if it is the primary replica, it starts another job. The second job needs to be disabled and not have a schedule. The code for this method would look like:

What I like about this method is the simplicity and that the job history shows all green. What I don’t like is having twice as many jobs and having to hard-code the job to be run. Additionally, you can’t tell by looking at the job history when the job ran on the primary replica.

Programming Notes

In all three of these methods, the code references the AG name (MyAGName). If you only have one AG on the instance, then this line is not necessary. However, since you can have more than one AG on an instance, and each AG can have a different primary replica, I’m showing how to handle this.

Additionally, there is another way to determine if the current server is the primary replica, but this is on a database-by-database method. Just use the system scalar function sys.fn_hadr_is_primary_replica and pass in the database name to check.


In this post, I have shown three different ways to create primary replica jobs that only run if it is on the primary replica. The job is installed and enabled on all the replicas, and the work will only be performed on the primary replica. All three of these methods are viable methods of accomplishing this. You can use whichever method that you prefer. The order that I like to use these methods is 1): RAISERROR (simplicity), 2): Start another job (don’t like seeing red on the job history), then 3): using sp_stop_job

There are several different options available for working with tabs and spaces in SSMS. In fact, there are enough that I could make several tips out of them. I decided to just keep all the tips together.

Tabs and Spaces options

The first thing that I want to go over are the various option settings that are configurable in SSMS.

Regardless of whether you like to use tabs or spaces, this is where you go to configure your settings. The first part of the screen controls the indenting options. If “None” is selected, then the next line will start at the beginning of the line. If you have selected “Block”, then it will align the next line with the previous line. And if you are using “Smart”, then the appropriate language will determine which indenting style to use.

The next section controls the tab size / indent size. This controls how many characters that a tab takes. It also controls whether tabs are converted to spaces or kept as tabs.

You can read more about these options at this link: Manage Code Formatting.

Viewing White Space

Now that you have the options set to what you like to use, how do you find out what a file is using? Just select the “View White Space” option from the Edit | Advanced menu.

This option will convert this text in SSMS:

to this:

Now you can see that tabs were used everywhere, except for the line with the first column. That line has a mixture of tabs and spaces. Whether you want to use all tabs or all spaces, you have something to change.

Changing tabs to spaces, or spaces to tabs.

In the Edit | Advanced menu option (see above screen shot), the first two items will swap spaces to tabs (Tabify Selected Lines) or tabs to spaces (Untabify Selected Lines). Selecting these lines and choosing the “Tabify Selected Lines” option makes the code look like this:

Great, all the spaces have been converted to tabs. That is, unless I want to use all spaces. Then just select “Untabify Selected Lines”:

This post is for day thirty-four 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 the other tips in this series.


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
Day Thirty-Three: Using the Full Screen Mode in SSMS
Day Thirty-Four: Working with tabs and spaces in SSMS
Day Thirty-Five: SSMS Batch Separator
Day Thirty-Six: Removing servers from the SSMS connection dialog

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.

Launch the full screen mode by using the keyboard shortcut Shift+Alt+Enter, or selecting that option from the View menu:

That will switch SSMS into full-screen mode:

Now you have a nice, minimalist window (and it has maximized to the full size of your monitor). All of the clutter and distractions are removed, leaving you to just focus on that fabulous query that you’re working on.

To switch back, use the keyboard shortcut again, or press the Full Screen button that has appeared on the toolbar (indicated above).

This post is for day thirty-three 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 or at ssms.waynesheffield.com. Please visit this page for an easy place to quickly view all of the other tips in this series.

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.


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.

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