Evaluating Policies on Multiple SQL Server Instances using Central Management Server

By: Svetlana Golovko   |   Read Comments (2)   |   Related Tips: More > Central Management Servers

Problem

So, you have created a SQL Server Policy to check database recovery models. Now you need to check the databases on all of your SQL Server instances. In this tip we will show how you can evaluate a SQL Server policy against multiple instances.

Solution

To evaluate the policy against multiple SQL Servers you can use Central Management Servers or Registered Servers. In this tip we will evaluate a policy against SQL Server 2005, SQL Server 2008 and SQL Server 2012 using Central Management Servers.

Prerequisites

To learn more about Policy Based Management please review this tip: Using Policy Based Management in SQL Server 2008.

To learn more about Central Management Servers please review this tip: Execute SQL Server query on multiple servers at the same time.

Checking Policies Across Instances

After you have setup Central Management Servers and created the policy follow these steps:

Step 1

In SQL Server Management Studio (SSMS) click the "View" menu and then click "Registered Servers"

Step 2

Right click on the server or server group under Central Management Servers and select "Evaluate Policies..."

Step 3

Select the source where the policy is saved:

Step 4

Select the policy you want to evaluate:

Step 5

Click "Evaluate" and then review the results as shown below:

Next Steps

• You can also use Registered Servers in SSMS to evaluate a policy on multiple servers. Refer to this tip for more information.
• Create other policies that you want to evaluate on multiple servers
• Read more tips on Policy Based Management
• Read more tips on Central Management Servers

Last Update: 1/31/2013

About the author

DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Thursday, February 07, 2013 - 11:37:02 AM - Patrick Groce

Read The Tip

one useful option i find is to run a sql agent job calling powershell to evaluate a policy against multiple instances. Invoke-PolicyEvaluation -Policy "d:\policies\mypolicy.xml" -TargetServer "server1\instance1" Invoke-PolicyEvaluation -Policy "d:\policies\mypolicy.xml" -TargetServer "server2\instance1" Invoke-PolicyEvaluation -Policy "d:\policies\mypolicy2.xml" -TargetServer "server1\instance1" the output from the job can be reviewed to determine the results. the scheduled job hen runs each day.

Thursday, February 07, 2013 - 9:23:14 PM - Svetlana Golovko	Read The Tip
Thank you, Patrick, for your feedback! Yes, you can do this with PowerShell and SQL Server job. You can also dynamically pass Registered in Central Management Server SQL Server names to this PowerShell script: SELECT s.server_name    FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s

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

 

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." SQL Monitor: prioritize your SQL Server workload with easy-to-use performance monitoring What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA. Spring Clean Your Data - Clean your global contact data with Melissa Data tools for SSIS. Download a free trial! Optimizing SQL Server performance can be a daunting task. Or is it?