In my previous article, I have covered the following topics:
- What are Registered Servers and their usage.
- How to create and configure a Local Server Group and Central Management Studio.
In this article, I am going to demonstrate how to execute a query on multiple servers using Registered Servers. Also, I am going to cover how we can evaluate the database policy on multiple database servers using Registered Servers.
Querying Registered Servers
As I mentioned in my previous article, with Registered Servers, we can execute a query on multiple servers of the same group. Let me demonstrate it.
I have already set up an environment. Below is the high-level overview:
- An SQL instance CMS Server has been designated as a central management server.
- Under the CMS server, two server groups have been created named “Development Servers” and “Production Servers”.
- Under the “Development server” groups, we have registered the database servers named “QA Testing Server” and “Staging Server”. Similarly, under “Production Server”, two servers have been registered named, “Sales DB” and “HR DB”.
See the image below for better understanding:
To execute a query on the “Development Server” group of the central management server, expand “CMS Server”. You will see two server groups that have been created, right-click the “Development server” group underneath of CMS Server and select “New Query”. As shown in the following image:
A query editor window will open.
Usually, we do not notice that the status bar of the regular query editor window is colored with yellow. But, the status bar of central management server query editor has a different color (pink) as shown in the following image:
Sometimes, Central Management Server is risky. For example, if you want to drop any database from Staging Database and if by mistake you run it in the query editor of the central management server, it will execute it on all servers which might turn out to be a BIG disaster.
You can change the color of taskbar of the Central Management Server query editor and choose some dark color to identify it easily. To change the color of status bar, use Options> Tools> Text Editor> Editor Tab and Status Bar> Status Bar Layout and Colors and select a different color from the “Group connection” drop-down list, as shown in the image below:
Now we want to collect a list of database objects of all databases that belong to the servers, registered in the “Development Servers” group. I have written an SQL script which will populate below information:
- Server Name
- Database Name
- Object Type
- Count of Objects
- Report Run Date
Below is the script:
Create Table #FinalSummery
id int identity (1,1),
declare @SQLCommand nvarchar(max)
declare @I int=0
declare @DBName varchar(350)
declare @DBCount int
Create Table #Databases (Name varchar(350))
insert into #Databases (name) select name from sys.databases where name <> 'Report-Server' and database_id>5
set @DBCount=(Select count(*) from #Databases)
Set @DBName=(Select Top 1 name from #Databases)
Insert Into #FinalSummery (DatabaseName,ObjectType,TotalObjects)
''' + @DBName +''',
when Type=''TR'' then ''SQL DML trigger''
when Type=''FN'' then ''SQL scalar function''
when Type=''D'' then ''DEFAULT (constraint or stand-alone)''
when Type=''PK'' then ''PRIMARY KEY constraint''
when Type=''P'' then ''SQL Stored Procedure''
when Type=''U'' then ''Table (user-defined)''
when Type=''V'' then ''View''
when Type=''X'' then ''Extended stored procedure''
End As ObjectType,
Count(Name)TotalObjects from ' + @DBName +'.sys.all_objects
group by type'
exec sp_executesql @SQLCommand
delete from #Databases where name =@DBName
select DatabaseName,ObjectType,TotalObjects,convert(date,getdate()),as ‘Report Run Date’ from #FinalSummery Where ObjectType is not null
Drop Table #FinalSummery
drop table #Databases
Now let’s execute the above query on the Central Management Server. The output will be shown in the following image:
If we execute the “Select” query on the CMS server group, the first column of the query output will be the name of Registered Server. The above query has been executed on “Development server” group. The first column will be the name of the servers, registered under “development server” group will be displayed.
Evaluating Database Policies
Using central management server, we can evaluate database policies on multiple servers.
What is Database Policy
According to MSDN definition, Policy-Based Management is a policy-based system for managing one or more instances of SQL Server. Use is to create conditions that contain condition expressions. Then, create policies that apply the conditions to database target objects. (Reference: https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/administer-servers-by-using-policy-based-management?view=sql-server-2017)
Evaluation of Database Policy
I have upgraded the SQL server edition from SQL 2008 to SQL 2014. I have created a database policy named “Check Compatibility” on SQL Instance which will ensure that compatibility level of the database has been changed across all servers. I want to evaluate this policy on the “Development server” group of “CMS server”.
Expand CMS Server, right-click “Development server” group and select “Evaluate Policies” as shown in the following window.
The “Evaluate Policy” dialog box will open. In the dialog box, click on Ellipses, next to the Source text box. The “Select Source” dialog box will open.
Here you can import the policy file which is in the XML format exported from another server OR you can retrieve policies from the SQL server instance. I have created this policy on the SQL server instance, hence choose server option and in the Server Name drop-down box, select the name of the database server where policy has been created and click OK.
It will retrieve the list of policies that has been created by system OR by the user. Select the “Check Compatibility” policy and click “Evaluate” as shown in the following image:
Registered Servers will evaluate policy across all servers and instances of databases and generate the result as shown in below image:
As you can see from the above image, the policy had iterated through all the servers registered in the group and evaluated policy successfully.
Export and Import Local server group configuration
We can export and import configuration of central management servers and local group. I will demonstrate that how we can export configuration of the Local server group.
Export Local server group configuration
To export local server group configuration, expand “Local server group”, right-click “Development Server”, select task, click “Export” as shown in the below image:
The “Export Registered Servers” dialog box will open. In the dialog box, you can see the list of local server groups. Click the ellipses next to the “Export File” text box. A dialog box will open. As I am going to export it to a different server, hence I will copy the file on a network location. Navigate to the network drive and click Save as shown in the below image:
Export is completed successfully. See the image below:
Importin Registered Server Configuration
To do that, right-click “Local server group”, select Tasks, click Import, as shown in the below image:
The “Import Registered Server” dialog box will open. In the dialog box, you can see the list of local server groups. Click ellipses next to the “Import File” text box. A dialog box will open. Go to the location where the configuration file is stored, select the file and click Open as shown in the below image:
Import is completed successfully. See the below image:
As a result of the successful import, the “Development server” Local Server Group has been created. See the below image:
Registered Server is one of the good options to manage multiple servers more efficiently. But as I mentioned, it should be used carefully.
In this article, I have covered:
- How to query the Registered Servers.
- How to evaluate database policies on multiple DB servers.
- How to import and export server group configurations.
Latest posts by Nisarg Upadhyay (see all)
- Registered Server, a Hidden Gem of SQL Server Management Studio. Part 2 - July 11, 2018
- Registered Server, a Hidden Gem of SQL Server Management Studio. Part 1 - July 3, 2018
- Performing Data Changes Audit Using Temporal Table - May 29, 2018