Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.