Ensuring your data is safe with Azure SQL Database

Critical to any business is its data. Data is king. So it’s vitally important to ensure you have a plan to restore your data should anything untoward happen to it. From accidental user error, to application error, to an outage, fire or flood. There are many ways in which data can be lost or its integrity compromised.

So it’s important to ensure you have regular backups, and that you perform regular restores of that data. After all, you don’t want to find out after you have lost all your data, that there is a problem with your backup making it impossible to restore it.

If you are using Azure SQL Database (ASD) for your data storage, there are a range of options available to you. I won’t go through all of them, as there are plenty of articles online already. I’ll just describe the options I have chosen for our particular application and business needs.

ASD provides several business continuity features including automated backups and optional database replication. Each type of business continuity feature has different characteristics for estimated recovery time (ERT) and potential data loss for recent transactions. Understanding these ensures you can make an informed decision with regards to the needs of the business.

The business continuity needs of the business will depend on several factors including:

- Is the data mission critical?
- Is the data bound to an SLA? Will the loss of data result in financial liability?
- Does the data have a low rate of change? (the data changes infrequently such that losing data for a certain period of time is acceptable)
- is the data cost sensitive?

In conjunction with the estimated recovery time (EST) mentioned earlier, there are two other important factors to understand when considering the business continuity of your business.

- Recovery Time Objective (RTO) is the maximum acceptable time before the application fully recovers from a disruptive event
- Recovery Point Objective (RPO) is the maximum amount of recent data updates (time interval) the application can tolerate losing when recovering after the disruptive event

ASD automatically creates database backups at no additional charge. They occur straight out the box. You don’t need to do anything to make them happen. Database backups are an essential part of any business continuity plan because they protect your data from accidental corruption or deletion. If you need to keep your backups longer than the default storage period, then you can configure a long-term backup retention policy. The default retention policy on the Basic tier is 7 days, whilst for the Standard and Premium tiers it is 35 days.

ASD creates full, differential and transaction log backups. The transaction log backups generally occur every 5–10 minutes, with the frequency based on the performance level and amount of database activity. Transaction log backups in conjunction with full or differential backups, allow you to restore to a specific point-in-time to the same server that hosts the database.

In addition to getting automated backups, I then configured Geo-Replication. Active Geo-Replication (AGR) enables you to configure readable secondary databases in the same or different data centre locations (or regions). Secondary databases are available for querying and for fail-over in the case of a data centre outage, or in the event of being unable to connect to the primary database. When you configure a secondary database, you give it a name and login credentials, as you would with any other database. This allows you to connect to a secondary database in exactly the same way as you would the primary (or any other) ASD. After a fail-over, the new primary has a different connection endpoint.

So in the event of a disruptive event that causes the outage of the data centre that hosts your ASD, you can automatically fail-over to a secondary database in a completely separate region. You are able to configure up to four of these secondary databases. You can initiate fail-over to any one of these secondary databases. Once fail-over is activated to one of your secondary databases, this then becomes the new primary database. All other linked secondary databases automatically link to the new primary. You can configure automatic fail-over or manual fail-over, whichever best suits the needs of the application and the business.

I haven’t even scratched the surface of ASD and its business continuity features. I hope to return to this topic in a future article. As I’ve said before, everything about Azure is fantastically easy to use and configure (either through the Azure portal, Azure Powershell or the REST API), and this is certainly true with regard to its database features. If your data is important to you, then check out the features in Azure SQL Database.

If this article was helpful to you, please do hit the 💚 button below. Thanks!

A father, cyclist, vegetarian, atheist, geek and multiple award winning technical author. Loves real ale, fine wine and good music. All round decent chap.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store