6.Run SQL Query with Multi Database Query with Registered 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 thistip 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).