Don’t Shoot Yourself in the Foot–Azure Hybrid Scenarios

I’m working with a great company named Opsgility to produce some training around using SQL Server and Azure Database. Some of the demos I’ve been producing are around using hybrid scenarios without Active Directory and/or a VPN connection to Azure. Some people may unfortunately encounter these scenrios and have to use them. I’m sorry!

I know this is a SQL Server focused blog and most of my readers aren’t AD or networking experts (heaven knows, I’m not). But let me state this—if you are building a hybrid environment follow the following two steps:

GET A $%^&ing VPN CONFIGURED BETWEEN YOUR ON-PREM SITE AND AZURE

This will make everything way easier—no firewalls (Except for the pesky windows one) to worry about, no opening SQL Server to the public internet, or any of that nonsense. You’ll be able to ping and connect to your servers just like they are in your data center. It costs $30/month roughly to have a network gateway, and my rate is way higher than that, and it took me most of the day to get log shipping working in this scenario yesterday.

USE ACTIVE DIRECTORY AND HAVE A SECOND DC IN AZURE

Not having AD makes everything really painful. In order to make log shipping work, I had to set both of my SQL Service accounts to the same user id and password—I don’t think that configuration is inherently insecure, but it just feels dirty. It also makes mirroring setup a lot more painful than it needs to be. If you are using Availability Groups of course, you’ll have AD, as it’s requirement of clustering, but some of these other scenarios (replication, log shipping, mirroring) don’t require it. Set it up anyway.

When you have AD and a VPN Azure feels like an extension of your data center. When you don’t have these things, everything feels like a painful kludge. Don’t build a Rube Goldberg machine.

How Much Do SQL Server Licenses Cost?

So I’m being a little bit of an SEO whore in this post because it took way to many google searches to get this data.

SQL Server 2016 Enterprise Edition—Core Licensing Only–$27, 495/4 cores, $13, 747.50 per additional 2 core packs.

SQL Server 2016 Standard Edition—Core licensing–$7171/4 cores, $3585.50 per additional 2 core packs,

Server plus CAL Licensing– $898/server (CALs are $170/named user)

SQL Server 2014 BI Edition—Server Plus CAL Licensing Only–$8592/server (CALs are $170/named user)

BI Edition is dead with 2016. I also updated the versions here, but the price hasn’t changed for 2016, and remember you get a lot of cool enterprise features in standard edition.

 

Managed Backup to Azure—Configuring on Your Server

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.

 

 

 

 

 

 

%d bloggers like this: