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: http://www.youtube.com/watch?v=TF9_Kn2Iqy8 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.

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 Microsoft.com’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

SQL Server 2012 Roadshow in Malvern MTC

Many thanks to all of those who were able to attend today’s roadshow in Malvern, PA for our Microsoft SQL Server 2012 here in the Malvern Microsoft Technology Center!

As promised, here are some follow-up links and presentations that I have to share with you all. If you have any further questions or require anything else from today’s session, please post follow-up questions in the comments and I promise to reply back with some answers or further material.

You may also follow this blog by subscribing or follow my twitter @MSSQLDUDE for other updates and events related to SQL Server.

Thank you!! Br, Mark Kromer

This is a link to the primary SQL Server 2012 overview presentation

Here is the Microsoft page with presentations and additional information about the database Private Cloud and Consolidation Appliance that I talked about today.

If you would like to try out the awesome new BI reporting tools in SSRS for SQL Server 2012, we have publicly-available live data models built on our new in-memory BISM analytics with Power View that you can play around with here. This is the Power View tool that AJ was demonstrating today in the BI sessions.

There were 2 questions that I promised to follow-up on for the audience today and here they are:

1. Here is the link to the Upgrade Advisor for SQL Server 2012

2. Chargeback appears to be part of the Microsoft System Center suite’s capability set for VMs, meaning that it is meant for chargeback in virtualized private cloud scenarios.


2011 End-of-year Microsoft BI Wrap-up

Welcome to the final week of 2011 everyone!

I have a couple of really quick loose ends to tie up here in the blogosphere for Microsoft Business Intelligence. Ever since Microsoft acquired Proclarity in 2006, they have shown a real commitment throughout the organization to business intelligence. Microsoft’s investments and focus on democratization of BI and BI for the masses has been a huge success, particularly with self-service for business decision makers and the decision to make BI accessible to Excel and business users.

So, that being said, there are just 3 topics that I want to close up on in my MSSQLDUDE blog this year:

  1. A few friends of mine here in the Microsoft SQL Server & BI field on the east coast here in the U.S. have started a YouTube channel where we are recording our monthly lunch ‘n learn sessions, with a focus on BI & DW: http://www.youtube.com/user/MSFTSQLBIEASTEPG
  2. I have written about the new Enterprise Information Management (EIM) capabilities that SQL Server 2012 is exposing and expanding at blogs such as SearchSQLServer, MSSQLDUDE and SQL Pro BI Blog. I can remember pushing hard for data quality, MDM and expanding those capabilities into ETL through SSIS back in a series of meetings in Redmond in 2007. That is all coming into place now in the SQL Server product and coming to market finally. I wanted to point you all to a nice series of videos to explain how to get started with the DQS product coming in SQL Server 2012 to provide data quality services to your BI solutions here.
  3. Lastly, I wrote a quick blog with an intro to the new Silverlight Power View BI reporting and data exploration tool in SQL Server 2012 here. Unfortunately, the image links do not seem to be working, so I’ve included the thumbnails to th screenshots of Power View and the SharePoint BISM data connection page below. Notice the awesome Silverlight-based data visualizations that you can now generate out-of-the-box with Power View, which is part of SQL Server Reporting Service in SQL Server 2012. You can try this now, today, with the SQL Server 2012 RC0 download.

Happy New Year!!

SQL Server 2012: What is “Always On” ??

This is not meant to be a posting to describe the details of the new SQL Server Always On High Availability (HA) capabilities. You can go here for a nice overview from Andrew Fryar’s blog.

What I want to make sure that you understand is that Microsoft has created a name for all of the SQL Server HA capabilities called “Always On”. I guess you can think of this as a marketing chore, or a more simple way to think about or name Log Shipping, Clustering, Mirroring and Availability Groups.

Notice that last item in my list above: Availability Groups. That is the real NEW capability in SQL Server that uses Database Mirroring features to allow multiple readable secondary replicas and groupings of databases for failover, backup and reporting.

Now, note that on the new SQL Server 2012 Books On Line page on MSDN for licensing Availability Groups, it is an Enterprise Edition only feature, while “Always On” includes Clustering, Mirroring & Log Shipping, all of which still partially support Standard Edition of SQL Server, click here.