Another Cool PBM Policy for Admins: Part 3

I think this is part 3 … but in actuality, I’ve lost track!

Anyway, what I came across this week was a really cool policy that DBAs will like when using SQL Server 2008 R2’s policy based management (PBM).

This is a policy that you can build using the Certificate facet on your server. Create a condition using DateAdd() to add 7 days to the current date with GetDate() then compare that against the ExpirationDate of Certificates on your servers. You can schedule this policy and check the results each day to alert you of any certificates that will expire 7 days from today:

BTW, have you imported all of the out-of-the-box policies that ship with SQL Server 2008 R2? Take a look under Policies in your Management node at the instance level … There are a LOT of goodies that Microsoft gives you and these are all based on best practices that have been accumulated over the years, including the server hardening rules from the surface area configuration that was moved into PBM from SQL Server 2005:

Data File Autogrow and Policies

Today, I’d like to consolidate 3 different areas of data file growth management from 3 of my favorite sources of best practices and information: SQL CAT, MS SQL TIPS and BOL.

The way that I boil this down for DBAs that I work with is this: for smaller, less critical databases, data file autogrow is a good practice to keep the database functioning and requires less care & feeding. For critical high-activity databases, you can keep autogrow set for data files, but manage the file usage and growth through tools like the UCP dashboard in SQL Server 2008 R2 or a central management tool like System Center. Here is the SQL CAT document with good practices to follow on data file storage in general, with a few good tips on managing data files on critical databases.

A more rudimentary (and complete) discussion on data file growth is on Microsoft’s site here. I point you to that because it is important to note that if you are using a tool that monitors your overall file system utilization, that does not tell you how full your data file is getting and when it will auto-grow. Keep an eye on the free space in the database with a script like the ones here or with Utility Control Point from SQL Server.

Lastly, the really, really awesome new feature in SQL Server 2008 R2 is policy-based management which allows you to apply a standard policy for data file autogrow across your ENTIRE SQL Server footprint. In addition, you can use it to do more than simply evaluate out-of-compliance SQL Server databases in terms of data file management. You can then APPLY that policy to each database. Here is a quick tutorial on how to do exactly that. ENJOY!

How to Enforce SQL Server Naming Conventions

Want to enforce naming standards in your SQL Server environment? For example, do you have a standard for naming stored procedures that you need to enforce?

With SQL Server 2008 R2 policy-based management, this is now possible. Create a “On Change Prevent” policy using the Stored Procedure facet and the @NAME field.

Pandian Sathappan has the full rundown on all of the steps you need to create this here. It’s an excellent and thorough article and I highly recommend it.

How cool is policy-based mgmt?

Ok, week 2 of my (re) onboarding with MSFT and I am slooooowing finding time to blog again. I am working with customers on ALL areas of SQL Server. Those of you familiar with the entire SQL Server stack will know that covers just about ALL things IT: database, BI, DW, administration, development, security, etc, etc, etc.

When I was a DBA on SQL 2000 & 2005 databases, prior to coming to Microsoft for my 1st tour of duty, I worked in environments and for companies where we had many unknown pockets of SQL Server instances. Many of these were “un-official” databases that groups had stood-up, sometimes through organically-grown applications that a group’s guru had put together to cleverly solve business problems.

Now that I am studying for my SS2K8 certifications, I am currently focusing on a SQL Server 2008 features that a lot of colleagues had been talking to me about, particularly when I was at Oracle. As a point of pride that it made the cut in SQL Server 2008, it is “policy based management”.

Very cool, I thought … a way to manage particular settings on a number of remote SQL Server instances from your management studio by enforcing compliance to standard policy settings. One really obvious use case that I read this morning from MS SQL TIPS here is to ensure that your critical SQL Server instances are running in Full Recovery mode. Seems like a no-brainer and that short tip tells the story. Check it out …