SQL Server Multi Database Query with Registered Servers

By: Svetlana Golovko   |   Updated: 2013-01-17   |   Comments (8)   |   Related: More > Central Management Servers

Problem

A user supports an application that has three databases for three different environments. These databases are located on two different SQL Servers and have different names. The user needs to validate application users on each of these databases. How can he/she run a single query in SQL Server Management Studio (SSMS) for all three databases?

Solution

In this tip we will show you how to run a query against multiple databases that are spread among different SQL Server instances. We will use the Registered Servers feature in SSMS. The same result could be achieved using a multiple servers query with Central Management Server (refer to this tip to find out how to use Central Management Server).

Pre-requisite

We will demonstrate the solution for this tip using two SQL Server instances and three databases:

• Production Environment:
  • SQL Server name - SQLDEMO1
  • Database Name - DemoApp_ProdDB
• Test Environment:
  • SQL Server name - SQLDEMO2
  • Database Name - DemoApp_TestDB
• QA Environment:
  • SQL Server name - SQLDEMO2
  • Database Name - DemoApp_QADB

Connect to each database and run this script to create a demo table:

CREATE TABLE dbo.ApplicationUser (Username VARCHAR(50), isActive TINYINT)
GO
INSERT INTO dbo.ApplicationUser (Username , isActive) VALUES ('John Smith', 1)
INSERT INTO dbo.ApplicationUser (Username , isActive) VALUES ('Peter Pan', 1)
INSERT INTO dbo.ApplicationUser (Username , isActive) VALUES ('Jack White', 0)
GO

On DemoApp_ProdDB database run this script to make some of the data a little different:

UPDATE [DemoApp_ProdDB].[dbo].[ApplicationUser]
   SET [isActive] = 0
 WHERE [Username] = 'John Smith'
GO

Register Servers

We will create the new Server Group "Demo App1" and register our two test SQL Servers under this group (refer to this tip to find out more about how to register multiple servers in SSMS).

Register the servers under the "Demo App1" Group as follows (note that we have the same "Server name" twice, but a different "Registered server name" each time, also we have different Default database for each connection):

Server Registration for the Production Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO1" as Server Name and "ProductionDB" as Registered server name: 

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database":

Click "Yes" to continue, select DemoApp_ProdDB database:

Click "Save".

Server Registration for the Test Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO2" as Server name and "TestDB" as Registered server name: 

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database", select DemoApp_TestDB database:

Click "Save".

Server Registration for the QA Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO2" as Server name and "QADB" as Registered server name:

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database", select DemoApp_TestDB database:

Click "Save".

Run Multi-database Query

Under Registered Servers right click on the "Demo App1" SQL Server Group that we have created and click "New Query":

Run the following query:

SELECT Username, isActive, @@SERVERNAME as "Real Server Name", DB_NAME() as "Database Name"
FROM dbo.ApplicationUser 
WHERE Username = 'John Smith'

Review the results:

Note, that the "Server Name" column contains the name that we entered for the Registered Server:

Note also that we have connected to the different databases and that two of these databases are on the same SQL Server Instance (SQLDEMO2).

Next Steps

• Use this tip to validate database users, to create the new users or make other database changes that have to be applied across different environments.
• Teach your application support users how to use registered servers for running the queries in multiple environments.
• Read more tips about Central Management Servers
• Read more tips about SQL Server Management Studio

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips