In this series of articles (see below for links to other articles) about the SQL Server Policy Based Management feature, we have explained concepts, terms, basic, and advanced tasks that create complex conditions and policies.

The SQL Server Policy Based Management feature provides an efficient way to declare certain rules and corresponding policies (e.g. to force Windows authentication mode on a SQL Server instance, to evaluate and prevent violations in object naming conventions, etc.). So far, we focused on a single SQL Server instance, the methods to setup and evaluate Policy Based Management policies on it.

In this article, we are going to explain how the Central Management Servers feature, introduced in the Standard and Enterprise Editions of SQL Server 2008, helps DBAs to maintain multiple SQL Server instances across an enterprise environment. The feature provides two major benefits when multiple SQL Server instances need to be affected by a specific action:

• An execution of a specific T-SQL query against SQL Server instances registered within Central Management Servers
• An evaluation of a set of policies on SQL Server instances registered within Central Management Servers. This is the benefit we are going to describe in further text

Creating a central management server

It is advisable to have a dedicated SQL Server instance which will be used as the central management server. Note that central management servers support the registration of servers using the Windows authentication mode only. So, even DBAs can access the central management server, the access to its registered servers depends on the Windows authentication.

There are two database roles in the msdb database that grant access to central management servers. The ServerGroupAdministratorRole role membership is required to manage the central management server and the ServerGroupReaderRole role membership to connect to the central management server.

To create a central management server:

1. Start SQL Server Management Studio and open the Registered Servers pane using the View menu
2. Expand the Database Engine node and click the Register Central Management Server option from the Central Management Servers sub-node context menu

3. This will open the New Server Registration dialog. Use the Server name dropdown box to browse for SQL Server instance that will be used as the central management server (e.g. LENOVO\CMS). Type-in the name of the server the way you want it to be shown in the Registered Servers pane (e.g. CENTRAL SERVER).

   

4. Use the Test option to verify the connection and authentication on the central management server

5. Click Save to register the newly registered central management server within Central Management Servers.

The next step is to create a server group that will identify SQL Server instances manageable by the central management server. It is recommended to create multiple server groups which identify SQL Server instances (e.g. by their business role, such as production or development groups). One SQL Server instance can exist in multiple server groups if needed.

To create a new server group under the CENTRAL SERVER and add SQL Server instances to the group:

6. Select the New Server Group option from the CENTRAL SERVER context menu, type in the group name (e.g. Production Servers) and optional description
7. Select the New Server Registration option from the Production Servers context menu. This will open the same dialog type we used for creating the central management server. In our example, we’ll register two SQL Server instances (e.g. REMOTE\PRIMARY and ACMECOMPANY\SECONDARY)

After the process of configuring the central management server, its group(s), and servers is finished, the Registered Servers pane will show the structure in a tree-like form:

The central management server stores its groups and servers registration details within the server itself. This way, you can simply register the existing central management server on other remote machine and instantly have access to its groups and registered servers.

Evaluating Policy Based Management policies

Policies can exist on particular SQL Server instances or in a form of XML files (the format used for exporting once declared policies to a file system). Our goal is to evaluate Policy Based Management policies on multiple SQL Server instances, in our case on the instances managed by the CENTRAL SERVER management server. Note that the evaluation does not have to involve all instances, but only the ones in particular server group, or even single SQL Server instances. The central management server, server groups, and single instances provide the Evaluate Policies context menu option.

To evaluate policies on a server group:

1. Select the Evaluate policies option from the Production Servers context menu

2. The Evaluate Policies dialog will open. Use the Source option to select the source of Policy Based Management policies. Use the Files option to browse for exported to XML policies, or the Server option to select a SQL Server instance which hosts declared policies we want to evaluate on instances in the Production Servers group.

   

3. Click OK to confirm the policies source selection

4. The Policies grid in the Evaluation Policies dialog will populate with policies from the source. Select the ones you want to evaluate against the servers group. In our example, we’ll select the PolicyServerAuthenticationMode policy (it checks whether the Windows Authentication mode is forced on a SQL Server instance).

   

   Note that the Evaluation Policies dialog provides information the same way it does when used locally on SQL Server instances (e.g. the yellow exclamation icon in the screenshot warns that the PolicyCustomersCount contains scripts, and that it should be evaluated only if originates from a trustworthy source).

5. Click the Evaluate button to start the process of verifying whether the servers in the Production servers group comply with the selected policy

6. The Results tab in the Evaluation Policies dialog will show which SQL Server instances on which policy evaluation failed.

   

Certain policy types, depending on the conditions used within, offer immediate reconfiguration of SQL Server instances (or other policy defined targets). To do that, check the box next to the server name and click Apply. In our case the LENOVO SQL Server instance will switch from the mixed (both Windows Authentication and SQL Server Authentication) mode to the Windows Authentication mode.

In the described example, we have used the LENOVO\ONE SQL Server instance as the source for the policies we evaluated on the servers group. In order to improve centralized management of registered servers, you can use the central management server or the server groups as the policy source instead. Once the needed policies are tested (whatever their source is), the policies can be imported to the central management server or the particular server group which will perform the policies import to appropriate servers.