Goran Žuri

BACKUP IS IMPORTANT. I always bug people how backup is super important. You should backup everything even the backup itself. This sounds somewhat crazy but backups get corrupted or lost. This is not one of the posts explaining why backup is important (just google it),

Prologue

I have a site with a fair amount of users that uses on-premises Microsoft SQL 2014 server. Server is hosted in on VM with NetApp storage behind it that is fault tolerant and also backed-up. But I want to make additional security measure of local db backup and using Azure Blob storage for off-site backup. Note: There is a rather big discussion on cloud security so to protect my backups I also want to encrypt it.

There are quite the few solutions to do this in the market, but most of them cost money or I should combine a couple of them to provide me with the same functionality. So I started building my own solution. In the end I want to have console app that will be full configurable and I will put it in windows Scheduler to run every night. The full source code can be found on GitHub or a compiled version can be downloaded from the URL.

Building the app

Like always lets open our Visual Studio, and make an empty Console App, I'll call mine simple "MSSQLbackup". I have made additional Settings.cs file just to hold properties of config file.

Let's connect to our SQL server.The easiest way I found is to use Microsoft.SqlServer.Management.Smo reference. This is official namespace containing core functions of SQL server, the reference can be tricky to find you can find it in your SQL server installation folder or if you don't have SQL server installed found a nuget package.

We will have a separate file for all databases on a single day. Basically we will store all backups in a folder, and later on ZIP it and encrypt. Let's create a folder, and in th same batch define that we want to go over all included databases on the server and call a function to backup the db.

Backup DB

Beckup functionality is also provided by the SQL SMO library

ZIP and encryption

Now that we have all the bak files in a single directory we want to ZIP and encrypt the whole directory. For all of that w don't want to reinvent the wheel and we'll use niffy lib SharpZipLib adn I can always recommend as a fast reliable solution for ZIP in c#.

Upload to Azure

All to do now is to upload the archive to Azure Storage account

Summary

After almost for 2 years we have decided to move our main SQL database from Azure SQL to our data center. The decision was made for security purposes mostly, we started to have sensitive customer data in our SQL and it was unacceptable that all that data was in the cloud, also German laws prohibit storing any customer information outside Germany. To be fair we had a pretty good experience with Azure SQL service. The biggest pro would be that you have all of the technologies at your disposal without any maintenance or admin work, but the big con was when some Azure services go down (and they do!) you are powerless. We had all kinds of failures as Traffic manager went down, Websites in multiple regions at once, etc. Also one thing to keep in mind if you are using Cloud services for important project have offline independent backup service, a lot can happen.

Migrating Azure SQL