Evaluate and Implement Policies in Multiple SQL Server Instances

By: Edwin Sarmiento   |   Read Comments (2)   |   Related Tips: More > Policy Based Management

Problem
In relation to the tip on Policy-Based Management in SQL Server 2008, we would like to implement policies on multiple SQL Server 2008 and even SQL Server 2005 instances.  Can you give us a detailed explanation of how we go about doing this?

Solution
Policies in SQL Server 2008 can be evaluated on the instance on which it was created. But one of the most tedious task that a database administrator needs to do is to go thru each of the SQL Server instances he or she manages and implement those polices. What database administrators do most of the time is to implement the changes on a test environment before rolling it out to production. However, we do not want to individually export and import; we want to apply it to all the servers at once. We can use our test server as a configuration server, then define the other servers as targets for this policy. Fortunately, we have the ability to export those policies as XML files for storing and even for multi-instance evaluation.

Exporting a Policy

Referring to the example provided in the tip on Policy-based Management, let's export the policy to an XML file. Right-click on the policy and select Export Policy. I have selected the AutoShrink OFF policy, in this example. Save the policy on your local drive for reference. I'll use AutoShrink OFF.xml for my filename.

Now that we have a copy of the policy as an XML file, we can now use this to evaluate other SQL Server instances we manage. Let's start by creating a server group to register the instances we manage.

Creating a Server Group

In SQL Server Management Studio, open the Registered Servers tab. You can create a new server group for ease of management or register your servers under the Local Server Groups. In our case, let's just create a new server group named Application Servers.

Right-click Local Server Groups and select New Server Group. Specify Application Servers in the Group Name field and click OK .

Next, register your SQL Server instances on Application Servers group. In my example, I'll register three SQL Server 2008 instances. Right-click on Application Servers group and select New Server Registration.

Once you have registered all the instances you need to manage, it's time to test the policy against them all at once.

Evaluating the policies against multiple instances

Since you already have your defined policy as an XML file, we can use that as a reference to evaluate the instances registered on your server group. Right-click on Application Servers group and select Evaluate Policies.

This will open up the Evaluate Policies - Application Servers window. On the Source field, click on the ellipses button and select the AutoShrink OFF.xml file which you generated.

Make sure that the AutoShrink OFF policy checkbox is checked. Then, click the Evaluate button. You will notice that the policy has been evaluated on not just one but all registered instances. From my registered servers, you will notice that one instance has at least one database which is out-of-compliance, in the case of the WIN2K3-SQL2K8\STAGING instance, the MetroDB database, as the red icon on the AutoShrink OFF policy indicates.

You can opt to implement the policy one at a time or you can do it on all instances at the same time. You can click the Configure button on the non-compliant database to apply the policy on one database at a time or click on the Configure button on the AutoShrink OFF policy to apply the policy on all databases on all registered instances. This makes it easy to configure multiple servers at the same time. By doing so, you'll see that all the databases on all my registered instances are now compliant

Next Steps

• Download a copy of the latest Community Technology Preview of SQL Server 2008 from this site.  The above examples were created using the February 2008 CTP.
• Create policies as per your environment and try them out on SQL Server 2008
• To view the SQL Server 2008 Books Online content on Policy-based Management go to this site.

About the author

Edwin works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code