Anything that simplifies backup is something that I’m a big fan of—as a DBA it’s your job to make sure your company can restore its critical data in the hardware failure, user error, or natural disaster. So making the process simple and easy is a good thing right? Another thing that is important is sending your backups to a second location—in the event of natural disaster, or storage failure (please don’t tell me you are backing up your databases to the same SAN that they live on) it’s nice to have a DR site. In the old days, we would backup to tape and then ship the tapes off to secure location. In doing so we opened the potential for a lot more people to potential steal our data, and managing tapes is a nightmare. Many companies now are taking advantages of the cloud to archive their backups, or in some cases be their primary backup location. Both Amazon and Microsoft have really good (and really cheap—as low as a penny a gigabyte per month) options for doing this. As you might expect, Microsoft’s solutions are tightly integrated with SQL Server, and pretty easy to use.
In SQL Server 2014, Microsoft introduced two features that bring this cloud backup solution to the fore—backup encryption (independent of transparent data encryption (TDE)) and managed backup to Azure. Managed backup to Azure is also know internally, smart_admin (that’s the schema that owns the object which run this feature). You simply specify a retention period (in days) for your backups, supply a credential, and SQL Server does the rest. There are a few limits—currently system databases, and there is a maximum size of 1 terabyte, and your databases have to be in full or bulk-logged recovery modes. Aside from that, SQL Server determines the frequency and the type of the backups based on the workload of your database. You can find the full details of how SQL makes these decisions here.
You’ll need a couple of things to configure managed backup:
- A SQL Server Instance with databases (if you don’t have those, why are you here J ?)
- A SQL Credential
- An Azure storage account
- An Azure Management
The first thing you’ll need to do is create your credential—this is where you’ll need your Azure publishing profile. You’ll need to work with whomever manages your Azure account to get permissions to get permissions for the this task, but to do it we’ll break out PowerShell (Microsoft Azure PowerShell which you’ll need to download here. Run the command get-azurepublishsettingsfile
From there, Azure will launch a webpage (and may prompt you to login with your credentials). In my case, I was logged into my account, so my file was automatically downloaded. Now to Management Studio. Note—if you want to encrypt your database, you’ll want to create a master key and a certificate for database backup. Please do me favor, back these up some place safe, immediately. Better yet—put them in a container in your Azure storage account. You won’t be able to restore your encrypted backups without these, so it’s pretty important.
Ok—so now you have a publish settings file and a certificate. So you are to set up Managed Backup. First find it, expand the management pane in SSMS and right click on Managed Backup and select configure.
You’ll notice that this reports that you had selected an invalid credential, so go ahead and click “Create”. Another screen will popup here
Browse to where you downloaded your publishsettings file. Next click the drop down box and select the storage account which you want to use for this. The drop down will enumerate any Azure blob storage accounts to which your account has access. The naming convention for this account:container name—you container name will be $SERVERNAME-$INSTANCENAME. Go ahead and click create.
Now you can click the check to box to “Enable Managed Backup” and the “Encrypt Backup” checkbox. Select the certificate you created above, and choose your encryption, I’m using AES 256 here.
Now, that we have managed backup enabled, we need to setup a retention period. This will be a T-SQL operation, and we have the option to either set retention at the instance level, or at the individual database.
You’ll need the name of your certificate and the name of your credential. Your backups are now officially starting. One last thing—configure monitoring, so you can get notified when your backups are taking place.
That procedure with that specific input and parameter will setup your notifications and you are now good to go. My backup started.
