Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Evaluate and Implement Policies in Multiple SQL Server Instances

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (3)   |   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.


Last Update: 5/7/2008


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Friday, May 09, 2008 - 2:00:52 AM - re0mablue Read The Tip

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!


Friday, May 09, 2008 - 4:17:24 AM - Jen Read The Tip

I found this blog article really useful [Y]


Wednesday, July 24, 2013 - 2:24:22 PM - Clayton Hoyt Read The Tip

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.



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
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.