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

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.

 

Microsoft System Center Tools for SQL Server DBAs – Intro

This post is an introduction to the beginning of a new 4-part series that I am going to start this week. What I am going to do is to introduce my primary audience, SQL Server DBAs, to 4 of my favorite offerings in the Microsoft System Center Suite that are specific to helping maintain a healthy & scalable SQL Server environment. There are certainly a lot of tools included with SQL Server, especially the 2008 R2 enterprise edition, that make management, monitoring and maintenance of your SQL Server environment easier, but up to a limited point. I am going to consolidate my past postings here on MSSQLDUDE shortly after this series to help you evaluate your options between in-the-box capabilities in SQL Server management, monitoring and maintenance and the System Center options to extend those capabilities.

When you evaluate these options, it is important to also think about some of the other non-Microsoft 3rd party options, too. But one of the advantages that I like to point out with a suite like System Center is that your investment in data center tools like System Center go beyond just the database-specific offerings that I am going to mention here. Not only is there more in the box with System Center than I can cover here, but it can also extend to provide similar benefits to managing the entirety of your data center environment including other database types like Oracle databases, and other infrastructure systems like Exchange, operating systems, etc.

The 4 areas that I will focus on in parts 1 – 4 will be:

Part 1: Monitor SQL Server with System Center Operations Manager (SCOM) and the SQL Server Management Pack

Part 2: Manage SQL Server virtualization and “private cloud” or “data center optimization” with System Center Virtual Machine Manager (VMM)

Part 3: Backup protection and automation with System Center Data Protection Manager (DPM)

Part 4: Manage server configuration with System Center Configuration Manager (SCCM)