Monitor SQL Server CDC with Customer Data Collector

I’ve posted several times over the years here @MSSQLDUDE on the benefits of SQL Server Management Data Warehouse (MDW) for baselining your SQL Server performance and sending those measurements to a central server for monitoring. MDW makes use of the SQL Server Data Collector feature and is a feature you get out of the box. Here are some of my writings on MDW here @MSSQLDUDE:

There is a debate among SQL Server DBAs about the worthiness of MDW and Data Collectors because they can appear to be a “heavy burden” on your server and provide overhead that you may not wish to occur on your production server. That being said, the way things work in our world and jobs in databases is a give and take with performance, monitoring, scalability and providing functionality to our business users & customers to allow them to do their jobs. Any monitoring and baselining product incur a small performance cost to your servers. There are a number of options out there in the market and many DBAs write their own scripts to store DMV data into tables and files to provide similar functionality.

What I wanted to point you all to is something that I think proves the value of Data Collectors (and MDW) through extending the functionality with custom data collectors.

I’ve been working on a CDC project recently in a large BI/DW scenario and one thing that you notice when working on CDC instead of longer-run SQL Server features like Replication, is that there is not much support in SSMS or other built-in SQL Server tools for CDC.

But what is really nice is this custom data collector for CDC that is included on MSDN from this terrific article onMonitoring the Change Data Capture Process. Scroll down to the Data Collector portion and the code to paste into SSMS is right there for you to create and start your data collector. This is a great way to monitor your CDC log processes.

BTW, you have to make an update to the XML in code from MSDN … Look for the SELECT @parameters code and replace the entire SELECT … XML statement with this:

SELECT @paramters = CONVERT(xml, 
    N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
          <Value>SELECT * FROM UserDB.sys.dm_cdc_log_scan_sessions</Value>

Enjoy! Best, Mark

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

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:

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.

SQL Server 2012 Database Backups in Azure

SQL Server 2012 inlcudes new & updated features to SQL Server Management Studio (SSMS) that provide out-of-the-box integration into the Windows Azure platform-as-a-service Cloud service from Microsoft. It goes beyond just SQL Azure integration as you can now also connect into Windows Azure storage accounts where you can store files and blobs, including DACPAC & BACPAC files, essentially providing DBAs with out-of-the-box backup-to-cloud capabilities.

From a DBA’s perspective, this can be very beneficial because this would allow you to take your SQL Server backups and post them into the Azure cloud where files are automatically protected and replicated for high availability. This will also eliminate the need for you to maintain infrastructure for backups locally on your site. You would utilize Azure Storage for maintenance, retrieval and disaster recovery of your database backups. Here is a link with more details on Azure Storage.

Here are the steps to backup from SSMS 2012 to Windows Azure:

  1. First thing to note is that you will need to sign-up for a Windows Azure account and configure a storage container. You can click here for a free trial.
  2. Now, on SSMS, choose a database to backup. But instead of the normal Backup task, select “Export Data-Tier Application”. This is going to walk you through the process of exporting the schema with the data as a “BACPAC” file output.

3. On the next screen in the wizard, you will select the Azure storage account and container where SQL Server will store the export. Note that it will first backup the database (schema & data) to a local file and then upload it to the cloud for you.

4. Once the process is complete, you will see your exported backup as a BACPAC in your storage container. To restore a BACPAC, you right-click on the file from your container and select “Import Data-Tier Application”.

BTW, this process is identical to the way that you can export & import databases in SQL Azure. You can also easily now move your databases (schema and/or data) to and from SQL Server and SQL Azure with these BACPACs. This is not a full-service TLOG and data file backup like SQL Server native backups. This is more of a database export/import mechanism. But that is pretty much the most interactive that a DBA will get with SQL Azure anyway because you do not perform any TLOG maintenance on SQL Azure database. Microsoft takes care of that maintenance for you.

SQL Server 2012 AlwaysOn Availability Groups In Action

I think of SQL Server 2012 AlwaysOn Availability Groups as both high availability and scale-out database. This is because you can identify multiple secondary servers in your Availability Group that can be marked for read-only. When you connect to the Availability Group Listener, SQL Server will redirect the request of any connection marked as READONLY to the secondary read-only database server automatically for you. Additionally, you can run common administrative functions that take up cycles and locks on your production database server such as backups and DBCC checks to a readable secondary, again making for a higher-performance scaled-out SQL Server infrastructure.

I just posted a short 10-minute video on our Microsoft East Region DW BI database group here: which shows my AlwaysOn configuration from the perspective of an end-user writing and viewing reports. This highlights the high-availability capabilities that AlwaysOn AG’s provide, above and beyond the now-deprecated Database Mirroring feature.

One last thing that I want to re-emphasize which I call-out in the video a few times, is how easy it was set-up AlwaysOn and then Availability Groups. I didn’t have to configure shared storage for the cluster configuration, which makes it very easy to demonstrate on a VM, because I don’t need a SAN. There is no shared storage involved with AlwaysOn and the entire demo in the video is performed on Hyper-V.

System Center for the SQL Server DBA, cont’d SCOM Pack for SQL

My original posting on System Center Operations Manager for SQL Server DBAs start in part 1 of the series here. Today, I want to take just a minute to give you some pointers in getting started using the System Center Monitoring Pack for SQL Server. Notice in that download link, that the pack has been updated to allow you to monitor up to SQL Server 2012, which now also includes monitoring your Always On Availability Groups from SCOM, a very important new feature.

One thing that I always mention to DBAs or other IT professionals who are new to using the SCOM SQL monitoring pack is to start by downloading and taking a look at the online guide for the MP:

There is a LOT in there. This is good. As a DBA and someone responsible for SQL Server database health, you want a lot of monitoring tools and capabilities at your fingertips. But I recommend starting off with a small subset of counters that you will monitor.

As a common best practice when using the SCOM MP for SQL Server, start by picking a list of critical areas in your SQL Server environment that you most need visibility to provide the highest level of service, such as performance, uptime, high availability, replication, etc. This kind of approach will force you to think about which counters and reports to light-up in SCOM instead of overloading a dashboard or NOC with too much noise & chatter.

This is one of the best top-10 lists that you can use to get started is by Gregory A. Larsen from Database Journal:

I generally agree with this list and monitor these counters through normal tools like perfmon, DMVs or MDW. It’s a good starting point for you for database performance. From there, you can start to configure the infrastructure monitoring pieces in the monitoring pack through SCOM for clustering, replication, Always On, etc.

Quick Note on Always On Availability Groups Performance in SQL Server 2012

It’s still early in the lifecycle of SQL Server 2012 (heck, we just RTM’d 2 weeks ago and not yet GA!) but some of the whitepapers, best practices, guidance, etc. are starting to flow out and I’ll make it a point of mine to try and highlight some of these as they come out. That is, the ones that effect projects or customers that I’m working on, anyway.

So here is one that caught my eye: I saw this on Books on Line about making AlwaysOn Availability Groups perform better in terms of resolving network names and IP addresses with the AG Listener:

Always specify MultiSubnetFailover=True when connecting to a SQL Server 2012 availability group listener or SQL Server 2012 Failover Cluster Instance. MultiSubnetFailover enables faster failover for all Availability Groups and failover cluster instance in SQL Server 2012 and will significantly reduce failover time for single and multi-subnet AlwaysOn topologies. During a multi-subnet failover, the client will attempt connections in parallel. During a subnet failover, SQL Server Native Client will aggressively retry the TCP connection.

I copied that directly from the BOL link above. The bolded areas in that passage are mine and I wanted to point out that it states that adding “MultiSubnetFailover=True” is the recommendation for clients connecting to AG Listeners even in single-subnet AlwaysOn topologies (FCI’s as well). If you scroll down in that full passage, you will also note the bit about OLEDB not supporting the “MultiSubnetFailover keyword”, which gives me the opportunity to again remind everyone that OLEDB for SQL Server client connectivity has been deprecated as of SQL Server 2012.