At work recently, I explained to a coworker the reasons and benefits of having databases in Simple mode in your development environment. Funnily enough, a few hours later, I see my friend Andie Letourneau (Blog | Twitter) posted a blog on how to adjust your recovery modes in development. While Andie’s homework assignment is to substitute it with a WHILE loop, I have MUCH simpler solution using Policy-Based Management!

First, let’s create the condition and policy. As always you can either use this T-SQL or you can simply download the XML policy and import it:

Download Policy Here

TSQL:

--Create Condition First
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Simple Recovery Mode', @description=N'Ensures databases are set to SIMPLE recovery mode', @facet=N'Database', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>EQ</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>Numeric</TypeClass>
    <Name>RecoveryModel</Name>
  </Attribute>
  <Function>
    <TypeClass>Numeric</TypeClass>
    <FunctionType>Enum</FunctionType>
    <ReturnType>Numeric</ReturnType>
    <Count>2</Count>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>Microsoft.SqlServer.Management.Smo.RecoveryModel</Value>
    </Constant>
    <Constant>
      <TypeClass>String</TypeClass>
      <ObjType>System.String</ObjType>
      <Value>Simple</Value>
    </Constant>
  </Function>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id
GO
--Create Policy
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Simple Mode Check_ObjectSet', @facet=N'Database', @object_set_id=@object_set_id OUTPUT
Select @object_set_id
Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Simple Mode Check_ObjectSet', @type_skeleton=N'Server/Database', @type=N'DATABASE', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Simple Mode Check', @condition_name=N'Simple Recovery Mode', @policy_category=N'', @description=N'This policy ensures that the databases evaluated against are set to SIMPLE recovery mode.', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Simple Mode Check_ObjectSet'
Select @policy_id
GO

Now that you’ve created your policy, you can either evaluate it directly on that server or we can leverage the Central Management Server feature to evaluate this policy against one or more servers in your environment! Since we’re looking at this from an enterprise-level standpoint, we’re going to go over the CMS way of doing this.

Once you’ve established your CMS, you’re going to want to register servers to it. You could register everything directly under the CMS but then it becomes one giant list and that’s not very nice. One method I like to implement in my CMS is to create new server groups for each environment (e.g. DEV, QA, PROD). Within each group, I also create additional groupings for each version of SQL Server (e.g. 2000, 2005, 2008). Once the groups/folders have been created I then register my servers in their appropriate spots.

The advantage of breaking groups up like this is that CMS allows you to query against multiple servers at once. For instance if you were to right-click the folder for DEV, which in my case contains two registered servers in groups beneath it, SSMS will connect to both instances so that you can query them at the same time. It is this mechanism that we’ll use with Policy-Based Management since that feature allows us to evaluate policies against multiple servers in the same way.

To evaluate this policy, right-click the DEV folder and select Evaluate Policies. For source, click on the ellipses button and select either the folder location of the XML file or select the SQL Server instance which you imported the policy to. Once you’ve selected the policy’s location, you’ll see the Evaluate Policies screen with a list. Tick the box for the Simple Mode Check and then click the Evaluate button.

Check that out, you just evaluated policies against all of the databases in DEV! The databases that are NOT in simple mode (per our condition check) fail the policy evaluation and show up with red X’s. To quickly switch those to simple mode simply check the boxes for those that failed the check, then click the Apply button. This will enforce the policy on those databases and switch them for you to Simple mode! Imagine doing this that quickly and easily against hundreds of databases!

If you’re wondering if you can automate this process, the answer is absolutely! Check out the great open-source project Enterprise Policy-Management Framework over at Codeplex. This project allows you not only automate this process and policy enforcement but it also offers some really nice reporting of all this as well!

UPDATE: After chatting with Andie it looks like I forgot to mention a few things. Namely that for those wondering if this works on down-level servers (2000, 2005, etc.) the answer is YES! So long as you have a SQL Server 2008 server acting as your CMS, you can evaluate (certain) policies against down-level servers. Given this confusion I'll write up a post on how all this works and what the caveats are.

The other point to make is that CMS uses Windows authentication only. If you have multiple domains, and there is no trust established between the domains, then this solution won't work. If you DO have multiple domains and trust established, so long as your credentials allow you to traverse domains and the proper security authorizations are in place on the target SQL Servers then it will work.