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.

What Does the New SQL 2012 BI Environment Look Like?

This is sort of a continuation of my recent tool by tool exploration of the SQL Server BI ecosystem in SQL Server 2012 that I’ve been writing about for SQL Server Pro Magazine here and here.

For years, I’ve carried around with me many different high-level data flow diagrams of what and end-to-end BI solution using the Microsoft stack would look like. Come to think of it, I was able to use essentially the same diagram in SQL Server 2005 and SQL Server 2008. Some of the rendering tools changed like Proclarity, PerformancePoint and SharePoint added more BI features. But there was always SSRS, SSIS and SSAS, so I would use something like this below:

When SQL Server 2008 R2 came to market and introduced PowerPivot, I still stuck with this general architecture because PP was still on the uptake / heavy-lift portion of the curve and the majority of production-ready BI solutions were using SSAS for the semantic modeling and cube building.

SQL Server 2012 has changed the game enough such that I’ve started a new data flow diagram in Visio, albeit not as detailed or fancy as the one that I show above.  A big reason for that is (1) I just created this new diagram this week! And (2) it has to evolve over time. As SQL Server 2012 BI solutions using Tabular Model databases and techniques becomes more mature and builds up a larger set of best practices and lessons-learned, then I will update these diagrams and share them here on my blog as well as over at SQL Server Pro Mag.

Now that SQL Server 2012 fully embeds and supports columnar compression through the Vertipaq engine in SSAS, you can build semantic models with Visual Studio or with PowerPivot. To use the Power View visualizations such as I am depicting in this diagram, you will need to have a BI Semantic Model, so I’m now shifting to this guidance in many cases. Using PowerPivot for data modeling, IMO, is very advantageous because it expands the data analyst community to Excel users and data experts and allows for easy trail-and-error style of data modeling whereby Excel becomes the design surface to test your models through Pivot reports.

April Catch-up on Recent Goings-on

Time for an end-of-the-month catch-up on the goings-on of the life of a Microsoft SQL Server TSP!

My latest blog entries for the SQL Server Magazine BI Blog are on Enterprise Information Management (EIM) tools in SQL Server 2012 and on using the new SSAS Tabular Model database paradigm using Report Builder as your reporting tool:

I was honored to have my entry on SQL Azure Reporting Services in the latest edition of SQL Server Magazine (now SQL Server Pro) and now online only (subscription required):

I completed my Windows Azure Web application for event registration using Windows Phone 7 and Silverlight UIs and successfully deployed it at Microsoft’s Women Executive retreat in Philadelphia last week. I am making a Codeplex project for the code and will publish the App in the Windows Marketplace soon. Look for a write-up on that on MSDN and this blog shortly.

If you are in the Philly area May or June, I will be presenting the SQL Server 2012 overview for the local .NET Code Camp at the Penn State Abington campus on May 12 and our SQL Saturday on June 9. There I will present DW management in SQL Server and Cloud BI. Both of these events are on a Saturday and include an entire day of free training immersed in .NET and SQL Server, respectively.

Well, back to authoring SQL Server & BI demos, presentations and planning my weekly customer visits for next week … That is a MSFT SQL Server TSP’s life after all!

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.

Nice New SQL Azure Integrations in SQL Server 2012

Have you downloaded the newest RTM (release to manufacturing) version of SQL Server 2012 yet? If not, download it here.

And if you are a big Cloud Computing enthusiast like I am, you will definitely want this latest version of SQL Server. The SQL Server 2012 RC0 is missing some of the newly-released integrations to Azure that I am going to highlight below. These are only available in the new RTM version of SQL Server 2012:

  1. Connect directly to your Azure Storage from the Azure Storage connector on SSMS Object Explorer. Here you can use Azure blob storage for your database backups to eliminate the need to use tape libraries or eliminate local storage for database backups. Azure Storage is automatically redundant with 3 copies of your data kept safe in Microsoft data centers.
  2. It is now super-easy to move database schemas from on-premises to Azure cloud in SQL Azure using the updated Data-Tier Application or DACPAC capabilities. And this function is also much less sensitive to database objects than in the previous SQL Server 2008 R2 first version of DAC along with ability to single-click deploy your database to SQL Azure.
  3. SQL Azure has introduced a new built-in programmatic capability to design scale-out OLTP SQL Server solutions using a sharding technology known as Federations. These are now manageable and visible natively in SSMS.

Resource Governor Updates in SQL Server 2012

When I travel to visit with DBA teams from different customers there are a number of common recurring problems and issues that it seems all SQL Server DBAs are facing as part of their daily routines. It is only natural for a DBA for any data platform to focus on database performance, uptime and business continuity as the 3 main areas of a DBA’s job.

But within database performance and uptime, there is a feature in SQL Server that often do not see utilized enough. That is resource governor. You can search the MSSQLDUDE blog here at WordPress to find my postings about resource governor. One of the best resources in gaining an understanding of resource governor is here at’s How-To video collection.

There are 2 very important things that resource governor provides a SQL Server DBA that directly help you to produce results day-in and day-out:

1. You can “classify” all incoming connections to your SQL Server by aspects of the connection string from the client like application name, user name, group name, etc. I recommend doing this on your busy production servers even if you don’t intend on necessarily managing resources and throttling those connections. This is because you end-up with a system that can now be managed & monitored more granularly. Instead of monitoring or managing runaway queries or busy connections from a server level, you get a picture of your system based on application, user, group, etc. by Resource Pools which you can see in my perfmon screenshot below:

2. Now that you’ve classified the connections, you can set thresholds on resources and governor your server at a more effective granular level for CPU, memory, MAXDOP and more.

So, what’s new with SQL Server 2012 Resource Governor? There are 3 areas that I think will be very beneficial to DBA when using Resource Governor in 2012 over SQL Server 2008 R2:

  1. Current CPU throttling only kicks-in when there is contention with other busy connections. SQL Server 2012 resource governor allows you to set a hard cap on CPU for each pool
  2. SQL Server 2012 has some changes to the memory manager that now allow resource governor to control memory outside of the query grants, which is all that setting MAX memory in resource governor can control today in SQL Server 2008 R2
  3. You can even now affinitize a resource pool (based on your classifier function) to a scheduler or NUMA node