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.

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.

SQL Azure: Scale-Out and Big Data

Perhaps the NoSQL / Big Data trend in high-performance computing, made popular by Hadoop and MapReduce will end up being the “killer app” or at least “killer capability” for cloud databases?

I find it be an interesting thought because the currently available Microsoft cloud databsae, SQL Aure, is a complete SQL Server-based transactional database complete with support for SQL Server tools, T-SQL, ODBC, referential integrity, etc. The current maximium single stand-alone database size is 50 GB.

But Microsoft has recently shown a lot of interest in providing support for scaled-out large database workloads, first with SQL Server Parallel Data Warehouse (PDW) and then the recent announcement of PDW support for Hadoop. Scale-out applications built on traditional SQL Server have been around for some time. The typical mechanisms used to do this are based on partitioning or “sharding” the data to fan-out the data and queries across SQL Servers using MS-DTC, replication or Service Broker.

SQL Azure is coming out with a built-in capability to enable this style of “sharded” partitioning called Database Federations. This is a link to a terrific write-up of using these concepts in a Big-Data application, written by Roger Jennings for Visual Studio Magazine.

Note that this capability is not yet available even in CTP (beta) for SQL Azure yet at the time that I am writing this blog post. I like the fact that these capabilities are being surfaced as a built-in T-SQL command. There will be accompanying ADO.NET library changes with APIs and methods to manipulate the distributed data and to query it appropriately as well.

Very interesting, exciting ways that SQL Azure can be used. Once I get access to the CTPs, I’ll start building out distributed apps using that capability and blog my results here for you. In the meantime, that article link above gives you some code samples to start thinking about your Big Data architectures.

Microsoft Cloud BI part II

Head over to the SQL Server Magazine BI Blog where Derek Comingore and I blog about Microsoft BI here. Today, I added part 2 of my series on created Microsoft Cloud BI solutions using SQL Azure, PowerPivot and Reporting Services. Part 1 is also available from that blog site where I created a SQL Azure data mart in the cloud from my on-premises SQL Server AdventureWorks database.

If you have interest in Silverlight BI for mobile or other devices, I’m going to get to that in part 4 and will use some of the techniques that you can find here on MSSQLDUDE blog like those that I talked about at Code Camp and SQL Saturday.

Thanks!! Mark

How to Schedule Database Jobs and Maintenance with SQL Azure

If you’ve started looking at or working with SQL Azure, as a DBA, you may be disappointed to know that much of the visibility and control into the servers & databases that you’ve come to live by with SQL Server are not there in SQL Azure. For example, there are no SQL Agent Jobs and there are no Maintenance Plans. But that doesn’t mean you are smack out of luck. Here are 4 ways to schedule jobs & maintenance against SQL Azure:

1. Schedule jobs & maintenance plans for your local on-premise good old SQL Server 2008 R2 SSMS! You can write T-SQL directly to SQL Azure from SQL Server 2008 R2 by using maintenance plans or jobs with linked servers.

Let me be honest with you, though. Take a look at the Management Node and the System Database folders from SSMS on your SQL Azure instance. Not much there, is there? You also cannot change database context with USE DB commands in SQL Azure. So when you connect into the instance, you either need to have the user set with a default database or do like I did above and run a maintenance job like that one to grab the server sessions, which I can here because it is in the Master database, which is my user’s default database. BTW, there is no sp_who, sp_configure and a lot of DBCC commands are not there in SQL Azure as well.

You must keep in mind that the current use cases for SQL Azure are Web-based database apps where developers need to have a SQL database and they normally do not want to or know how to get involved in the database at this level, which DBAs do all the time with SQL Server. SQL Azure support for system views is listed in that link for you see what is there.

2. Azure Data Sync. This is a Microsoft-provided tool that you can get from Microsoft.com and download it onto your local SQL Server systems. It uses the Data Sync Framework APIs, instead of BCP like the popular Codeplex SQL Azure tool called Azure Data Migration Wizard uses. You can run this tool to copy data and schemas from SQL Azure to another SQL Azure or SQL Server on-premises database, giving you a tool similar to SQL Server backup, for data.

3. Windows Scheduler. You can use PowerShell or even native local SQL Server client tools like BCP to perform one of the most common automated, maintenance DBA tasks: backup the database. SQL Azure does not support the native SQL Server backup/recover commands or capabilities, so you need to either use the database copy capability in SQL Azure to create your own backup, rely on the SQL Azure replicated database scheme, or extract the data with SSIS or BCP. You can schedule a BCP command in Windows Scheduler to connect to your SQL Azure database and backup the data into a local SQL Server database. To keep the schemas synched-up, you’ll need to use a mechanism like data-tier apps.

ex.: (run from local SQL Server box) bcp.exe mark.dbo.Table_1 out  “c:\temp\out.txt” -c -U username@azureserver -S azureserver.database.windows.net  -t “|”

4. Windows Azure Worker Role. If you want to keep the entire solution “in the cloud”, then you can use a Windows Azure worker role and export the data from SQL Azure using BCP. By using the SQL Server client tools in a worker role on Windows Azure, you can perform the entire data backup process in Azure in the cloud by scheduling the job to pull data from SQL Azure and store it into a BLOB storage from Windows Azure. There is a company called Cerebrata that offers pre-scripted PowerShell scripts to do exactly that: copy your data from SQL Azure and back it up in Windows Azure blob storage. Download it here.

Where SQL Server & SQL Azure Converge

Throughout 2011, with each new Microsoft CTP release of SQL Server Denali, updates to SQL Azure and service packs for SQL Server 2008 R2, you are going to see more & more convergence between the traditional on-premises database and the cloud database world, SQL Azure. Add to this mix, the advancements coming from Redmond this year with AppFabric and Windows Azure and you are starting to see these worlds blend together into the goal of optimized and effective IT data centers and workgroups where you seamlessly move data and applications between bare metal to private cloud to public cloud.

Ok, let’s slow down for a second and be realistic about this. We’re not there yet. In fact, each of those Microsoft iniatives mentioned above has separate product teams, release cycles and release mechanisms. But I did want to point out a few areas where you can get started to be ahead of the curve during this very exciting time in Cloud computing. We are nearing an inflexion point where traditional on-premises practices are moving over to Cloud. In our SQL Server land, we have 3 primary ways to begin this transition:

  1. SQL Azure Data Migration Wizard
  2. Data Sync Framework
  3. Data-Tier Applications

SQL Azure Data Migration Wizard

This is a free download tool from Codeplex (http://sqlazuremw.codeplex.com) and it is a very simple, easy-to-use and intuitive utility. It is a tool for engineers, so don’t look for fancy GUIs and Silverlight. But it does an awesome job of migrating your on-premises SQL Server database objects and data to SQL Azure. It handles a lot of the migration steps that you would need to do manually such as identifying unsupported SQL Azure objects and code as well as things like adding clustered indexes on every table (a current SQL Azure requirement). This is a great stop-gap tool to use until Data Sync is ready for prime time …

SQL Azure Data Sync

This is the Microsoft-sanctioned go-forward way to replicate data between on-prem SQL Server and SQL Azure, as well as scheduling data synchronization and replication across Azure data centers and SQL Azure databases. The problem is that the current publicly available version (https://datasync.sqlazurelabs.com) does not sync to or from SQL Server on-premises yet and the new version is still in CTP (beta) which you can sign-up for here.

SQL Server Data-Tier Application

This is functionality that is built into SQL Server 2008 R2 that allows developers and administrators to move units of code built in SQL Server databases, around instances and through a development lifecycle (i.e. dev, test, stage, prod). This is available today and you can deploy SQL Server database schemas that you’ve developed from Visual Studio 2010 to SQL Server 2008 R2 on-prem or to the cloud in SQL Azure.

Joe D’Antoni (Philly SQL Server UG VP) and I are presenting on all of these techniques at this Saturday’s SQL Saturday Philadelphia in Ft. Washington, PA (http://www.sqlsaturday.com). Our presentation material is available for you to view here.

What Makes SQL Azure Compelling? SQL Developer Edition …

In part 1 of “What Makes SQL Azure Complelling?”, I focused on the DBA role and what is good & what is different with SQL Azure from SQL Server.

Now let’s talk about the SQL developer role.

The DBA role is only partially transparent from SQL Server to Azure and in some ways simpler, in other ways limiting and just plain different. For developers, the change will be less intrusive, but have a number of limitations.

One of the unmistakeable benefits of the Platform as a Service (PaaS) approach of Windows Azure & SQL Azure is that you can airlift your applications & databases to the Cloud with minimal impact and changes to code. In fact, with an application that you have written that connects to SQL Server via ODBC, all you have to do is change the connection string to the Azure connection string and everything will work just fine. The Windows Azure management screen even gives you a place to copy the connection string:

There are a few steps you need to follow first. You need to get your database from your traditional on-premises SQL Server database into SQL Azure. To do this, I typically use the SQL Azure Data Migration Wizard from Codeplex which you can download free here. It’s a great tool, very effective, simple and straight-forward. Microsoft is completing a CTP 2 of Data Sync for SQL Azure that will allow you to automate moving data around from SQL Azure to different data centers and also on-prem SQL Server that will operate similar to SQL Server replication, which is currently not supported in SQL Azure.

Next, you will need to make changes to your applications that may be required due to unsupported features from SQL Server in SQL Azure. Here is a complete list for application developers. And here is my list of common gotchas when converting applications from SQL Server to SQL Azure:

  1. Replication is not supported
  2. No support for CLR
  3. Partitioning is not support
  4. No Service Broker
  6. No Fulltext Search
  7. No Sparse Columns

What Makes SQL Azure Compelling?

What makes the Cloud-based version of SQL Server, SQL Azure, compelling? Part 1 …

I am going to have to do this in parts because there are different IT & business roles that will see different value in SQL Azure based on the aspects of their jobs that are affected by a move from on-premises to Cloud. The change can be minimal in some cases or dramatic in others.

Let’s start with the primary role interacting with SQL Server databases: DBAs.

Compelling features:

  1. Quick & easy to provision a new database. By going to your Windows Azure portal, you can request a new database of varying max size, up to 50 GB and have a new database ready in minutes.
  2. Costs. You pay monthly like your database was a utility. If you averaged 10 Gb size for your database in SQL Azure, you pay that rate.
  3. Elasticity. Now, if you start growing to 20 GB and 30 GB, your database is just fine in SQL Azure. You now step-up to the price points for those larger database sizes.
  4. Easy to migrate. There are tools on Codeplex and a new version coming of Data Sync to let you integrate and sync data similar to replication seamlessly between Cloud and on-prem SQL Server.

Things to be aware of – differences from SQL Server:

  1. You do not have control of the system & instance levels of SQL Server in SQL Azure. This is not the same as standing up a box or a VM with SQL Server. You get a database that is part of PaaS (Platform as a Service) in a Microsoft data center.
  2. Backup & recovery is not the same. There is a database copy feature that you can schedule to create “snapshots” of the database that are copies. Backups are built-in through the fact that 3 replicas of your database are constantly maintained by the Azure infrastructure.
  3. High availability is through database replicas and the Azure infrastructure. You get the same replicas and the same infrastructure for each database. You do not set-up mirroring, log shipping, clustering, etc.

That is not everything there is to know, just a few pointers to get you started. I’ll move on to developers next in part 2 … Many thanks, Mark