Overview
Ok so you have successfully deployed to the AWS/Azure Cloud or On-Premise but what happens if the data centre goes down or the whole region is down?
What about if you just want to apply a patch and can’t afford long outages, or perhaps your SQL Server is significantly slow today and your initial investigation suggests a physical host issue.
How can you avoid major downtime for the above scenarios or at least give yourself some breathing space to investigate the issue?
DBInsight’s Always On service is the answer!
DBInsight’s Always On service designs and implements a SQL Server high availability and disaster recovery solution optimised for the AWS/Azure Cloud, On-Premise and hybridOn-Premise/Cloud environments.
Depending on your business objectives and budget we can seamlessly:
- Switch to another server in the local data centre and minimise downtime for patching or provide an alternative environment while investigating physical host issues.
- Switch to another server in another data centre in the same region for local disaster recovery.
- Switch to another server in a different region to provide regional disaster recovery.
DBInsight’s high availability and disaster solutions cover the following editions of SQL Server :-
-
SQL Server Web Edition
– DBInsight’s custom log shipping solution allows failover and failback capabilities for SQL Server Web Edition. -
Standard Edition
– Basic Availability Groups, two servers in separate Availability Zones and/or Regions. (Note SQL Server 2016 SP1 required) -
Enterprise Edition
– Availability Groups, two or more servers in separate Availability Zones and/or Regions.
Call 1300 914 460 or send us an email to find out more about DBInsight’s Always On Services.
HADR Solution Summary
| HADR Solution | Description/Protection | Potential Data Loss |
|---|---|---|
| Custom Log Shipping | SQL Server Web Edition
Warm standby database Utilises standard transaction log backup job Failover and failback option available Simple Powershell management commands Protect against VM or AZ or Region failure |
5 to 15 minutes of data loss on a forced failover 0 data loss for a controlled failover (both SQL VMs are operation) Mitigation - Monitoring to alert log shipping failures |
| Basic Availability Group (BAG) – Same Region | SQL Server Standard Edition
Availability Group robustness and ease of failover Synchronous data transfer with Automatic failover Protect against VM or AZ failure |
0 data loss if BAG is in sync Mitigation - Monitoring to alert on log send backlogand AG health |
| Basic Availability Group (BAG) – Separate Regions | SQL Server Standard Edition
Asynchronous data transfer with manual failover Protect against Region failure |
Seconds to minutes data loss Mitigation - Monitoring to alert on log send backlogand AG health |
| Basic Availability Group BAG – On Premise Hybrid to Cloud | SQL Server Standard Edition
Asynchronous data transfer with manual failover Protect against On-Premise failure |
Seconds to minutes data loss Mitigation - Monitoring to alert on log send backlogand AG health |
| Availability Group - local HA and remote DR replicas | SQL Server Enterprise Edition
Synchronous data transfer with Automatic failover for SQL VMs in local region. Read only replica in local region for reporting Asynchronous data transfer with manual failover for remote DR replica Protect against VM or AZ failure in local Region Protect against Region failure |
0 data loss if local AG is in sync Seconds to minutes data loss for remote DR replica Mitigation - Monitoring to alert on log send backlog and AG health |
SQL Server Web Edition HADR Solution
- Simplified custom log shipping solution that includes 1 SQL Agent job to backup transaction logs and another SQL Agent job to restore/rollforward transaction log backups onto the secondary. Transaction log backups can be configured for 5 minute frequency for provide a granular recovery point.
- Transaction log file share can be local or remote or on S3 storage.
- Controlled failover and failback between primary and secondary.
- Simple Forced failover if the Primary server is permanently disabled.
- Monitored by DBInsight’s SQL Server monitoring service.
SQL Server Standard Edition HADR Solutions
- Basic Availability Group (BAG) deployed for automatic failover between twoAWS availability zones in the same region.
- A Fileshare Witness or 3rd server in the cluster in the availability zone 3 (3rd data centre) is configured as tie breaker for the Cluster.
- Multiple BAGs can be deployed with one Availability Group Listener, with a SQL Agent job ensuring all databases failover with the designated primary database or multiple Availability Group Listeners deployed to load balance databases over two SQL Servers.
- Use AWS Directory Service for domain management or deploy Domain Controllers in each availability zone.
- Backups can be automatically copied to S3 storage and S3 Life Cycle Management used to maintain backup file retention.
- Monitored by DBInsight’s SQL Server monitoring server.
- Basic Availability Group (BAG) deployed for automatic failover between two Availability Zone in the same region.
- The Cloud Witness service is used as tie-breaker Fileshare Witness.
- Multiple BAGs can be deployed with one ILB/Availability Group Listener, with a SQL Agent job ensuring all databases failover with the designated the primary database or multiple ILBs/Availability Group Listeners deployed to load balance databases over two SQL Servers. Note a Load Balancer provides a “floating” IP address for the AG listener that allows quicker failover and reconnection.
- Use AAD Domain Services for domain management or deploy Domain Controllers in each availability zone.
- SQL Server Backups can be directly written to Azure Blob Storage using the Backup to URL feature.
- Monitored by DBInsight’s SQL Server monitoring service.
- Basic Availability Group (BAG) deployed for manual failover between two Regions. i.e. protects against total Region failure.
- The Cloud Witness service is used as tie-breaker Fileshare Witness.
- Multiple BAGs can be deployed with one ILB/Availability Group Listener, with a SQL Agent job ensuring all databases failover with the designated the primary database or multiple ILBs/Availability Group Listeners deployed to load balance databases over two SQL Servers. Note a Load Balancer provides a “floating” IP address for the AG listener that allows quicker failover and reconnection.
- Use AAD Domain Services for domain management or deploy Domain Controllers in each Region.
- VNET to VNET connection is required.
- SQL Server Backups can be directly written to Azure Blob Storage using the Backup to URL feature.
- Monitored by DBInsight’s SQL Server monitoring service.
- Basic Availability Group (BAG) deployed for manual failover between On-Premise and Azure. This provides disaster recovery capabilities for the On-Premise SQL Server.
- A Fileshare Witness created in the On-Premise data centre as a tie breaker.
- Multiple BAGs can be deployed with one ILB/Availability Group Listener, with a SQL Agent job ensuring all databases failover with the designated primary database. On failover to Azure, the ILB redirects requests to the Availability Group listener’s IP for the Azure subnet.
- A domain controller is required in the On-Premise data centre and Azure data centre.
- SQL Server backups are written to local disk in On-Premise, on failover to Azure SQL Server backups are written Azure Blob Storageusing the Backup to URL feature
- Monitored by DBInsight’s SQL Server monitoring service.
SQL Server Enterprise Edition HADR Solutions
- Availability Group (AG) deployed for automatic failover between two AWS availability zones in the same region and manual DR failover a separate Region.
- Inter-region VPC peering is required to connect separate region’s VPCs.
- Multiple AGs and AG Listeners can be deployed, in the above example one for transactional databases (primary server 1) and another for reporting databases (primary on server 2.
- SSRS Scale-out EE feature and AG2 provides a SSRS failover capability. AG1 databases on Server 2 are read only and can be used reporting.
- Use AWS Directory Service for domain management or deploy Domain Controllers in each availability zone and Region.
- Backups can be automatically copied to S3 storage and S3 Life Cycle Management used to maintain backup file retention.
- Similar scenario can be created in Azure using internal load balancers (in each region) mapping to the AG Listeners and a VNET to VNET connection.
- Monitored by DBInsight’s SQL Server monitoring service.