High Availability Disaster Recovery with SQL Server Always

High Availability & Disaster Recovery with SQL Server Always. On Availability Groups

Limitations of Log shipping for DR Cannot be used for Reporting unless put into standby mode. Each Database log ship need to be created and maintained separately. Copy and restore jobs. Alerting and monitoring. Lot of moving pieces to take care during disaster recovery testing. Log ship need to be recreated. 1

Always. On Availability Groups to Improve Redundancy Secondary Data Center Replica 4 A Replica A 3 A Backups Reports 2 Primary Data Center Reports A Replica 2 Replica 1

Client Failover using Virtual Name Availability Group Virtual Name allow applications to failover seamlessly on availability group failover Application reconnects using a virtual name after a failover to a secondary Server. A Server. C Server. B HRDB AGHR HRVNN Primary Secondary Application retry during failover -server HRVNN; -catalog HRDB 3 Connect to new primary once failover is complete and the virtual name is online

Readable Secondary SQLservr. exe Primary Secondary SQLservr. exe Instance. A Instance. B Database Log Synchronization DB 1 DB 2 Reports Readable secondary allow offloading read queries to secondary Close to real-time data, latency of log synchronization impact data freshness Backup ve DBCC Check. DB operations can be done on secondary 4

Active Secondary : Read-only Routing Application. Intent – A New Connection Property Used to get access to secondary Applicable when Secondary Replica set with ALLOW_CONNECTIONS =READ_ONLY or YES (ALL) Connection String Connect to primary replica Server=my. Listener; Database=DB 1; Connect directly to a secondary instance Server=my. Listener; Database=DB 1; Application. Intent = Read. Only Read-Only Routing Connection behavior optimized for automatic routing of read only applications to secondary You have to create the routes manually for this to work 5

Read-only Routing Server. B Server. A AGHR HRDB Secondary Primary HRVNN Reports OLTP -server HRVNN; -catalog HRDB 6 -server HRVNN; -catalog HRDB; Application. Intent = Read. Only Microsoft Confidential

Comparison of SQL Server High-Availability and Disaster-Recovery Solutions Technology Always. On Failover Clustering Instances Always. On Availability Groups Database Mirroring Zero data loss * ** *** Log Shipping Instance Redundancy Database Redundancy Auto Failover Readable Copy Multiple Secondaries 7 * Microsoft Confidential ****