Applying Policies to Multiple Servers
Produced: 02/02/2014 19:28:00
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.

FULL Recovery Model - Policy Management
Produced: 26/01/2014 19:17:00
In most SQL Server environments with fast moving data we want to ensure that we will suffer the least amount of data loss in an outage or disaster. This means using the FULL recovery model. There are some circumstances in which someone might wish to change the database to a SIMPLE mode in order to, for example, shrink a runaway transaction log.

We want to ensure that any time a database changes state away from the FULL recovery mode we have a policy in place which will log and highlight this so that we can correct it and fix the backup chain.

Email Alert from On Change Log Only
Produced: 19/01/2014 19:07:00
Having Policy Management set up is great… being able to schedule a job to start up, check your systems, and report anything that doesn’t fit your ideal is fantastic. But what about those policy breaches which cannot be automatically rolled back (On Change: Prevent is not an option) but for which you want to be immediately notified?

On Change: Log Only will happily notify the Windows Event Log that someone has breached policy but who, in all honesty, ever has that open all day on constant refresh?

An Introduction to Policy Management
Produced: 12/01/2014 17:31:00
This was a new feature added to SQL Server 2008 and it’s just superb. I honestly don’t think it gets the appreciation that it deserves as it seems to be rarely seen or used despite the control it can give you over your instances.

Now I’m not going to claim it’s perfect as there are a few things I would like it to do better, but even so I use it whole heartedly in all SQL Servers I work with as it’s ideal to keep an eye on some key aspects of SQL Server and also to verify settings across multiple instances.

So, what is Policy Management? Well, simply put, you apply a policy to SQL Server and it either enforces it on the spot (live tracking), or reports breaches to you based on a schedule you provide.