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.
Filed under: CodeProject, Database, SQL Server Tagged: Database, DB Productivity, DB Tips, SQL Server 
Submit an article or tip
