System Center for the SQL Server DBA: Wrap-Up

I’m going to call the series on System Center for the SQL Server DBA complete with this one last post today. First, here is the full series to date … my apologies for the lack of post naming consistency!!

System Center for the SQL Server DBA Intro

The Continuing Story of System Center for the SQL Server DBA

Monitor SQL Server from System Center Operations Manager

SQL Server management pack for SCOM

System Center for the SQL Server DBA Part 2: DPM

System Center Data Warehouse for SQL Server DBAs

What I wanted to point you all to is an update to my methodology of using SQL Server 2012 business intelligence leveraging in-memory analytical models to provide very important insights into your SQL Server environment with System Center Operations Manager. The link is here to my blog on this on SQL Server Pro Magazine.

By leveraging the SCOM data warehouse, you can create compelling reports quickly with a semantic model that includes SQL Server performance counters, server health and history for long-term history and trends. Use this to create your server performance baselines, alerts, trends, etc.

The take away for SQL Server DBAs is that it is very, very important to gain that level of insight into your databases.

Thanks!! Mark

Advertisements

Continuing Series: System Center for the SQL Server DBA (SCOM Data Warehouse)

Not really a new part to my multi-part series on System Center for the SQL Server DBA … I’m going to post more on the other products in the System Center Suite shortly, but I want to go back to the bread & butter tool for the SQL Server DBA in System Center: Operations Manager. OpsMgr (or SCOM) is the tool where SQL Server DBAs can view alerts, events and overall health monitoring of your entire environment including application & web monitoring, server monitoring as well as databases. With the SQL Server Management Pack add-on to SCOM, you get a large set of SQL Server counters added to your monitoring tool that you can put into dashboards and reports.

Where I want to focus today is the data warehouse feature that ships with SCOM. When events come into the SCOM server from agents, they are logged in both the operational SQL Server database schemas as well as the data warehouse schema. The DW schema keeps history that gives you a wealth of information to report against from recent performance and health to historic reports.

In the box with System Center and the SQL Server MP, you get several reports already built into the catalog for you. To extend those, you use a SQL Server reporting tool like Report Builder against the data warehouse schema. For the current project that I’m working on to extend SCOM for SQL DBAs, I used the new SQL Server 2012 reporting tools with PowerPivot and Power View (see below).

In order for you to build reports from the SCOM DW, you are going to have to poke around at the documentation for the data model, reverse engineer existing RDL reports (which you will find on the SCOM server) and read the reporting guide and blogs at these links:

http://technet.microsoft.com/en-us/library/gg508713.aspx

http://blogs.technet.com/b/momteam/archive/2008/02/26/operations-manager-report-authoring-guide.aspx

Once I had a good enough understanding of the data model, I was able to build a tabular model cube in PowerPivot by connecting directly to the SQL Server DW tables and then stored the cube in SSAS. You don’t need to use the in-memory analytics tools that I show above to report against a cube, but having some sort of a semantic model in tabular, PowerPivot, SSAS UDM, report models, etc. will make the process of building and maintaining SCOM reports much easier. Otherwise, you are relegated to creating on-the-fly joins or using the data model views, which limits you to what is presented. Certainly, in my mind, building a cube will provide much more meaningful business intelligence based on events & alerts that you collect into System Center from your entire SQL Server environment.

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:

http://download.microsoft.com/download/E/E/7/EE797D69-02B2-420D-B0F2-196906CCE063/Whitepaper-Audit_Collection_with_System_Center_Operations_Manager_2007_final.pdf

 

 

System Center for the SQL Server DBA Part I: SCOM

I’ll start part 1 of the Microsoft System Center series, focused on what a SQL Server DBA should know about System Center, with System Center Operations Manager, or SCOM, with the SQL Server Management Pack. When you have licensed the System Center suite of products, you can use these different pieces or put them together in your data centers for what we are now calling a SQL Server “private cloud” that has elastic scale, maximum server consolidation capabilities, charge back capabilities, self-service provisioning, the greatest data center economies of scale and (my favorite) load-balanced SQL Server with zero downtime configurations. The SQL Server Monitoring Pack for SCOM is downloadable here and adds a very distinctive monitoring capability to your SQL Server infrastructure. Out of the box, the SCOM SQL monitoring pack can monitor all areas of your SQL Server instances and databases by using the SCOM agent on that server. This also then allows you to create SLAs and root-cause analysis that can drill down from an application or server alert that you’ve configured down to the database level all from the System Center GUI. As you can see from this health monitor roll-up diagram from SCOM below, you will be able to monitor all aspects of SQL Server including database performance and health areas including database files, long-running jobs, blocking sessions, compliance and many others:

This is similar to what you will find from 3rd party monitoring tools on the market today. I just want you to become familiar with what you can use to monitor SQL Server from System Center. As I take you through the rest of the System Center suite and its applicability to SQL Server management, you may find that the combination of monitoring, management, auditing, virtualization, etc. that the System Center Suite brings to the table may be a more compelling value proposition. I’ve also put a few screen shots below that show how SCOM can bring together your SQL Server health with the rest of the environment that System Center is monitoring from server agents (including Unix, Linux, Oracle and more) and gives you a much wider lens from which to view your overall system health. Now, having said all of that, and since I pretty much live exclusively in DBA land these days, as a SQL Server DBA, you may not want to have to look into ANY tools that are outside of your daily bread-and-butter tools like SSMS. In that case, you can set-up SQL Server with a central management and monitoring server instance using SQL Server Enterprise Edition to get visibility into just your SQL Server investments using MDW for data collection and trending, UCP for dashboards, PBM for management and SQL Audit for auditing.

 

Monitor SQL Server from System Center Operations Manager

I’ll start part 1 of the Microsoft System Center series, focused on what a SQL Server DBA should know about System Center, with System Center Operations Manager, or SCOM, with the SQL Server Management Pack. When you have licensed the System Center suite of products, you can use these different pieces or put them together in your data centers for what we are now calling a SQL Server “private cloud” that has elastic scale, maximum server consolidation capabilities, charge back capabilities, self-service provisioning, the greatest data center economies of scale and (my favorite) load-balanced SQL Server with zero downtime configurations. The SQL Server Monitoring Pack for SCOM is downloadable here and adds a very distinctive monitoring capability to your SQL Server infrastructure. Out of the box, the SCOM SQL monitoring pack can monitor all areas of your SQL Server instances and databases by using the SCOM agent on that server. This also then allows you to create SLAs and root-cause analysis that can drill down from an application or server alert that you’ve configured down to the database level all from the System Center GUI. As you can see from this health monitor roll-up diagram from SCOM below, you will be able to monitor all aspects of SQL Server including database performance and health areas including database files, long-running jobs, blocking sessions, compliance and many others:

This is similar to what you will find from 3rd party monitoring tools on the market today. I just want you to become familiar with what you can use to monitor SQL Server from System Center. As I take you through the rest of the System Center suite and its applicability to SQL Server management, you may find that the combination of monitoring, management, auditing, virtualization, etc. that the System Center Suite brings to the table may be a more compelling value proposition. I’ve also put a few screen shots below that show how SCOM can bring together your SQL Server health with the rest of the environment that System Center is monitoring from server agents (including Unix, Linux, Oracle and more) and gives you a much wider lens from which to view your overall system health. Now, having said all of that, and since I pretty much live exclusively in DBA land these days, as a SQL Server DBA, you may not want to have to look into ANY tools that are outside of your daily bread-and-butter tools like SSMS. In that case, you can set-up SQL Server with a central management and monitoring server instance using SQL Server Enterprise Edition to get visibility into just your SQL Server investments using MDW for data collection and trending, UCP for dashboards, PBM for management and SQL Audit for auditing.

 

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.

NOTES:

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.