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.

Database Mail Working But No SQL Agent Alerts
Produced: 05/01/2014 15:20:00
This is a common “problem” that I see within SQL Server and was asked about just yesterday, but 99% of the times, and with this particular instance as well, it’s actually not a problem at all just a missing setting within the SQL Server Agent.

The key thing to remember here is that Database Mail is part of the SQL Server Service which means that the SQL Server Agent cannot automatically know that it’s present and active and which settings to use. Therefore although you may have set up your Operators and are able to select them within the Notifications tab of a scheduled task, you still have to tell the SQL Server Agent which email account and profile it can use.