5 Steps to Evaluate SQL Server Policies on Multiple Instances through Central Management Server

The Article informs about the easy steps required to evaluate SQL Server Policies across multiple Instances using CMS.

While working with SQL Server, users/DBAs usually face problems with management of dozens of servers having different configuration. That situation arises when they have to check their databases on multiple SQL instances.

Evaluate SQL Server Policies on Multiple Instances

Since different organizations may use different configured Servers, it becomes a need to have a central policy management system. That’s why the Central Management Servers was introduced which could evaluate the policies against various SQL servers such as 2005, 2008, 2012, etc. 

In this article we will focus on easy steps to use Central management server feature to manage Multiple SQL server instances.

Policy Check across Multiple Instances 

Users first need to set up Central management Servers or Registered Servers and create a policy. Central management Server stores a list of names in hierarchal order in tables within that particular server and users can update or access the list through TSQL. It basically plays two roles, one is to manage and store all the logical servers and other is to evaluate policies. Using Central Management Servers, users can create what we call Server groups which help in easy administration and manual policy evaluation against each server groups.

So to check policies across various instances users can follow these simple steps.

Step 1

Users are required to open SQL SSMS i.e. Server Management Studio where they can find the option of ‘Registered Servers’ under view option in menu bar. Alternatively, users can also press Ctrl+Alt+G as keyboard shortcut.

Step 2

Registered Servers dialogue box appears where users can select Central management Servers and then SQLSRV1. It will display the option of Demo Servers enlisting all the available Servers like SQL2005, SQL2008, SQL2012, etc. There users can right click on the server group and select Evaluate Policies.

Step 3

In the Select Source Dialogue Box, users have to select the source where policy has been saved. It will require Login and Password of the account. Once the required fields are filled, one can press OK.

Step 4

In the Evaluate Policies dialogue box, select the policy which needs evaluation. There are two options, Policy Selection and Evaluation Results. The Policies along with categories will be mentioned in a table and users are required to check and uncheck the policies manually to determine the results. After selecting policies, users have to go to the option of Evaluation Results.

Step 5

In the results section, the selected policies will appear along with a table of target details. Simply click Evaluate and review the displayed results. In the target details, users can get all the information and evolution reports of the policies. There is also an option of exporting the results.

So this is how, users can evaluate a SQL Server Policy against multiple instances using Central Management Servers and Registered Servers. It’s an easy solution to the problem of evolution of policies on multiple servers as now users can easily monitor and reduce the unnecessary work of implementing the default traditional scripts on each instance.         

SQL Server administrators should always be prepared for situations where their recovery plans are put to test. Hence they should also keep a third party specialized SQL Server fix tool handy to deal with scenarios where normal recovery options do not give optimum results.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix mdb and sql recovery software products. For more information visit https://www.datanumen.com/