Learn more about SQL Server tools

   
   















































SQL Server Multi Database Query with Registered Servers

MSSQLTips author Svetlana Golovko By:   |   Read Comments (5)   |   Related Tips: 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: 

Register Production DB connection

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

Browse for DB

Click "Yes" to continue, select DemoApp_ProdDB database:

Select DB

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: 

Register Test DB connection

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

Select DB

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:

Register QA DB connection

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

Select DB

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":

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:

Query Result

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

Registered Server Name

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


Last Update: 1/17/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, January 17, 2013 - 5:57:21 PM - Svetlana Golovko Read The Tip

eric81

Yes, you can do this using PowerShell script. You can pass server names, groups from this query:

 SELECT s.server_name, ssg.name as GroupName
   FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s
   INNER JOIN msdb.dbo.sysmanagement_shared_server_groups ssg
   ON s.server_group_id = ssg.server_group_id


Thursday, January 17, 2013 - 2:54:41 PM - eric81 Read The Tip

 

I have over 100 servers registered in my CMS.  From time to time we have to validate all of those servers are operational.  Is their automatically run the same query I run in CMS say through a job or some other method?


Thursday, January 17, 2013 - 11:40:20 AM - Reinis Read The Tip

Does anybody know a way to execute the same multi-server query through T-SQL only.  As in, something to the equivalent of "USE <dbname>".  Or a way to open a query automatically that is connected to the registered group?

I'm pretty sure there isn't a way and the only way you can do it is to go through the registered server list, but just wondering.

Reinis


Thursday, January 17, 2013 - 10:25:45 AM - alfredo Read The Tip

hello,  


Everyday I validate loads of tables that run from Oracle to SQL Server 2000 Server and SQL Server 2008 R2. The query is:

 

SELECT TOP 2 FEC_ACT, 

@@SERVERNAME as 'Nombre Real Server', 

DB_NAME() as 'Nombre Database'

FROM dbo.ZLMT_VALCUOSIL 

ORDER BY FEC_ACT DESC;


and validation results are as follows, according to tips, SQL Server Multi Database Query with Registered Servers

 

 

Server NameFEC_ACTNombre Real ServerNombre Database

LH-CONTOPER-BD20130116LH-FNCONTI-BDindices

LH-CONTOPER-BD20130116LH-FNCONTI-BDindices

LH-REPORTES-BD20130116DESBTDB01SISILHIA

LH-REPORTES-BD20130116DESBTDB01SISILHIA

MIGRACIONSAP20130116MIGRACIONSAP  MigraSAP

MIGRACIONSAP20130116MIGRACIONSAPMigraSAP

 


The tips helped me a lot, thanks. So, I have evidence that the tables are loaded with the previous day's date as the loads are at 2:00 to 4:00 AM.


thank you very much.


Alfredo

DBA


Thursday, January 17, 2013 - 9:16:20 AM - Seth Delconte Read The Tip

Nice!




 
Sponsor Information