Configure a SQL Server Standalone Performance Monitoring Instance

SQL Server 2008 R2 Enterprise Edition has enough combined features that you can now centralize monitoring your SQL Server instances from a single point. What this allows you to do is to enable pro-active performance monitoring of your SQL Server infrastructure so that you can identify trends and alerts in common areas that require monitoring such as query performance, server capacity and resource utilization.

This is not meant to replace more feature-rich off-the-shelf products that monitoring the health of SQL Server with specific designs that allow DBAs to take SQL Server performance monitoring to the next level. But that being said, here is a common set of steps that you can take to enable centralized monitoring with SQL Server 2008 R2:

  1. Determine how many instances you are going to monitor centrally from this monitoring SQL Server instance. If the number of instances is greater than 25, then you may want to buy the SQL Server license for this monitoring server as Data Center because the UCP feature which I’ll touch on in this posting, requires Data Center to monitor more than 25 SQL Server instances centrally with UCP. At minimum, you will require Enterprise Edition so that you can use MDW (also explained below) which is required at each monitored instance, too.
  2. The hardware for the monitoring server does not need to be the beefiest of your iron in terms of RAM or CPU. This is a monitoring server, not a database server for OLTP or DW workloads. That being said, you will centralize data collection of performance stats across your instances here, so this server will need disk space.
  3. The out-of-the-box (with SQL Server EE) features that we’ll use to make this work are Utility Control Point (UCP) , Management Data Warehouse (MDW) and Central Management Server (CMS). CMS is available in ALL editions of SQL Server 2008. I’m not going to go any further in this post about multi-server management and central management of your servers. I’ll stay focused on monitoring SQL Server. I’ll leave you with a promise to follow-up with a similar entry on how to MANAGE SQL Server centrally with CMS, DAC & PBM. Let me just say this: centralizing your monitoring on a single instance like this helps to set you up for centralizing management, too. So this becomes another added benefit to this technique and approach.
  4. Let’s start with MDW on your central monitoring instance of SQL Server (2008 R2, of course). In SSMS on the Management folder, right-click on “Data Collection” and select “Configure Management Data Warehouse”. You will run through the wizard here to set up the MDW collection database on your central instance.
  5. Next, configure MDW on each instance that you want to monitor. When you run through the wizard on the monitored instance, you want to set-up the data collectors to collect stats on that instance and send the data to the centralized MDW database. Do not just default to setting up each & every instance of SQL Server to collect and send to MDW. The reasons for this include that not every instance that you have in your environment will be Enterprise Edition and you also need to understand that based on the # of instances & stats you collect and the collection interval will effect the amount of data being stored and retained in the MDW centralized database. This can result in very large MDW databases. Be sure to automate the purge history job for the data warehouse.
  6. Now configure UCP on the central monitoring instance. To get to the configuration tool for UCP, choose View | Utility Explorer in SSMS. You will see the set-up option now in SSMS for UCP. Once you’ve walked through the wizard to set up the local centralized UCP instance, you can now enroll instances into the UCP. This is done from the central server in the Utility Explorer. Select the Managed Instance node, right-click and select Enroll Instance. Be aware that these instances must be SQL Server 2008 R2. In the NOTES section below, I’ll point you to some ways that you can use MDW for SQL Server instances other than 2008 R2.
  7. Lastly, Central Management Server (CMS) will allow you to create groups in the central monitoring server’s SSMS where you can run queries and access the remote instances from a single SSMS instance. Why this is important to centralized performance monitoring is because with CMS, one thing that you can do is to run DMV queries across all of your monitored instances from that single central instance with one single query execution. This will allow you to get results back from a number of instances around missing indexes, long-running queries, buffer size, etc. by running 1 query.


Here are my examples of how to configure MDW to collect through a pull mechanism instead of the classic push data collector mechanism so that you can collect stats from SQL Server instances that are not SQL Server 2008 instances.

Here is where I talk a little bit about actually using these tools on your monitoring instance once you have it up & running.

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.

CLOSED: Free SQL Server Performance Monitoring Seminar in Malvern MTC

**** We had to close registration for this 20-seat seminar as the response was so quick and we filled it up!

**** If you sent me an email and received a response back that you are registered, then we will see you here on June 22 @ 2 PM.

If not, don’t worry … I promise to work to schedule another SQL Server free seminar in the Microsoft Malvern office again very soon.

Thanks! Mark




Are you going to be in the Philly are on June 22 from 2-5 PM? We are hosting Sebastian Meine for a 3-hour complimentary seminar in our Microsoft Technology Center in Malvern, PA. This free training will focus on SQL Server 2008 R2 performance Monitoring and will include snacks & beverages.

If you can join us, that would be great! Just send me an email here. You must register with me in order to attend. Below is the flier for the seminar. Many thanks! Mark

SQL Server Performance Monitoring Basics — Will my SQL Server keep up with the workload of tomorrow?

This class will introduce the concepts and tools to recognize performance trends to identify and address small problems before they become big ones.

To accomplish this we will:

-­‐ introduce the ACIDproperties, focusing on Isolation including locks and latches

-­‐ explore the query lifecycle with query compilation and the three query execution phases

-­‐ investigate blocking, showing ways to monitor for blocking occurrences

-­‐ examine performance-­‐related DMVs

The class  closes out by introducing the Management Data Warehouse, a tool to use on all your servers to monitoring the items covered in this class.

After  attending the class, you will be able to:

-­‐ describe ACID and its components and explain its  impact on concurrency

-­‐ differentiate between locks  and latches

-­‐ explain the query lifecycle

-­‐ identify blocked and blocking sessions and their SQL statements

-­‐ use DMVs to get a picture of your server health

-­‐ monitor  important SQL Server performance metrics over time using the Management Data Warehouse

-­‐ see  important trends in your performance metrics

SQL Server 2008 R2 SP1 Preview is Available

The CTP (aka beta) of SQL Server 2008 R2 Service Pack 1 is out & available. Go here for details and to download. Microsoft’s community techology preview program is our way of releasing betas to the community for you to test out, try out and provide feedback as part of the release process. This is not a production-ready release and so do not use this yet on any production SQL Server 2008 R2 databases yet. But please do download it and give us your feedback on your development and test boxes.

The SP1 for 2008 R2 is essentially CU 1 – 6, so this is good news for those who have waited for an SP instead of actively loading each cumulative update. You will also get a few new features which are listed for you in the product page blog link I listed above. Some of the things that I noticed are new in-place upgrade methods for Data-Tier Application packs and the ability to control Disk Cache for PowerPivot.

Enjoy! Mark

SQL Server 11 Denali and 2010 Year-End

I won’t repeat my most recent SQL Server Magazine BI Blog posting here so instead just scoot on over there and read it here. It focuses on SQL BI but I did spend some going into a little bit of detail on the direction of the BI Blog for 2011 in terms of SQL Server data warehouse products, Cloud Computing with Azure and a few Denali features to keep your eyese on. Peace in 2011!

SQL Server 2008 R2 Feature Pack

I’ve run into a few inquiries recently where folks are having trouble locating all of the add-on goodies for SQL Server 2008 R2 in a single place. Some of this confusion seems to originate from the R2 CTPs and bits & pieces of functionality that had their own trials or early releases as separate downloads.

Go here for the latest definitive SQL Server 2008 R2 Feature Pack with all things grouped together such as remote blog storage, sync framework, powershell extensions, migration assistant, many more.

Enjoy! Best, Mark

Slides from SQL Server 2008 R2 Event

Hi everyone! Thank you to those who were able to find time on their busy calendars to join us today for Microsoft’s overview of SQL Server 2008 R2 in Malvern, PA. The focus of today’s session was Microsoft’s BI vision with a few additional discussions on the new features, products and database advances of SQL Server 2008 R2. It was great to meet everyone today!

Here are links where you can download my presentation as well as the presentation from today’s sponsor, Solid Quality Mentors.

Thank & best, Mark