How to Connect to Several SQL Servers in One Click( Registered Servers Group) - SQL Server / TSQL Tutorial Part 5


Think about a situation where you need to get all Database Names for Development servers or Check if Login exists on all UAT Servers. In these situations you have to connect to each SQL Server Instance individually and run the query.You are curious if there is a way when you can run same query on multiple Servers with one click?


We can create Registered Servers Groups and then add the SQL Server Instances to it. Let's take a look how we can create Registered Groups.

Step 1: 
Open SSMS and then go to View and choose Registered Servers.
How to create Registered Servers Group

Registered Servers windows will open as shown below.
How to add SQL Server Instance to Registered Servers Group in SQL Server

Step 2: Add New Group to Registered Servers Group
Right click on Local Server Group and then go to New Server Group. Let's say we want to add our all Development Servers to this group.

How to create Server Groups in SQL Server - SQL Server Tutorial

Provide the Group Name, Description and hit Ok.

Step 3: Add SQL Server Instance to Server Group
As our Development-Server Group is created, Now we can add SQL Server Instance to it.

How to add SQL Server Instance to Registered Server Group in SQL Server

Now we have to provide SQL Server Instance information that we are going to add to Registered Group ( Development-Servers)

Provide the SQL Server Instance Name, Test and Finally hit Save button.

How to add SQL Server Instance to Registered Servers Group in SQL Server.

Now repeat the step# 3 to add more SQL Server Instance to the Registered Server Group. 

Step 4: How to Run Query on Registered Servers Group
Click on the Registered Servers Group Name and then hit New Query. Any query you will run in newly open window, will run on all the SQL Server Instances which are included in Registered Servers Group.

How to run SQL Server Query on Multiple SQL Server Instances by using Registered Servers.

No comments:

Post a Comment