Equivalence Classes & Cliques
In this series, I’ll cover the practical side of integrating the “cloud” – specifically the Microsoft Azure Platform – for the SQL Server professional. It’s a bare-bones, get-me-started, show-me-the-money kind of article that has only three elements:
- Situation – What you want to do, not what the platform can do. I’ll start with a one-sentence description of what you want to do. I’ll expand that a bit to make sure we’re on the same page
- Concepts – How it works and why. This is the background you need to know so that you can make sound decisions. But hey, if you’re in a hurry or you’re lazy, you can even skip this part. Since Microsoft Azure changes a lot and I’ll have to leave things out, this section includes references to the documentation at Microsoft and other articles you might find useful.
- Implementation – This is the “just-show-me-how” part. I’ll include the steps, things that you need to think about to get the steps working, and screen-shots of how I did it on my own machine.
That’s it. Those three parts, and you’re off and running. You can search this site for more articles in this series, or even suggest a situation you want covered (use the comments). Who knows, you might be able to get me to do your work for you, and where’s the harm in that?
I’ll repeat a lot of the sections below in each article. I’ll do that so that you don’t have to link off to this article each time for things like creating an account and setting up the components. Also, I can then copy the work and get credit for it. (I’m training to be a manager)
Short Version: You have SQL Server databases that you want to back up offsite with multiple redundant copies, and you want to be able to restore them from time to time to make sure they work.
(You can skip to the implementation part now if you want but you shouldn’t because don’t be so lazy)
Longer Version: In most organizations, there are databases behind an application. As the database administrator – whether that’s your title or you were just given the job because you weren’t quick enough to think of a good reason that you couldn’t do it – your job is to make sure you can get the data back in two instances:
- The data goes missing for some reason (hardware, software, oops I deleted that file did you need it?)
- The data goes bad (hardware, software, oops I changed all the prices to 1.00 and apparently it should have been 10.00 OMG could you save my job I owe you a favor can I stand here while you fix it I think I’m going to be sick)
One of your options is to back up the data on-premises to a hard drive or to a tape system you have locally. That works fine. In fact, you probably should do this anyway so that you have a copy of the data that you can restore quickly. And you should also back up that local file to a tape or removable drive so that you have another copy just in case the hardware goes bad, and so that you have more than one copy to restore in case you need an earlier version.
But those solutions have a glaring problem: what if something happens to the building, like a fire (Mercy, it’s a FIRE) or hurricane or something like that? In the case of the tapes, there is a solution. Since they are a separate object, you can hire a service to take them somewhere else and bring them back if you need them.
Funny Story: I once had a guy who threw the tapes in his car to “offsite” them. We needed one of them one day, so I got them from his car. I live in Florida, and the weather here can be so hot it makes your teeth sweat. The tapes had formed into something resembling a cowpat or cow dung moulded from a solid block of plastic. I never did find out where that guy went to work after that. Turns out this story really isn’t that funny after all.
Getting the tapes back from a service can take time. Sometimes you’re in a hurry, and it would be nice to start right away on the restore process to get that data back.
Your solution is to use the Storage feature of Microsoft Azure to keep the backups for you. It keeps three copies in a datacenter, and copies all three to another datacenter (if you want). You have the ability to take the backup directly to Storage built in to SQL Server 2012 (SP1, CU 2 – Microsoft now calls this feature “SQL Server Backup to URL”), there’s a free tool from Microsoft that can do it for other versions, you can use a simple copy command from PowerShell, or you can use third-party tools like the Azure Explorer from Redgate, among others. I’ll show you how to do all of that.
The Microsoft Azure platform has a lot of components and features, and all of them can be used separately or together. The component I’ll focus on for this solution is called Storage.
The Money Part
Before I go into all that, you’ll need to create an account for Microsoft Azure, called a Subscription. You can have one or more of these, and it forms the basis of how you’ll be billed. There are free offerings, you can use your Microsoft Developer Network (MSDN) benefits, or you can pay for one outright. A subscription holds all of the other parts of Azure you can create. You’ll see how to set up a subscription in the Implementation section below.
So how much does storage cost? It’s by size, and by month. There’s a full calculator (here for data management prices ) you can use for the most current pricing, but as of this writing it looks like Storage is billed out at 1.20 (US currency) per 25GB, if it’s Geo-Redundant (more on that in a minute). It’s cheaper if you don’t add the redundancy, and a little more if you want to be able to read the redundant copy (a handy feature indeed).
You’re not charged for sending data to Microsoft Azure, but you’re billed for pulling it out – about 2.40 (US currency) per 25GB. That makes it a great candidate for a place to store your backups, since hopefully you’re loading data to keep it safe more than you’re restoring it – although you should always test your restore operations from time to time.
There’s also something called “Storage Transactions”, and you’re billed .05 (US currency) for a million of these. Essentially this is like a disk request, but honestly, most folks don’t even include this in their calculations since it’s pretty trivial. You can add it in if you like on the calculator I mentioned.
The Logistics Part
Microsoft Azure uses Microsoft datacenters around the world. When you create your storage, you can select a datacenter in a Region that’s closest to you. The data in Azure Storage is replicated three times within that datacenter, and then (if you select that you want it to) all three copies are replicated to a geographically separate, but geopolitically similar datacenter – meaning that data in Asia won’t automatically replicate to Europe.
Whenever Microsoft builds a datacenter, they take pains to ensure it’s in a geologically stable area, that it has lots of access to power, water and fast bandwidth, and that they can build two of them in the same Region. In fact, there’s a whole group that does nothing but worry about, care, and feed datacenters – you can find their info (along with a lot more about the datacenters) here: http://www.globalfoundationservices.com/.
The Components and Features Part
Microsoft Azure is a platform made up of a lot of components you can use, like Virtual Machines, Web Sites, Hadoop Clusters, a Service Bus, and more. For the situation we’re talking about here, you only need one – Storage.
Even within Microsoft Azure’s Storage component, however, there are more parts. There are three types of storage:
- Blobs – Binary Large Objects (There are two kinds of these – Page and Block. We’ll use Page.)
- Tables – Key/Value Pair (KVP) storage
- Queues – Persisted message-passing for applications you write on Azure “PaaS”, or Platform as a Service
For this solution, you only need to concern yourself with the first type: Blobs. Blobs can store, well, anything that can be encoded in binary, from text files to Microsoft Word documents, and yes, backup files. Blobs are placed in Containers, which live inside the Storage Account I mentioned earlier. So just to quickly recap:
- Storage Account (holds) -> Container(s) (holds) -> Blob(s) (holds)-> Your object(s)
I’ll show you how to create all this in a moment.
- Main documentation for storage: http://azure.microsoft.com/en-us/documentation/services/storage/
- Microsoft Azure Storage Team Blog: http://blogs.msdn.com/b/windowsazurestorage/
Let’s take a look at the steps you need to take to create your backups and restore them. I won’t explain what you’re doing in each of these steps – that’s back in the Concepts section. This is just the step-by-step instructions to make it work. If you play along at home and it doesn’t work, go read the Concepts section and check the references there in case something has changed since this writing.
Step One: Create a Microsoft Azure Subscription
Your subscription is the billing instrument – so if different departments want different billing, they could create their own subscriptions. There are other options for separate billing tracking, but we’ll stick with the “each Subscription is its own billing” definition for now.
You have a few options for creating your subscription. You can start with the general, public-facing, list-price offer, there are free offerings, or you can use your MSDN subscription. In any case, the general process is:
- Go to https://azure.microsoft.com/en-us/ and log on with a Microsoft account
- Choose Sign-up for Windows Azure and follow the instructions to create a new account
- When your account is set up successfully, the Subscriptions page of the Account Center appears
- Choose Add a subscription
- On the Add a Subscription page, choose a subscription offer from the available list to create the subscription
And voila! You have an Azure Account and a Subscription. You can now start creating Virtual Machines, deploying applications, using Machine Learning, all kinds of features. For this article we’ll stick with Storage.
- If you want more information on the free option, start here: http://azure.microsoft.com/en-us/pricing/free-trial/
- Want a video of all this? Here you go: http://www.youtube.com/watch?v=PXMTxCEJUkU
If you are ready for production, you’ll need to start paying for the offering. You have a few options described above – and if you’re in a company, check to see if they are already using the Microsoft Azure platform. They may have an account you can use.
Step Two: Create a Storage Account and a Container
You now have an Azure Account and a Subscription. Next you need to set up a Storage Account. The general process to do that is:
- Open the Azure Portal (https://manage.windowsazure.com)
- Click the “plus-sign” (New) button at the bottom of the page, choose New, Data Services, Storage and then Quick Create
- Give the URL a unique name – prepending some code, your name, or some other random value is best
- Choose an area closest to your server as the Region. If you’ve made an Affinity Group (more on that here: http://social.technet.microsoft.com/wiki/contents/articles/7916.importance-of-windows-azure-affinity-groups.aspx ) you can choose that location instead
- Click Create
Here’s more info on that: http://azure.microsoft.com/en-us/documentation/articles/storage-create-storage-account/
Next, create a Container in your Storage Account. Staying in the Portal:
- Click Storage, the Storage Account name, then click the Containers tab
- From there, click Add
Enter a Container name, and select Private for the access. Unless, that is, you want everyone in the world to be able to look at your backup. Actually, there is a use-case for that – for instance, when you’re teaching a training class or to make a product catalog available, demos, that sort of thing.
OK – you’re all set. I haven’t forgotten about the Blob part – we’ll come to that in each option.
Option One: Use the BACKUP command in SQL Server 2012 and Higher
Starting in SQL Server 2012 SP1, CU2, you can use a simple T-SQL command to back up a database directly to a Blob. This has the advantage of full automation, integration with your current system, and portability within a script. And it’s actually pretty easy to do.
Before you open Management Studio, however, you’ll need some information from the Azure Portal. It’s important to keep these items secret, since they are your keys to be able to store items and get them back.
In the Portal screen above, click the Dashboard tab. There at the bottom of that screen you’ll see an item called Manage Access Keys. Click that, and note the Storage Account Name and Primary Access Key items. That’s what you’ll need in the next steps. Also note the URL of the Container – you’ll need that as well.
Now open SQL Server Management Studio (or your T-SQL environment of choice). Using the information above, type in the following code:
IF NOT EXISTS
(SELECT * FROM sys.credentials
WHERE credential_identity = 'mycredential')
CREATE CREDENTIAL mycredential WITH IDENTITY = 'YourStorageAccountName'
,SECRET = '<that really long storage access key you copied>' ;
If you name the
mycredential part to something a tad more descriptive, who would blame you? Not me.
Now you have the link to the security part of the connection. All that is left is to send the back up to the Container, and automatically create the Blob object for it.
BACKUP DATABASE pubs
/* Yes, I'm still using pubs because I'm old and so what use whatever you want don't be so judgmental it's not a good look for you */
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/pubs.bak'
WITH CREDENTIAL = 'mycredential'
,STATS = 5;
The BACKUP command is the one you already know and love – the magic is in the TO URL part. That’s where you put in the string for the URL, followed by the name of the backup, which Microsoft Azure will then convert to a Page Blob automatically for you.
And you’re done! You can look at the list of Blobs in the portal if you like.
Wait – let’s restore that. (I always get nervous about a backup until I check it. Remember that guy with the tapes?)
RESTORE DATABASE pubs FROM URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/pubs.bak'
WITH CREDENTIAL = 'mycredential'
,STATS = 5;
Whew! Worked. OK, on to the next thing you could do.
Wait – there are limitations and considerations here. For instance, you can’t back up more than a terabyte at a time, that sort of thing. Details on all that are here: http://msdn.microsoft.com/en-us/library/jj919148.aspx
There’s also a very robust way to manage these kinds of backups if you have SQL Server 2014, called Managed Backup. You can find more about that here: http://msdn.microsoft.com/en-us/library/dn449496.aspx
Option Two: Use PowerShell or AZCOPY
You could use PowerShell to do the same thing if you have the right version of SQL Server. In fact, I manage most of my servers using PowerShell, so this is usually the process I follow. Even within this option, you have two options. You can send commands from the SQL Server PowerShell Provider, which essentially does the same thing as the T-SQL above. For that matter, you could just send the T-SQL above using PowerShell. The process for setting up your credentials, performing the backup, and testing the restore are all here: http://msdn.microsoft.com/en-us/library/dn435916.aspx#credential
However, there’s another tool you can use for a slightly different use-case. Let’s say you want to use some of the options in your SQL Server Database Backup that aren’t available for the backup to URL feature. Or perhaps you want to back up not only SQL Server, but other files as well. Or perhaps you want to do heavy encryption of the backup so that if you accidentally let the storage keys out, the data would still be secured with a private, on-premises key.
In those cases you could back up the database (and anything else) to a directory on your local storage, encrypt it, compress it, combine it or whatever, and then copy the directory to Microsoft Azure.
You can do this with a tool called azcopy (which you can find here: http://aka.ms/AzCopy). This is a fantastic program that can copy an entire directory to a single Container object, has lots of options and features, including restarts, multiple copy streams and more. Think RoboCopy for the cloud.
After you’ve installed the tool from the link above, backup your database to disk as normal:
BACKUP DATABASE pubs
TO DISK = 'c:\temp\pubs.bak';
Now to back up the whole c:\temp\ directory to Azure, drop to a command prompt, and type:
"C:\Program Files (x86)\Microsoft SDKs\Windows Azure\AzCopy\azcopy.exe" c:\temp 'https://mystorageaccount.blob.core.windows.net/mycontainer/pubs.bak' /destkey:YourKeyGoesHere
Of course you’ll replace the URL with your container URL, and use your keys.
A full description of the azcopy tool is here: AzCopy – Uploading/Downloading files for Windows Azure Blobs
Option Three: Use the Free Microsoft Tool for other Versions of SQL Server
Wait – you did *not* buy the latest version of SQL Server? SHAME ON YOU. In this case, you can still back up to Microsoft Azure – with a free tool from Microsoft. You can find that tool here: http://www.microsoft.com/en-us/download/details.aspx?id=40740
I’m not going to cover a full installation of the product, it’s actually pretty straightforward. Make sure you download the documentation for this tool however, because there are a few clicks to go through to set up the features.
It’s an entirely separate tool, and it sets up a service on your machine for automation. It has some great features like supporting encryption and other rules. Once you set the program up, access its Configuration feature from the Start Menu or use the Windows Key to search for and start the ” SQL Server to Azure Configuration” program. When it starts, you’re given an initial Wizard to set up your paths and connections:
You’ll be guided through a set of questions about encryption and so on. Enter your data as requested, and then exit the configuration program.
From the Documentation:
When the installation completes, a service named Microsoft SQL Server Backup to Windows Azure Tool Service is created on the machine. This service runs the SQLBackup2Azure.exe to apply the configured rules such as backing up to Windows Azure Blob storage, compression, or encryption of the backup files.
Essentially you’ll be sending the files as a “stub” to the directory, and the files themselves are then stored in Azure. More details here: SQL Server Backup to Microsoft Azure Tool
Option Four: Use a Third-Party Product
Microsoft isn’t the only game in town if you have an older version of SQL Server or want features that don’t exist in the standard commands. Red Gate, among others, have tools that work with Azure. You can find their offering at: Red Gate Cloud Services: Azure backup from Red Gate
There are some added advantages here in that it’s all Wizard-driven, hosted in the cloud, works with Microsoft Azure Database (formerly SQL Azure), and works with other providers as well.