Archive for the ‘Central Management Server’ Category

Using Policy-Based Management to Update PAGE_VERIFY Settings – Part Two

August 2, 2013 2 comments

In my last post, I showed how to define a policy using Policy-Based Management (PBM). In this post, I will show how to evaluate this policy. While a policy can be evaluated against a single instance of SQL Server, PBM is especially powerful when used with Central Management Server. For more information about setting up Central Management Server, see my previous post on the topic.

5 - CMS

To begin evaluating your policy, open the Registered Servers tab in Management Studio and expand the Central Management Servers node. Right-click on the server group you want to evaluate and select New Policy. In the example, I will be using the 2005-2008 group.

6 - Evaluate Policies

In the Evaluate Policies dialog, click the lookup button next to the Source box.

7 - Select Source

Click the radio button next to Server and enter the name of the instance where you defined your policy. Click OK.

8 - Evaluate Policies 2

You should see your policy in the Policies list. To evaluate it, check the checkbox next to the policy and click Evaluate. Central Management Server will connect to each of the instances defined in the selected server group and will evaluate the policy, returning the results in the Target Details pane.

9 - Evaluate Policies Results

In my example, a number of databasesfailed the test. As a result, a red X is displayed next to the policy name in the Results pane. The Target Details pane will show the status for each database. I’ve obscured the results, to protect the innocent (and my job), but the Server column contains the name of the instance connected to and the Target column contains the following: SQLSERVER:\SQL\<Machine Name>\<Instance Name>\Database\<Database Name>.

A - Evaluate Plices Result Detail

Clicking the View link next to a failed database will show you the reason for the failure. In this case, the PAGE_VERIFY setting for the database is set to NONE. That, in technical terms, is real bad! Fortunately, Policy-Based Management gives us an easy method of fixing all databases in violation of the policy.

B - Apply Policy Changes

To apply the policy against all databases in violation, check the checkbox next to the policy name in the Results pane. This will select all of the databases in violation. If you want to apply the policy to a subset of the violators, you can select those manually in the Target Details pane. Once you’ve selected the databases you want to apply the policy to, click the Apply button.

C - Policy Apply Warning

Management Studio warns you that you are going to make changes to the databases in violation of the policy. If you are sure that this is what you want to do, click Yes.

D - Policy Applied

Central Management Server will connect to the instances containing databases in violation of the policy and change the PAGE_VERIFY setting to CHECKSUM. When it completes, you should see a green check mark next to the policy name in the Results pane, as well as green check marks next to all databases in the detail pane. If so, congratulations, you’ve successfully evaluated and applied your policy and PAGE_VERIFY is set to CHECKSUM for all databases.

These posts have demonstrated how to define and evaluate a Policy-Based Management policy with a single condition. There are many other facets and conditions that can be evaluated. You can get more information on Policy-Based Management at Books Online. And if you are really interested in learning more, I recommend Pro SQL Server 2008 Policy-Based Management.

Policy-Based Management provides a powerful tool for evaluating and standardizing your environment. I hope this introduction is helpful to you.

Using Central Management Server

April 6, 2012 1 comment

Central Management Server (CMS) allows a query to be executed against multiple instances of SQL Server at one time. It is a very useful tool for returning information about your environment and creating the same object on several instances. I will use one of the queries from my previous post in this example.

To set up CMS, open SQL Server Management Studio 2008 or later and click View and select Registered Servers. On the Registered Servers pane, right-click Central Management Servers and select Register Central Management Servers.

Register a Central Management Server

Enter a SQL Server name into the Server name field. You can test the connection using the Test button. Once you are satisfied, click Save. Your server will appear in the tree on the left.

Create the Central Management Server

Right-click the server name and select New Server Registration.

Register a New Server

In the New Server Registration dialog, enter a server you want to add. After testing the connection, click Save. The registered server will appear under the Central Management Server. Repeat this for all of the servers you want to add. Also, note that you can create multiple levels in the CMS tree structure. In my environment, we have the clustered servers grouped together by both data center and environment. This allows queries to be executed against the entire environment as well as any subset of the instances.

Enter Registered Server

To execute a query against multiple databases, right-click on the level in the tree you want to run against and select New Query.

Open New Query

A query window will open. You will notice that the status bar is pink and the lower left-hand corner will show Connected (n/n), displaying the number of instances you are connected to.

Query Status Bar

For this example, I am going to use the xp_fixeddrives query from my last post, but you can use CMS to execute any T-SQL query.


Once you click Execute, there is no going back. If you execute


against all of the instances in your environment, you could potentially drop every copy of master. Additionally, there is no way to control the order in which the servers return their results, so canceling a query may have unexpected results.

Once you’ve absorbed that, click execute. Your query results will return with one result set per instance. The first column in each row will always contain the instance the returning that row.

CMS Query Results

There are a few things I recommend when running CMS queries.

First, always declare a USE clause. This will prevent problems when running various system stored procedures against 2005 and 2008 systems at the same time.

Second, when running a SELECT statement, always use a column list. This will prevent problems if the number of columns returned has changed between versions of SQL Server.

Third, keep in mind CMS connects to each instance and executes your query, returning each result set individually. This means you have no control over the order the result sets return and cannot order the entire result set. If you code an ORDER BY in your query, each result set will be sorted. If I need to sort the entire result set, I will copy it into Excel and do the sorting there.

I use CMS on a daily basis to return information from my environment. It also makes creating and modifying code used in MSX/TSX jobs very simple. With a single execution I can push a new stored procedure out to every instance in my environment. Have fun playing with CMS to see what uses you can find for it.