Using SQL Server’s Registered Servers Functionality Effectively

Hello All,

In my last blog I explained how can we execute a Single Query on multiple servers using SQL Server’s Registered Servers functionality and how to execute a single query on multiple Databases using SSMS tool.

In this article we will learn how can we use SQL Server’s Registered Servers functionality effectively to improve productivity.

In order to deploy a software successfully in production and to prevent any error from being occurred directly on Live environment, we used to test an application in multiple environments before deploying it finally onto Production Servers. Considering full SDLC of an application, majorly 4 environments are built. These environments are Development, Testing, Staging & Production. However these may vary from project to project based on the complexity of the project.

Each of these mentioned environments may or may not have multiple SQL Servers configured. For our case, we will consider that each of these environments have multiple SQL Servers configured.

Now in day-to-day development life, it seems to be little difficult to remember all the SQL Servers name, if you are connecting these different SQL Servers often and connect them daily manually by providing the UserId & Password in case of SQL Server authentication or by Windows Authentication.

As I explained in previous blog that we can create multiple Server Groups under Local Server Groups. Each Server Group can represent an environment here and every group can have multiple SQL Servers registered with in them.

The good thing about Registered Servers is that the Servers registered once will always be there and available for future use. It is not required to remember each server name. During registration you can provide alias to each SQL Server which is convenient to remember else the SQL Server name will be displayed in Registered Servers list.

For Example, in the below image I have Registered 5 environments, Local, Development, Testing, Staging & Production. For these 5 environments I have created 5 different Groups.

In these groups I have registered a Primary and Secondary SQL Servers for each region and provided them alias to make it easy for me to remember.

Once you expand few nodes, it would look like below.

The beauty I found of Registered Servers is when It was not required to provide all the details again to connect with any of the server. Just open Registered Servers window and double-click on the Server which need to be connected. That’s all. SQL Server will open a new window automatically and connecting with the Master Database of that Server.

In case if you want to connect with a particular database always, that feature is available too. While Registering for the Server you can provide the default database which has to be connected every time. The same can be set in Connection Properties in New Server Registration window as shown below.

Along with Default Database there are few other properties available which can be set. For Example Network Protocol, Connection Timeout, Execution Timeout, Encrypt Connection etc.

It really takes a lot of time, if you have a big team of 20 people and each of the member has to Register so many servers. The solution to this issue is Export Registered Server details. Using Export functionality all the Registered Server details can be exported and shared with the whole team. Here I am assuming that complete team is using the same User Id and Password to connect with SQL Server.

Exporting can be done by Right clicking on Local Server Groups as shown below.

Exporting will save all the settings in a .regsrvr file. This file is a xml file and can be opened in a Notepad, in case if you are interested to see the content of the file.

Once the settings are exported, it can be distributed with the team and the team can import it from the Import option shown above.

I hope you like the article and am sure it would help you and your team in great extent.

In case you have any feedback or comment about the article, please feel free to comment using link Leave a comment link below.

Happy Learning till we meet again..

Execute SQL Query on Mutiple SQL Servers or Databases Simultaneously

This is very common scenario where we want to execute a SQL Query on multiple databases of single server or on multiple Database Servers. This is the basic requirement of one of my project where we execute SQL Queries in multiple databases repeatedly. Even every change in a single object has to be executed in all of the databases of all the servers including primary and secondary.

This is quite time-consuming and error prone when you decide to do the same manually. You may miss a server or a database where it was required to be executed, specially when you have a list of servers and a long list of databases.

After initial thought of creating some utility for the same where the utility will connect with provided SQL Server and execute the query on selected databases, I decided to search for the same on the net. As expected I got many options including Dynamic Query, SQL Server Stored Procedure etc. along with other couple of tools which does the same. In this blog I would be discussing about an inbuilt SQL Server functionality which execute a SQL Query on multiple SQL Servers simultaneously. The other tool which we learn here today is a third-party tool and executes a SQL Query on multiple databases simultaneously of a single SQL Server .

The SQL Server inbuilt functionality is called as “SQL Server Group” while the third-party tool is known as “SSMS Tools“.

Lets first discuss about SQL Server Groups.

As its name suggests SQL Server Group is a group of multiple SQL Servers. In case if a SQL Server has multiple instances installed on it. All the instances or any single instance can be added to a group. A group can be organized or created by environment say Dev/QA/Prod or Department say Finance/HR/Admin.

All the servers with in a group can be connected at the same time and a query can be fired against all. Once the SQL Server is connected to a group, it only shows those databases which are common in all the member servers of that group. For example, if we have added 2 SQL Server in a Group Say SQL Server 1 & SQL Server 2. SQL Server 1 has DB1, DB2 & DB3 while SQL Server 2 has DB2, DB3 & DB4. After connecting this group only DB2 & DB3 will be available against which the queries can be executed.

In order to View or Create SQL Server group SQL Server Management Studio’s “Registered Servers” functionality has to be used which is available under “View” menu or Press “Ctrl + Alt + G“. A Registered Servers window will open and will be displayed as below.

New SQL Server can be added by right clicking on “Local Server Groups” and Selecting “New Server Registration” as shown below.

New Server Registration window will open. Provide Server details along with credential details (SQL Server Authentication Details). Click on Test  button to verify if the provided information is correct or not. Once the connection is tested successfully. Click on Save button.

Add as many SQL Server as you want to a part of a single group. As mentioned earlier a Sub Group can also be created which may denote a logical grouping of Servers based on environments or departments as shown below.

To demonstrate I have added few databases in both the instances. “SQL Express” instance has Database1, Database2 & Database3 while “Local” instance has Database2, Database3 & Database4 as shown below.

Switch to “Registered Servers” window from “Object Explorer” window. Right click on “Development” Sub Group added earlier and Select “New Query“.

A new query window will be opened. Verify couple of things here. 1) In the Database Dropdown, only those databases will be available which are common to both the SQL Server instances. IN this case it will be Database2 & Database3 only as shown below.

2) In the Status bar down on the right side, instead of any Database name, Sub Group name will be displayed. In this case it will be “Development” as shown below. Login name will be the one which has been provided, sa in our case and the database selected would be master by default.

Select a database available in the Database dropdown and write queries in query window which need to be fired against both the servers in the selected database. I selected Database2 as shown below.

As we can notice here that the queries were executed against Database2 have been executed in both the servers registered in “Development” group. Insert query has inserted the records on both the servers while the Select query has returned the records from both the servers.

So the SQL Server Groups can be used to fire a query against a common database in multiple SQL Servers. The advantage here is if there are multiple servers included in the group , the query will be fired on all the servers at the same time and the records will be updated/selected from all the servers.

The SQL Server Group functionality provides the capability to execute a query against a common database on multiple servers but in case if a query has to be executed against multiple databases on a single server then some other functionality need to used. After browsing the net I found a quite useful 3rd party tool which does the same action along with many other useful functionalities. This tool is known as “SSMS Tools Pack” and readily available at http://www.ssmstoolspack.com/.

This tool is available for free till SQL Server 2008 R2 but SQL Server 2012 onwards it needs license. Once SSMS Tools Pack is downloaded and installed it will be integrated in SQL Server and a separate menu will be available with in SQL Server as “SSMS Tools” as shown below.

Open a new Query Window and write a query which has to be executed against multiple database of a server. Right click in the window and Select an option “Run On Multiple Targets” as shown below.

This will open a new window which will have all the database available on the current server listed as shown below.

Click on “Add” button. This will make Group Name field and Fixed Group Checkbox editable. Select few Databases which need to be part of the Group, Provide a Group Name and Check the Checkbox, if the Group has to be fixed. Fixed Group would keep the Selected database as it is even if any database is added manually later while executing a query. The added database will be a part of the Group only once and after that only original databases will be a part of the group.

Check “Script in New Window for Every Database” Checkbox, if you want to execute the query in a new window for every selected database. Click OK button.

Click on “Run Script” button. This will start executing the query for selected databases in a separate window. The benefit of executing in separate window is the query will be executing for multiple databases simultaneously else the execution will happen one by one only.

Here I would suggest that before executing any query on multiple databases, make sure that query works on any single database. Once you are sure that Query would be executing successfully, you are free to execute the query on all the Databases of your choice.

Along with Simultaneous Query execution, SSMS Tools provide many other useful features. I have given one liner about these features below.

Format SQL: Uppercase/Lowercase keywords and proper case Database Object Names. Set all keywords to uppercase or lowercase letters. Custom keywords can be added. Format all database objects to their proper case-sensitive name. You can also end every statement with a semicolon (;) since this is the new standard.

New Query Template: When opening a new query window you can specify a template that will be displayed.

SQL Snippets: SQL Snippets give you the ability to type shortcuts in editor which after pressing Enter or Tab turn into a predefined code block. You can have as many SQL snippets as you wish.

Search Results in Grid Mode: Find all occurrences of your search string in the results in datagrid mode. You can also search only in specific grid columns by preselecting a cell in each column you wish to search in.

These are few features which I found quite useful. SSMS provides many other features also. The complete list can be found at http://www.ssmstoolspack.com/. The downside I felt about SSMS is that it is available for free till SQL Server 2008 R2. SQL Server 2012 onwards, one need to purchase a license of the same.

I feel if we use both of these of the mentioned features, we can reduce the chances of making mistakes while running the same query on Multiple Servers or Multiple Databases. I would highly recommend to everyone to give a try to both of the functionality and start using it and I am sure you would also like the these.

That is all for today and I hope you enjoyed reading this blog and learned something new today.

I always appreciate the feedback, be it good or bad. One way it encourages me to write more while on the other side, it gives me an opportunity to improve my writing skills. Please take some time and send your feedback by leaving your comments in the following comments section.

Disclaimer: I am not associated with SSMS Tools Pack in any way and I am not paid for recommendation of the same. I found the tool useful and helped me in achieving what I wanted to. Hence I have recommend the same. Readers are free to use any other 3rd Party or SQL Server tool.