SQL Server 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 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

Svetlana Golovko

Svetlana has been working in IT for more than 17 years. Most of her career has focused on Database Administration (both SQL Server and Oracle) and Database Development. Databases are Svetlana’s passion, but she also has fun helping co-workers and friends in troubleshooting non-database related issues. Svetlana tries to explore and learn as many SQL Server features as possible. Her favorite SQL Server features are Policy Based Management, SSIS, SSRS and Master Data Services. One of Svetlana’s areas of expertize is cross systems / database integration.  Svetlana is currently a hands-on Database Team Lead in Calgary, Canada where she promotes SQL Server.

Svetlana likes to share her knowledge with others and enjoys learning herself. Her hobby is photography, but now she spends her free time away from Database Administration with her little girl who proudly wears her MSSQLTips shirt. Svetlana blogs at http://databaserefresh.com and posts her pictures to https://plus.google.com/u/0/111115767149899859037/posts.  Her Twitter account is @magasvs.

• MSSQLTips Awards: Rising Star (50+ tips) – 2018 | Author of the Year Contender – 2015-2017