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.
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 1,
BNE-SQL-PR-01\CONFIG has been registered as a central management server
with a production servers group containing the BNE-SQL-PR-01\Marketing
and BNE-SQL-PR-01\Sales 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 1 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.
2. Policy-based management with central management servers
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 1.
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 2),
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.
By
clicking Evaluate, we're evaluating the policy across all of the
servers in the configuration group. In our example, as shown in figure 3,
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.
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.
|
At
best, documenting server configuration produces an historical snapshot
of how a server looked at a given moment. At worst, it's a monumental
waste of time, particularly if the configuration changes in an
uncontrolled manner. In contrast, policy-based management provides a
much more efficient "living" document of server configuration. Better
still, its Apply function enables simple reconfiguration should the
server deviate from the desired state.
|
