8.4. Enterprise policy management
In opening this chapter, we discussed the challenges faced by a DBA in maintaining consistent best practice configuration across many SQL Server instances. We've looked at the new policy-based management feature and how it can be used to create, import, and evaluate policies.
Although the new feature is unquestionably useful, there still remains a significant challenge in being able to deal with instances running into the hundreds or thousands, particularly when the instances are spread across a number of categories such as production, test, and development, each of which may have individual configuration requirements.
What's really needed is a way of defining a template server for each environment, and then applying the configuration of that server to all other servers in that category, for example, make all those production servers just like this one. Using policy-based management in conjunction with central management servers allows us to do exactly that.
Before we look at combining these two features, let's start with a look at central management servers.
8.4.1. Central management servers
In SQL Server 2005 we could use the registered servers feature to register and group frequently managed servers. For example, we could create a production group, containing multiple production servers, and a test group containing multiple test servers. By exporting the registration information to file, DBAs could import it and quickly see the same groups and servers in their copy of SQL Server Management Studio.
In SQL Server 2008, this facility still exists, and it's referred to as local server groups. In addition to this feature, we now have central management servers. Unlike local server groups, central management servers store the group and server registration details within the server itself, thereby avoiding the import/export process. This way, DBAs can simply register an existing central management server and automatically see its groups and registered servers.
Unlike local server groups, central management servers only support the registration of servers using Windows authentication mode. As such, even though multiple DBAs can register the central management server, they'll only have access to the registered servers if permitted via Windows authentication.
Along with the ability to share a central repository of registration information, configuration servers enable two additional features: the ability to run a query against multiple servers simultaneously, and the ability to evaluate a policy against multiple servers.
In the example in figure 8.14, BNE-SQL-PR-01CONFIG has been registered as a central management server with a production servers group containing the BNE-SQL-PR-01Marketing and BNE-SQL-PR-01Sales instances. By clicking Production Servers and then New Query, we can execute a query against all servers in the group. The result set includes an extra column to enable us to distinguish results from the different servers.
One of the things to note about figure 8.14 is the red/pink status bar at the bottom of the screen denoting that this is a multiserver query. Production Servers is included in the status bar to help us easily determine which group of servers the query was executed against. In fact, when registering a server (including through the central management servers feature) we can use the Connection Properties page to associate a custom color with the registration. That way, all subsequent queries against the registered server will display this color in the status bar. Such a feature comes in handy for those who accidentally run queries in the wrong environment!
From a policy perspective, central management servers provide a huge benefit, as we'll see next.
Figure 8.14. You can run multiserver queries against central management server groups. The result set includes an extra column for the server name.
8.4.2. Policy-based management with central management servers
Earlier in the chapter we covered the process of importing and evaluating a policy against a single server. Using central management servers, we're able to do that against all of the registered servers in one action. Further, each server that fails policy validation can then be configured at the click of a button.
To demonstrate, let's use the central management server from figure 8.14. Under the Production Servers group, we have two registered servers. In a real-world enterprise environment, we'd obviously have more servers and more groups. Let's right-click on the Production Servers group and select Evaluate Policies.
In the window that opens (shown in figure 8.15), we can select the location of a policy file. This can be either one of the predefined policies or one that we've exported from an existing instance. Let's select the predefined Surface Area Configuration policy.
Figure 8.15. After rightclicking a central management server group and choosing Evaluate Policies, we can choose the policy source and click Evaluate.
By clicking Evaluate, we're evaluating the policy across all of the servers in the configuration group. In our example, as shown in figure 8.16, one of the servers passed validation and one failed. The great thing about evaluating policies in this manner is that you can reconfigure servers that fail validation by simply selecting the check box next to that server name and clicking Apply.
Figure 8.16. Evaluating a policy against a central management server group lets you evaluate and reconfigure groups of servers in a single step.
In the introduction to this chapter we spoke about the problem of coming into a poorly managed environment and being presented with the task of assessing a large number of servers for best practice. By grouping the servers with central management servers, you can validate policies across groups of servers, and you can easily reconfigure those that fail validation by clicking the Apply button.
Once they're configured, you can import policies into each of the servers with a scheduled evaluation mode to ensure their ongoing compliance. To make this even easier, import the policies against a central management server group, which will perform the import against each of the servers in the group.
Let's wrap up this chapter with a brief look at some advanced policy-based management techniques.