SQL Database and its three replicas
You might have heard that SQL Database (formally SQL Azure) is a scalable and highly durable database service on the cloud and there’re multiple replicas automatically provisioned when we create a database. It’s true that there will be three replicas store for each database. This is in fact purely for HA purpose in case one of the machine hosting the SQL Database service goes down.
Customers are transparent and inaccessible to these three replicas. In another word, if we accidentally delete one of the table (or entire database), it’s really gone ! (Luckily it’s only a demo database)
I had experienced that before and tried to contact the Azure Support. There’s no way to revive our deleted database anymore.
Design and archive it our own
As a cloud architect, we should really be aware of this. In fact, for many projects I’ve worked on of the last three years, the archival or backup mechanism has been always be part of my design. This is because at that time, there’s no built-in automated backup in SQL Database for customers.
How I did that?
V1. sqlcmd and bcp + Worker Role = Automated Backup
At earlier day, we used sqlcmd to backup the script and bcp to backup the data. This may sound a bit surprising for some of you and that’s really what we can do at that time. We created a worker role and ran in schedule (typically daily) to perform backup and push the data to Azure Blob Storage.
The output is 1 .tsql file and copies of .dat file per database table.
V2. bacpac + Worker Role = Automated Backup
Later, Microsoft introduced bacpac as part of import and export solution for both SQL Server and SQL Azure. The output of this technique is .bacpac file which is similar to .bak file as we familiar of.
There was also an UI in management portal that allow us to export and import the database to Azure Storage on-demand basis, but still lack of automated way. Alternately, there’s exe (command line interface) that eventually calls WCF service to perform backup. We twisted our design from sqlcmd + bcp to just simply use the command line.
Now, it’s built-in supported!
Finally, I notice that it’s built-in provided in management portal. SQL Database – Configuration. And you can find it by choosing Export Status to Automatic.
You can further specify the frequency of the backup on every N days. You can also specify the retention to only keep the last N days (so that your storage account won’t grow too big over the time).
After the configuration, you can see that the bacpac is finally pushed to my storage account.