The Continuing Story of System Center for the SQL Server DBA …

Instead of entering a new part to my ongoing series of understanding and utilizing Microsoft’s System Center Suite for SQL Server DBAs, I want to take a break here in the series and catch up on 2 things:

I just finished setting-up a new prototype environment for a customer today and there are 2 really neat areas in Operations Manager that I did not highlight on my earlier posting on SCOM with the SQL Server Management Pack:

One of the nice ways that SCOM takes the perfmon and SQL counters and wraps those up is the Health Explorer (see screenshot) below. Notice the way that it rolls-up aggregated counters up to the top-level objects and targets within your SQL Server environment. When managing large SQL Server footprints, this can be very, very helpful:

You can also integrate SSRS reports by importing RDL into the SCOM reporting capabilities. What I am working on now is taking the SQL Server Management Data Warehouse reports and making those available right from SCOM so that I won’t need to jump into SSMS first to interrogate the offending queries and execution plans that are causing database performance issues.

I want to also call-out System Center Audit Collection Services (ACS), which I called out in my description of an end-to-end auditing solution in SQL Server 2008 R2 using the Security Audit tools here. With ACS, you get the same benefit that SCOM provides in that you can audit not only your SQL Server activity, but also the rest of your Windows environment (and non-Windows, too), meaning that you’ll capture all server activity. ACS integrates with the SQL Server Audit capability through Event or Windows logs and extends the SQL Server built-in capabilities far beyond what SQL Server provides out of the box:



Complete SQL Server 2008 Auditing Solution Example

SQL Server 2008 helped to alleviate the need for DBAs to write DDL triggers or to use traces to audit SQL Server by adding the “Server Audit” capability. However, to have a complete audit solution that can be used by auditors or folks outside of the SQL Server administrator teams, you need a tool to format the audit information. It’s not reasonable to test for compliance or to manually look through complex audit logs.

There are a number of very good products on the market today that you can purchase that are meant for compliance standards and auditing of entire ecosystems. But if you would like to take the SQL Audit capability at least a step higher to where the SQL Server audit data from the audit objects are parsed out of logs and into nicely formatted reports and stored in a database that you can manage, search and monitor, then I suggest taking a look at a really cool sample solution.

This is something that the Microsoft SQL CAT team put together with consultants and a customer that will not do all the things a true auditing and compliance product will do. But it will take you a step beyond the current SQL Server Audit object which today stops at writing to a log file or a Windows log. For everything that I’m showing you in this posting, you can download it all and the complete sample application here.

Once you download that material, you will expand it out to a series of folders. You’ll find the full complete set of instructions on how to set-up & configure the complete solution from the Word document called “SQL Audit Solution” in the \SQLComplianceLab\SQLAudit folder. There are SSIS packages, SSRS reporting projects, the Audit database and scripts and even hands-on labs and policy-based management samples. Pretty cool stuff.

The details, requirements and relevance to compliance standards that this audit solution strives to meet are explained very well in this whitepaper on Reaching Compliance with SQL Server 2008. You will see quite a few references to one of Microsoft’s biggest SQL Server customers, CareGroup, because I’m sure that this is where the Microsoft partner and SQL Server CAT team first implemented with solution. Be sure to replace all things related to CareGroup and their file system (you’ll see a log of references to E drive and C drive folders that aren’t relevant to your servers). When going through this material and implementing this SQL Server Audit solution in your environment, I think you’ll agree that Denny Lee and the partner teams did an awesome job with this and it is great to see that they’ve shared the entire solution with everyone at no cost.

The diagram below is a very simplified high-level look at what the solution will do for you as an end-to-end auditing application. There are sample best-practice policies and security objects that you can use from the documentation and install. Once your SQL Server starts writing the audit logs, you can schedule the ETL process from SSIS (see below) to parse the audit logs and store it in the partitioned tables in the SQL Server Audit tables that are included in the download from above.



A few things to focus on when setting up the reports in your SSRS report server (see below) and your SSIS packages for ETL (directly below) based on my efforts in setting this solution up on my systems include:

  1. In my BIDS screenshot below of the SSIS package, I have the variables window open. Go through and make sure that you are pointing to your Server Audit logs folder and that you’ve removed any references to CareGroup that still exist in some of the code from the implementation at the customer site
  2. Do the same scan and scrub of the SSRS data sources in BIDS and make sure you are pointing to your SQL Servers and audit database that came with the install. You’ll need to do this on the source & target adapters in SSIS, too
  3. Run the ETL from BIDS a few times so that it is easier to troubleshoot and find errors before deploying to SSIS and scheduling. It took me a couple of runs to get the variables set right in the packages. Most of it is actually pretty well documented. But a few things took 1 or 2 tries to get right


Ok. So you’ve gone through the trouble of downloading the material and the scripts. You’ve installed the audit database, configured the SSIS packages, modified the connection strings in SSRS for the reports … Now, what do I get for all of my work & efforts??

See below … I’ve taken just a few of the report screenshots from the document in the Reaching Compliance whitepaper from SQL CAT. Now, this is not going to be a robust, supported solution for auditing in the same vein as off-the-shelf solutions that are sold by Microsoft (System Center Audit Collection Services) or 3rd party auditing tools that you can find in the market today by companies like Lumigent and Idera. You are essentially implementing custom code and are on your own to configure and support. You can always hire a consultant to provide help and support on a solution like this. And like I said earlier, this is much more robust and accurate that wiring together a bunch of triggers or manually scanning log tables and files. Just be aware of the risks of implementing something that is not a supported product.

My Notes on 70-432 SQL Server Exam for DBAs

I’ve finally found some down time this weekend to write a few notes about the Microsoft Technology Specialist 70-432 exam for SQL Server 2008: Implementation & Maintenance. I had wanted to do this for some time, but have been very busy recently. My hope is that if you are a SQL Server DBA looking for gain certification by taking the 70-432 exam, that this will help you to prepare. And so, without any further ado, here are my notes for those taking the exam and helpful tips to be successful:

  1. The Microsoft Press book for 70-432, the MCTS training kit, is essential. The sample tests and the material is spot-on with what you can expect from the test. And beyond just the test, the book does a very good job of explaining concepts that any DBA can find useful in your day-to-day job. Which leads me to point #2 …
  2. Even the most seasoned SQL Server DBAs typically struggle with their first attempt of the cert. I know SQL Server DBAs that have been at it longer than I have and are better at database administration that I am who scored in the 400-600 range on their first attempt (you need 700 to pass). My first attempt was 661 and I studied for weeks prior to the test. After missing the 700 minimum requirement the first time, I went back to the book & prep tests, went through EVERY QUESTION in the book and the prep test again, and passed the 2nd time 2 weeks later. My bottom line is this: even if you think you know everything about SQL Server 2008 implementation and maintenance, STUDY the prep book and prep tests for several weeks FIRST.
  3. Study, study, study. As I just explained in #2 above, you may be a drop-dead awesome SQL Server DBA, but you must use material like the book in #1 above to learn about what the testers are looking for. I am also certified as a PMP in project management and I like to use that PMP test as a good example. Just as with the PMP, a certified DBA or project manager is not necessarily a GOOD DBA or project manager! That’s because the cert tests are looking for very specific by-the-book answers that you may have found in practice don’t always work the way that you want or expect. But the tests are testing your book answers, not your specific experience. So try not to get hung-up on answers that you think are not really correct and go by what you read and practiced in the prep tests.
  4. I can’t do any better at compiling resources to use for your studies than my colleague Buck Woody did at his MSDN blog here. Check it out and don’t assume that you already know it all. Do your work ahead of time. Buck and I are both Microsoft Technology Specialists and I can tell you that I needed to study for hours to pass this exam even though I work with SQL Server every day and have for a number of years.
  5. Remember, SQL Server is very, very large platform, so make sure that you have an understanding of areas that may seem out of the mainstream for long-time SQL Server DBAs, those that started with SQL Server prior to SQL Server 2008. Things like MDW, PBM and spatial indexes, for example, may be areas that you’ll need to brush-up on.


Best wishes and good luck! – Mark