Evaluate and Implement Policies in Multiple SQL Server Instances

By:   |   Comments (3)   |   Related: > Policy Based Management


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?


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 OFFpolicy 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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, July 24, 2013 - 2:24:22 PM - Clayton Hoyt Back To Top (25985)

I am late this this but since this was publised, there is now a free set of tools available on CodePlex named Enterprise Policy Management that does all of this for you and wraps it up into handy SSRS reports.

Friday, May 9, 2008 - 4:17:24 AM - Jen Back To Top (963)

I found this blog article really useful [Y]

Friday, May 9, 2008 - 2:00:52 AM - re0mablue Back To Top (962)

This is actually one of the good additions/ features i've seen so far for SQL Server 2008. Using policies, DBA's will have more ways of securing and validating the contents of the database. This is also one good way of enforcing the rules among the database developers. Great job with the guidelines and easy to follow steps. Thanks!

get free sql tips
agree to terms