Applying Policies to Multiple Servers
Published: Jul 21, 2014
The good thing about a Policy is that you don’t have to have a copy on every single server to be able to enforce it. This makes Policy Based Management very appealing to DBAs who have a central server which they can use to create and hold policies to then run against multiple other instances on the network in order to bring them into line without having to create numerous different policies on numerous different sets of servers.

Imagine we have the following setup…

Instance0 - DBA Data Collection & Monitoring Server

Instance1 and Instance2 - Reporting Servers

Instance3 and Instance4 - OLTP Servers

Based on this we may have a Policy enforcing the SIMPLE recovery model on the Reporting Servers but wish to have the FULL recovery in place on all databases in the OLTP servers.

The initial thought would be to create the SIMPLE policy on both Instance1 & Instance2, and the FULL policy on Instance3 & Instance4. But this is a hassle and what happens if you then add another instance to either group? More work.

Instead, SQL Server provides us with a nice way to have just one copy of a Policy and be able to evaluate and apply it across any servers we see fit. We can do this using Registered Servers.

Let’s do that for our example above:

In SSMS, select “View” from the top menu and then “Registered Servers” and the following window should open…

Expand “Database Engine”, right click “Local Server Groups” and choose “New Server Group…”

In the resulting window give your group a meaningful name and press OK.

Do the same for the second group of servers…

Now right click a group and select “New Server Registration…” and fill in your server details...

Press OK and this will now appear in your Registered Server Group…

Do this for the remainder of your servers:

Now, to apply a policy to a group, simply right click the group and select “Evaluate Policies…”. Select your source (the server on which the Policies reside) and make a connection…

You will now be given a list of Policies held on that server.

Simply select the relevant one for your server group and click “Evaluate” and it will be run against every server in the server group.

This way it provides a VERY good and efficient means of keeping all servers aligned (such as basic server group based config etc) without having to have numerous copies of policies on all servers.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron