Implementing Policy-Based Management in Microsoft SQL Server

Configuring Central Management Servers

By default, each instance of SQL Server is responsible for monitoring and enforcing its own policies. Although this configuration is useful in stand-alone deployments, you often want a more robust solution in the enterprise, and this is where central management servers are useful. Central management servers take over the responsibility of monitoring and enforcing policies from any instance of SQL Server registered as a subordinate server. From a central management server, you also can execute T-SQL statements on multiple instances of SQL Server simultaneously.

You can specify a SQL Server instance that you want to use as a central management server by registering the server in the Registered Servers view. Afterward, you can specify and register the subordinate servers that you will manage via the central management server. Although you must register subordinate servers individually, you can manage subordinate servers collectively by using subordinate server groups.

Registering Central Management Servers

A central management server cannot be a subordinate server or a member of a subordinate group that it maintains. You can register a central management server by following these steps:

1. In SQL Server Management Studio, use the Registered Servers view to work with central management servers. To use this view, or to display it if it is hidden, press Ctrl+Alt+G.

2. Under the Central Management Servers node, you’ll see a list of previously registered central management servers. To register a new server, right-click the Central Management Servers node, and then select Register Central Management Server. This displays the New Server Registration dialog box, shown in Figure 3-1.

   

   Figure 3-1 The New Server Registration dialog box.

3. In the Server Name box, type the fully qualified domain name (FQDN) or host name of the central management server, such as dbsvr23.cpandl.com or DBSvr23.

4. Choose Windows Authentication as the authentication type.

5. The registered server name is filled in for you on the basis of the server name you entered previously. Change the default name only if you want SQL Server Management Studio to use an alternate display name for the server.

6. To test the settings, click Test. If you successfully connect to the server, you will see a prompt confirming this. If the test fails, verify the information you provided, make changes as necessary, and then test the settings again.

7. Click Save.

Registering Subordinate Servers and Groups

After you register a central management server, you can register subordinate servers and create subordinate server groups. While individual subordinate servers don’t have to be organized into groups, you can group servers according to their business unit, geographic location, or purpose for easier management.

You create a subordinate server group by completing the following steps:

1. In the Registered Servers view, expand the Central Management Servers node. You’ll see a list of previously registered central management servers.

2. Right-click the central management server that will have management responsibility for the subordinate server group, and then select New Server Group.

3. In the New Server Group Properties dialog box, type a name and description for the new group in the boxes provided. Click OK.

You register a subordinate server by following these steps:

1. In the Registered Servers view, expand the Central Management Servers node, and then expand the node for the server that will have management responsibility for the subordinate server.

2. Right-click the server or one of its subordinate groups, and then select New Server Registration.

3. In the Server Name box, type the FQDN or host name of the subordinate server, such as DatabaseServer12.cpandl.com or DatabaseServer12.

4. Choose Windows Authentication as the authentication type.

5. The registered server name is filled in for you on the basis of the previously entered server name. Change the default name only if you want SQL Server Management Studio to use an alternative display name for the server.

6. To test the settings, click Test. If you successfully connect to the server, you will see a prompt confirming this. If the test fails, verify the information you provided, make changes as necessary, and then test the settings again.

7. Click Save.

Moving Subordinate Servers and Server Groups

Sometimes, you need to move a subordinate server or server group to a new location in the central management server hierarchy. You can do this by completing the following steps:

1. In the Registered Servers view, expand the Central Management Servers node and the related server and group nodes as necessary.

2. Right-click the subordinate server or server group that you want to move, point to Tasks, and then select Move To.

3. In the Move Server Registration dialog box, select the node into which you want to place the server or group.

4. Click OK.

The Move To process does not let you move a subordinate server or server group to a different central management server. To move a subordinate server or server group to a different central management server, you need to export the related registration settings and then import them to the new location. The export and import process works like this:

1. In the Registered Servers view, right-click the node with the settings to export, point to Tasks, and then select Export.

2. Click the options (...) button to the right of the Export File box.

3. Use the Save As dialog box to select a save location, type a name for the exported registered servers file, and then click Save.

4. Click OK to close the Export Registered Servers dialog box. If the file exists and you want to replace it with the current settings, click Yes when prompted.

5. Right-click the node where you want to import the settings, point to Tasks, and then select Import.

6. Click the options (...) button to the right of the Import File box.

7. Use the Open dialog box to navigate to the save location, select the exported registered servers file, and then click Open.

8. Click OK to close the Import Registered Servers dialog box. If identical subordinate groups and servers are already registered, you are prompted to replace the existing settings. Click Yes or Yes To All only if you want to overwrite existing settings.

Deleting Subordinate Servers and Server Groups

If you no longer use a server as a subordinate server or no longer want to use a server group, you can remove the entry for the server or server group. Right-click the server or server group, and then select Delete. When prompted to confirm, click Yes. When you delete a server group, SQL Server Management Studio removes the group and all the subordinate server registrations it contains.

Executing Statements Against Multiple Servers

You can query multiple servers at the same time by using central management servers. You can also execute T-SQL statements against local server groups in the Registered Servers view. Keep the following in mind:

• To query all subordinate servers for a central management server, right-click the central management server in the Registered Servers view and select New Query. In the Query Editor, type and execute your T-SQL statements.

• To query every server in a server group, right-click the server group in the Registered Servers view and select New Query. In the Query Editor, type and execute your T-SQL statements.

By default, the results pane combines the query results from all the servers. Because the connections to subordinate servers are executed using Windows authentication in the context of the currently logged-in user, the effective permissions might vary. If a connection cannot be established to one or more servers, those servers are ignored, and results for the other servers are displayed.

The combined results contain the server name but do not contain any login names. If you want, you can modify multiserver results by using the Options dialog box. Click Options on the Tools menu, expand Query Results and SQL Server, and then click Multiserver Results. On the Multiserver Results page, do one or more of the following, and then click OK:

• Configure the Add Login Names To The Results option. Use True to add login names to the results. Use False to remove login names from the results.

• Configure the Add Server Names To The Results option. Use True to add server names to the results. Use False to remove server names from the results.

• Configure the Merge Results option. Use True to merge results in a single results pane. Use False to display results in a separate pane for each server.