I posted recently about my run-in with autoclose and my online friend Jorge Segarra (Blog|@SQLCHICKEN) left a great comment on my syndicated post on SSC where he said "Yeah Auto_Close came straight from the devil. One cool thing you could do on 2008 is use Policy-Based Management to evaluate all your servers/databases and make sure auto_close is set to off. Easy to do and saves you a ton of headaches!"

Which led me to this post, how do you do it? Well for June's TSQL2sday I wrote a post all about using Policy Based Management (PMB) to try and enforce some sort of sensible naming convention for stored procedures. And we will follow a similar process here.

An important point to note here is the execution mode of each policy is determined by the characteristics of the facet that is used by the condition in the policy. The Autoclose option is available in several facets including Database, Database Option, Database Performance. I was hoping to implement a policy that prevents AUTCLOSE being turned on altogether but alas I don't think PMB is capable of preventing 'auto close' being turned on at the time the database option is changed, as the facets that include 'Autoclose' only have 'On demand', 'On Schedule', or 'On Change - Log Only' execution mode's available to them. Instead we will have to make to make alternative arrangements to be alerted when the option is set so we can  correctly fix it before it becomes an issue. As you can see from my last AutoClose post, in a small number of circumstance some people have found a use for it but generally speaking it is better for 'auto close' to be turned off.

Create the Policy

I have talked about the steps involved in setting up Policies in a previous post, you can find that here. (http://blog.gre-sqlserver-solutions.com/2010/06/t-sql-tuesday-007-policy-based.html) so I will simply walk through creating the policy.

We have two options when it comes to creating the policy, we can use the management studio GUI or we can use TSQL. We will use the GUI for this demonstration but I will include the TSQL scripts at the end of the post for completeness, the scripts will be generated from Management Studio.

1. Connect to the instance in question in Management Studio

2. Expand the <Management> Folder, you will see 3 sub folders called <Policies>, <Conditions> and <Facets>.

3. We will start with the last folder, the <Facets> folder, expand the <Facets> folder and you will see a big long list of facets.

4. We will use the <Database Options> Facet. Right click on the <Database Option> and select <New Condition>

5. Give your condition a name, mine is called AutoCloseCheck

6. Ensure that the 'Database Options' Facet is selected.

7. In the expressions section click to add a clause.

8. In the Field Column, from the drop down list, pick @AutoClose

9. Ensure that operator is still '='

10. and change the value field to 'False' - thus ensuring that our condition checks for autoclose being set to false.

11. You can add a description in the description tab if you wish. When you're done, click <OK> and we have created the necessary condition to test.

Next we need to create a policy that tests for that condition:

12. Expand the <polices> folder.

13. Right-Click the policies folder and select <New Policy...>

14. Give you policy a name, I have picked the same name as my condition, mainly to avoid confusion so I have called it "AutoCloseCheck"

15. From the <Check Condition> drop-down list pick the condition we created above. in the <Against targets> box Every Database is selected. I'm going to leave that as the default setting as I want the policy to check on each database.

16.In the <Evaluation Mode:> drop-down list I picked the 'On-change: log only' I left the <Server Restriction> set to 'None'

17. In the description tab I simply enter a <description> and some <text to display>: 'Autoclose should be off '

We now have a policy created, so how will this policy help? Let's test the policy by changing the AUTO_CLOSE database option of one of many test databases to true. We can do this with the following script:

USE [master]
GO
ALTER DATABASE [Staging] SET AUTO_CLOSE ON WITH NO_WAIT
GO

What do you expect to happen? That's right because our Facet Database Option does not support prevention we are only able to use the Execution Mode: On Change - Log Only which writes a useful error to the SQL Server log:

So when you come in the next morning and perform your daily SQL Server checks, or if you are very diligent as you should be and perform afternoon checks as well as morning checks on your database servers you will see this error, become very upset with your colleague, or more likely a DEV who has the necessary permissions on a DEV box or a third party supplier who setup the database and you can go and switch off the AUTO_CLOSE option and wrap the offending person on the knuckles...Hey its better than before, you know when it would be left like that until it becomes a problem.

There is something else that you can do though to make sure that you get alerted when the policy fires and writes out the log: You can use Alerts in the SQL Agent and get sent a nice warning email as soon as the database option is changes which means that you can fix the issue before it becomes a problem for you.

Alerting

We can see from the screen shot above that my On Change - Log Only error was raised with a message number of 34053. The execution mode message number can be found in the following table:

We need to setup an alert to look for message number 34053 and get the alert to notify us when it fires, this is how we do it:

1. In SSMS expand the <SQL Server Agent>

2. Expand the <Alerts> Folder

3. Right-Click the <Alerts> folder and select <New Alert>

4. Give your Alert a Name: AutoCloseCheck

5. In the event alert definition leave <All databases> selected for <Database name>

6. Make sure that Alerts will be raised based on <error number> is selected and change the error number to 34053

7. On the response tab select notify operators and select who you want to alert when the error is raised, I use email, you can use whatever best suits your needs.

That's about it, we have looked at Policy Based Management and configuring it to log any changes to the database option AUTO_CLOSE and we have configured alerts to notify us of when that policy is violated so we can take action. The TSQL scripts (generated from SSMS) that can be used to recreate this can be found below. I'd be really interested to hear your thoughts on this, in particular if you used PBM and if you would use a different technique.

TSQL Scripts

--Create the neccessary condition
--script generated using SSMS
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'AutoCloseCheck', @description=N'', @facet=N'IDatabaseOptions', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>EQ</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>Bool</TypeClass>
    <Name>AutoClose</Name>
  </Attribute>
  <Function>
    <TypeClass>Bool</TypeClass>
    <FunctionType>False</FunctionType>
    <ReturnType>Bool</ReturnType>
    <Count>0</Count>
  </Function>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

--CREATE A POLICY using the auto close condition created
--
Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name = N'AutoCloseCheck_ObjectSet',
    @facet = N'IDatabaseOptions', @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'AutoCloseCheck_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'AutoCloseCheck',
    @condition_name = N'AutoCloseCheck', @policy_category = N'',
    @description = N'Autoclose should be off ',
    @help_text = N'''Autoclose should be off ', @execution_mode = 2,
    @is_enabled = True, @policy_id = @policy_id OUTPUT,
    @object_set = N'AutoCloseCheck_ObjectSet'
Select  @policy_id


GO 
--Turn on autoclsoe for a database.
USE [master]
GO
ALTER DATABASE [Staging] SET AUTO_CLOSE ON WITH NO_WAIT
GO

--Create Alert to notify if autoclose is enabled
USE [msdb]
GO

/****** Object:  Alert [AutoCloseCheck]    Script Date: 09/10/2010 09:35:43 ******/
EXEC msdb.dbo.sp_add_alert @name=N'AutoCloseCheck', 
        @message_id=34053, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO