Data Management for Reliability
Azure SQL Database
SQL Database automatically performs a combination of full database backups weekly, differential database backups hourly, and transaction log backups every five - 10 minutes to protect your business from data loss. Use point-in-time restore to restore a database to an earlier time. For more information, see:
SQL Server on VMs
For SQL Server running on VMs, there are two options: traditional backups and log shipping. Traditional backups enables you to restore to a specific point in time, but the recovery process is slow. Restoring traditional backups requires starting with an initial full backup, and then applying any backups taken after that. The second option is to configure a log shipping session to delay the restore of log backups (for example, by two hours). This provides a window to recover from errors made on the primary.
Azure Cosmos DB
Azure Cosmos DB automatically takes backups at regular intervals. Backups are stored separately in another storage service, and those backups are globally replicated for resiliency against regional disasters. If you accidentally delete your database or collection, you can file a support ticket or call Azure support to restore the data from the last automatic backup. For more information, see Automatic online backup and restore with Azure Cosmos DB.
Azure Database for MySQL, Azure Database for PostgreSQL
When using Azure Database for MySQL or Azure Database for PostgreSQL, the database service automatically makes a backup of the service every five minutes. Using this automatic backup feature you may restore the server and all its databases into a new server to an earlier point-in-time. For more information, see:
Distribute data geographically
Azure SQL Database provides two types of recovery: geo-restore and active geo-replication.
Geo-restore is also available with Basic, Standard, and Premium databases. It provides the default recovery option when the database is unavailable because of an incident in the region where your database is hosted. Similar to point-in-time restore, geo-restore relies on database backups in geo-redundant Azure storage. It restores from the geo-replicated backup copy, and therefore is resilient to the storage outages in the primary region. For more information, see Restore an Azure SQL Database or failover to a secondary.
Active geo-replication is available for all database tiers. It’s designed for applications that have more aggressive recovery requirements than geo-restore can offer. Using active geo-replication, you can create up to four readable secondaries on servers in different regions. You can initiate failover to any of the secondaries. In addition, active geo-replication can be used to support the application upgrade or relocation scenarios, as well as load balancing for read-only workloads. For details, see Configure active geo-replication for Azure SQL Database and initiate failover. Refer to Designing globally available services using Azure SQL Database and Managing rolling upgrades of cloud applications by using SQL Database active geo-replication for details on how to design and implement applications and applications upgrade without downtime.
SQL Server on Azure Virtual Machines
A variety of options are available for recovery and high availability for SQL Server 2012 (and later) running in Azure Virtual Machines. For more information, see High availability and disaster recovery for SQL Server in Azure Virtual Machines.
SQL Server Always On Availability Groups across regions
Alternatively, you can use SQL Always On Availability Groups for high availability by creating a single availability group that includes the SQL Server instances in both regions.
As an example, Multi-region N-tier application reference architecture shows a set of practices for running an N-tier application in multiple Azure regions to achieve availability and a robust disaster recovery infrastructure. It uses a SQL Server Always On Availability Group and Azure Traffic Manager.
Azure Storage provides data resiliency through automated replicas. However, this does not prevent application code or users from corrupting data, whether accidentally or maliciously. Maintaining data fidelity in the face of application or user error requires more advanced techniques, such as copying the data to a secondary storage location with an audit log.
Block blobs. Create a point-in-time snapshot of each block blob. For more information, see Creating a Snapshot of a Blob. For each snapshot, you are only charged for the storage required to store the differences within the blob since the last snapshot state. The snapshots are dependent on the existence of the original blob they are based on, so a copy operation to another blob or even another storage account is advisable. This ensures that backup data is properly protected against accidental deletion. You can use AzCopy or Azure PowerShell to copy the blobs to another storage account.
Files. Use share snapshots, or use AzCopy or PowerShell to copy your files to another storage account.
Tables. Use AzCopy to export the table data into another storage account in another region.
Samples related to storage resiliency are here. The scripts perform these tasks:
- ARM template to deploy a storage account and blob container.
- Copies file into the blob container.
- Creates the blob snapshot.
- Creates a share snapshot.
- Calls AzCopy for table storage