What is TSQL2sday? Back in late 2009, Adam Machanic (blog | twitter) had this brilliant idea for a monthly SQL Server blogger event (the origin of TSQL2sday). This month’s event is hosted by David Howard (blog | twitter) and this month Dave is letting us chose our topic from any of the prior 25 topics! As my first foray into this event wasn’t until the 14th occurrence, I really like this idea and selected “TSQL2sday #007 Summertime in the SQL” as my second chance topic. Okay, so it is January, but it was 70+ degrees in Houston today, so quite balmy. However, that wasn’t why I chose this topic; I really chose it because this topic was about what is your favorite “hot” feature in SQL Server 2008 or R2. I thought about “updating” the topic to SQL Server 2012, but I’m really not sure yet which new “hot” feature of SQL Server 2012 will turn out to be my favorite – and after 3 years, I definitely know which SQL Server 2008 set of features is my personal favorite – CMS and PBM.
The Central Management Server (CMS) and Policy-Based Management (PBM) features have made the overall management of large numbers of SQL Server instances, well, manageable.
The CMS enables us to organize instances into multiple different classifications based on version, location, etc. We rebuild the CMS on a regular schedule based on the data in our asset management system. This ensures that all DBAs have access to a CMS with all known instances. If you are not familiar with the CMS – it does not grant any access to the instances themselves and connectivity using it only works with Windows Authentication, so there are no security loopholes here.
We then use these CMS groups as input into our various meta-data and compliance collection processes. Approximately 90% of our technical baseline compliance evaluation is accomplished via policies in PBM. We’ve incorporated all of this using the EPM (Enterprise Policy Management) Framework available on Codeplex with a few tweaks of our own to work better in our environment.
If you haven’t yet checked out the CMS and PBM features, I encourage you to do so today. I have two previous blog entries relating to this topic – “Managing the Enterprise with CMS” and “Taking Advantage of the CMS to Collect Configuration Data”. I’d also highly recommend that you watch the SQL Server MCM Readiness Videos on the Multi-Server Management and PBM topics.
And, it is good to know that by the time this entry is posted – we should be back to our normal 50 degree January weather in Houston!