Geo-Replication

Good Failover strategy includes replicating your Database to a Partner Geography Server so that if there is an outage in the Primary Data Center Region, the Secondary Database Server can then become Primary to provide High Availability Support for the Individual Database. The Geo-Replication applies to the Database and allows at least 1 and maximum 4 Secondary databases where the Database can be replicated.

Failover- Groups

The Failover Groups are a set of Servers which form a sort of Cluster and provide a Frontend Cluster for Client Applications to connect. (Actually a DNS based CNAME).

The Failover Groups allow partner SQL Server instances to become members of the Group to allow Availability concerns of the BCP/DR objectives of the Organization.

This Failover Group setup is very similar to RA-GRS sku type of Storage Accounts. While the Primary is Read-Write Server, the Secondary is Read-Only Server which keeps the Read-only copy of Database always accessible. The below are usually Failover Group Endpoints for your Client Applications to connect.

• Primary (Read-Write) = trpserver.database.windows.net
• Secondary (Read-only) = trpserver.secondary.database.windows.net

Word of Caution! Both the above solutions don't protect the Data if deleted by an Application or a User with right Credentials. This includes deletion, Table Deletion, etc., because these are also Replicated to the Secondary Database / Server.

Database Backups - Point in Time Backups (PITR) / Long Time Retention Backups (LTR)

Because database backups protect data from accidental corruption or deletion, they're an essential part of any business continuity and disaster recovery strategy. There are 2 types of Backups provided by Azure SQL

• PITR (Point in Time Restore). The PITR runs every time since the time the Database is created. The below PowerShell Command retrieves the PITR Backup Status

Get-AzSqlDatabaseRestorePoint `
    -ResourceGroupName MyResourceGroup `
    -DatabaseName sql-erp-db `
    -ServerName $ServerName 

ResourceGroupName        : learn-85cf52f1-d21d-4da8-9c0c-b6c30eafd2ef
 ServerName               : srpserver-2393482
 DatabaseName             : sql-erp-db
 Location                 : East US
 RestorePointType         : CONTINUOUS
 RestorePointCreationDate : 
 EarliestRestoreDate      : 12/27/2020 12:58:13 PM
 RestorePointLabel        :

Below is the default PITR backup policy which keeps the PITR backups for 7 days with maximum of 35 days.

• Long Time Backups can be setup for a SQL Server (though applicable to each Database individually, the Setting can be done only through SQL Server) to keep the Backups beyond 7-35 days of default PITR backups.

The Long Time Backups can be setup from this panel /section in SQL Server --> Manage Backups. There are 3 types of LTR backups (Weekly, Monthly & Yearly). Below table highlights how the frequency, minimum and maximum retention can be setup

The Example screenshot below sets up Weekly LTR backups to be kept for 8 weeks, Monthly Backups for 12 Months and Yearly LTR Backups to be kept for 5 Years

Geo-replication and long-term backup retention

When using active geo-replication or failover groups as business continuity solution, the Solution Architect should prepare for eventual failovers and configure the same LTR policy on the secondary database or instance. Even if keeping the same LTR Policy, the LTR storage cost won't anything as backups are not taken from the secondary Servers. The backups are only created when the secondary becomes primary. It ensures non-interrupted generation of the LTR backups when the failover is triggered and the primary moves to the secondary region.

Note

Once the original primary database is available back from an outage that caused the failover, it will become a new secondary. So, the backup creation will not resume and the existing LTR policy will not take effect until it becomes the primary again.

Happy Reading ! See you next time ! till then stay safe and use Face Masks till the Corona versions are still oncoming !