Multi Site Clusters for SAP on SQL Systems: Recommendations & FAQ

Cameron_MSFT_SAP_PM · ‎Mar 13 2019

First published on MSDN on Mar 12, 2014

Multi Site Clusters for SAP on SQL Systems: Recommendations & FAQ

SQL Server 2012 incorporated a new HA/DR feature called AlwaysOn . This technology in combination with powerful new server hardware, Hyper-V virtualization , SSD disks and improvements in WAN links allow SAP customers to create highly resilient HA/DR solutions geographically distributed 100-300km apart without any data loss. These HA/DR solutions are considerably easier to implement and operate than previous generation HA/DR solution based on SAN storage replication. One key differentiator with modern AlwaysOn based HA/DR solutions is that the Basis & DBA team are able to execute a failover and failback without involvement of the SAN storage administrator or other teams.

Windows 2012 R2, SQL Server 2012/2014 and Hyper-V provide all the technology layers required to build a geocluster.

1. What is a Geographically Dispersed Cluster?

Geographically dispersed clusters or multi-site clusters are a single cluster with server nodes in different physical locations. SQL Server AlwaysOn is a DBMS software level replication technology that leverages Windows clustering. Unlike conventional SQL Server clusters there are no shared disks between cluster nodes. This is extensively discussed in a 12 part blog series on the SAP on SQL Server blogsite . AlwaysOn is well suited to geographically dispersed DR environments. Modern DR solutions are based on “Active/Active” data centers (where applications are active, running and accessed by users in both locations) or “Tick-Tock” (where a customer alternates between datacenters typically on a monthly or 2 monthly basis).

2. Why Build a Geographically Dispersed Cluster?

Line of Business applications such as SAP are often protected with a local high availability solution and a tertiary Disaster Recovery system.

Increasing number of SAP on SQL Server customers demand ultra-high availability of their systems. This is due to several reasons including:

a. massive increases in processing power on Intel hardware allowed customers to consolidate many SAP systems into a single instance and single client system. This improves reporting, standardizes business processes, master data and is simpler to manage

b. Multi-national companies have shutdown country or regional SAP instances and consolidated on a single global instance servicing global users based in many timezones. Servicing users across many timezones reduces or eliminates the possibility of planned downtime and increases the impact of unplanned downtime

c. Increasing percentage of business processes have been consolidated into SAP applications

d. SAP systems are now exposed via Internet or Intranet Portals to a larger number of internal and external customers. The business impact of downtime of a consolidated global single instance of SAP system is very significant for many organizations

Ultra-high availability requirements has led customers to build modern active geoclusters. These geocluster solutions are characterized by:

a. Synchronous replication is mandatory– zero data loss is allowed (RPO = 0)

b. Customers typically alternate between data centers every month (tick-tock) or run active/active data centers. This is done to test, prove and ensure DR capability

c. Distance between data centers can be 50km - 300kmwith full synchronous replication (RPO = 0)

d. SQL Server AlwaysOn is used to synchronously replicate databases

e. Hyper-V Replicais used to asynchronously replicate SAP application servers

f. Failover and failback times would be 2-10 minutes to fully restore service to end users

g. No SAN level tools are required, though SAN level replication can be used to replicate Interface file systems or other important file systems (SAP servers should not be used as file servers)

Older DR solutions typically:

a. require either manual software and configuration changes to stay consistent with production;

or use SAN level to replication virtual machines to keep software and configuration consistent with production

b. use Database level technologies to asynchronously replay transactions from Production on DR Databases;

or use SAN level replication to replicate Database disks from Production to DR

c. DR system is never tested or is only tested occasionally such as once per year, DR tests sometimes do not fully test interfaces and integration between SAP systems and other applications

d. Synchronous DR solutions are limited to a maximum of 20-50km typically, asynchronous systems risk large inconsistencies between SAP systems and between non-SAP systems (either internally managed systems or external systems controlled by other companies)

e. Failover and Failback times are lengthy (measured in hours) and require multiple teams (such as SAN team, network, Basis & DBA)

Older DR solutions are no longer recommended as they tend to be expensive, restrictive, difficult to test and can impact the performance of the production system. In addition a DR solution that is not regularly tested is highly unlikely to work without significant issues. SAN based replication systems for large Database intensive workloads can seriously impact performance when Synchronous replication exceeds 60-80km (about 60 miles).

3. What is Quorum and Why is it so Important for Geoclusters?

Quorum calculations are extremely important for geoclusters. Geoclusters require server and BASIS teams to have a good understanding of how quorum is calculated and the impact of a cluster losing quorum. Provided BASIS and Windows team members understand these concepts, the operation and maintenance of modern DR solutions is simple. SLA of 99.999% are possible with such solutions.

Quorum is extensively discussed in this blog:

SQL Server 2012 AlwaysOn – Part 2 - Quorum Detection

Windows 2012 R2 includes several new technology features that greatly benefit geocluster customers:

a. Dynamic Quorum

b. Preserve Quorum on failback

c. New Quorum model including Node + File Share Witness (FSW)

d. Tie breaker for 50% node split – a node can be nominated as low priority

An ideal scenario is where there are equal number of nodes in each data center and a File Share Witness in a third location with containing a File Share Witness with completely independent WAN links to each datacenter. This is only one suggestion, there are a vast number of valid configurations possible.

4. What is the Maximum Distance Between Datacenters?

This blog only discusses synchronous DR solutions, meaning in even of a disaster there will be no loss of committed transactions (RPO = 0 seconds).

The reason why this blog only covers synchronous replication is detailed in these two SAP Notes:

434645 - Point-in-time recovery: What must I be aware of?

434647 - Point-in-time recovery in an SAP system group

In summary these notes discuss the implications of Point-in-time restores on highly interconnected applications like modern SAP systems linked to multiple internal and external non-SAP applications. At a high level the notes warn that although it may be possible to recover one or more components of a modern business application landscape after a DR event, there is a risk that the application consistency is severely compromised. Because the application data model of SAP is extremely complex these application level inconsistencies can and have led to the loss of customer systems.

The distance limits for synchronous replication have many factors and inputs:

a. most importantly the link latency should be less than 1-3ms RTT (Round Trip Time) from application layer. A ping from one SQL AlwaysOn server to the DR AlwaysOn server should return in 1-3ms or less

b. bandwidth should be 2-10 megabits/sec(depending on transaction load) with no jitter

c. typically direct private fiber can achieve these specifications up to 300 kilometers or more

d. private direct point-to-point fiber (such as those offered by hosters) will have much lower latency a shared Telco provided links that have multiple hops between Telco exchanges

e. if Telco provided links are used, the precise exchange-to-exchange routing and distances must be provided by the Telco. Increased latency testing is required on shared switched Telco provided links

f. additional services such as MACSEC or Cisco OTV will add additional latency

g. Using “in server” internal SSD based devices for the transaction log is strongly recommended. This reduces the time to write transaction log entries on the hosts at each data center. FusionIO or LSI cards will lower the write times from 2-4 milliseconds per write to ~0.2ms or 200 microseconds and thereby lower the total latency of the SQL Server AlwaysOn Synchronous write process

Some mathematics:

C - Speed of light in a vacuum = ~300,000 km/sec

Refractive index of Fiber = ~1.467

Speed of light in Fiber = ~200,000 km/sec (C x Refractive Index)

300 kilometers / 200,000 kilometers per sec = 0.0015 second or 1.5 milliseconds bare link speed before network switching and application layer one way or 3 milliseconds Return Trip Time (RTT)

The overhead of the switches at each data center or two or three hops (routing from one Telephone Exchange to another) can induce latencies of 1-1.5ms.

Summary: Large busy SAP systems with high transaction throughput can be protected with SQL Server AlwaysOn up to distances of 100 to 300km with zero data loss (RPO = 0, Synchronous replication).

5. What is FORCE QUORUM and should I use it?

A well designed and operated Windows cluster with Dynamic Quorum should never lose quorum at any time other than when the entire cluster is shutdown. Windows cluster does include a manual quorum override. This feature should only be used by very experienced Windows engineers or when directed to do so by Microsoft Support.

Windows Cluster includes a feature called Force Quorum. There are several methods to Force Quorum: command line and registry

net start clussvc /fq

Review this article before attempting Force Quorum

WSFC Disaster Recovery through Forced Quorum (SQL Server)

6. What is PRESERVE QUROUM and should I use it?

Preserve Quorum is another cluster startup option that used to be required on Windows 2012 or earlier based clusters.

As of Windows 2012 R2 Preserve Quorum should no longer be required as the default behavior for Windows cluster is to preserve quorum

Prior to Windows 2012 R2 a scenario could occur as following:

a. Data Center 1 running production fails (due to electrical failure etc)

b. DR is invoked and Production is moved to Data Center 2

c. Quorum is achieved because the vote of the File Share Witness in the 3 ^rd location allows the cluster to count more than 50% of the votes

d. Due to operational or other reasons one or more of the remaining server nodes in Data Center 2 is shutdown. Provided Dynamic Quorum is used this will not offline the cluster (as when a node is shutdown one-by-one in a controlled manner the quorum will be rebalanced)

e. Data Center 1 is now started (for example power is restored) and now the servers in Data Center 1 vote and then recognize they have more than 50% of the votes and try to take ownership of Quorum. The exact impact of this depends on whether there is a Layer 2 span between data centers (single stretched VLAN) or not (the internal cluster service will have a duplicate IP if there is a Layer 2 span). Cluster services such as SQL Server will go offline or at best the cluster will be very unstable.

The scenario can be avoided by restarting the nodes in Data Center 1 with /PQ, however this is now the default behavior in Windows 2012 R2. It is therefore recommended to deploy Windows 2012 R2. The /PQ behavior respects the quorum status at the time of restarting additional nodes.

7. Is Automatic DR Failover Possible?

Automatic failover after an unplanned DR event is possible, however amongst the dozens of customers that have deployed modern DR solutions none of these customers have requested fully automated DR solutions. The reasons for this are:

a. invoking DR (which is part of an overall Business Continuity Plan – BCP) is a decision made by senior management and not by IT Departments. The decision is a “human” decision rather than an automated decision

b. automated DR returns the service to end users without allowing IT the opportunity to validate the last transactions in a system. Almost all organizations want to perform some level of validation before returning systems to service

Neither of these two objectives can be met if IT has implemented an automated DR solution that returns service to users. Customers will typically build Powershell scripts for automating alternating between data centers as previously mentioned - “tick-tock”.

8. What about SAN Level Replication?

SAN level replication was previously the only reliable mechanism for synchronizing applications between data centers.

Today most commercial DBMS have highly efficient and reliable technologies for replicating databases.

The combination of SQL Server AlwaysOn (for DB layer) and Hyper-V (for SAP application server layer) is cheaper, simpler to configure and operate and offers better performance over longer distances than older technologies including:

a. SAN level block replication

b. Host level software replication

c. SAN Fabric level or SAN virtualization tools

SQL Server AlwaysOn has the following advantages:

a. SQL Server AlwaysOn is able to avoid costly and performance compromising transmission of data by just sending metadata from a Production database to DR replicas. Block level SAN solutions might need to transmit tens of Gigabytes of data whereas SQL Server can send a command that might only be a few kilobytes

b. SAN based DR solutions require the participation and involvement of SAN administrators to design, setup, monitor and failover/failback. SQL AlwaysOn solutions can be fully implemented by the Basis team and/or DBA team

c. SQL Server AlwaysOn does not utilize shared disks or synchronized disks. The database engine on the Production and all HA/DR replicas is started and has read/write access to the SQL datafiles and underlying storage. If DR is invoked the DB engine simply performs a recovery on the database. The DB engine is started and warm, the storage is already attached and in read/write mode. This is always going to be faster than SAN solutions that require LUNs to be switched out of sync mode, presented and then starting the Database. The precise orchestration of these actions is difficult and a common source of failure on SAN based solutions

d. SQL Server Management Studio is a single management and configuration interface for the DB HA/DR solution. There is no dependency on involvement from SAN team.

e. SQL Server AlwaysOn allows read only access to replica copies of the SAP databases. Operations such as backups can be performed on the DR replica copies of a database without the purchase of additional expensive backup agents or tools. The Log Sequence Number (LSN) on a Primary Database and all AlwaysOn Replica(s) are identical meaning the transaction log backups from primary can be applied to a backup created on a Replica

Property

SQL AlwaysOn

SAN Replication

Note

Support Synchronous Replication on TCPIP Link

Yes

SAN level requires FC protocol for Sync usually, some IP support is possible in some cases

Support SynchronousReplication up to 300km on large busy database workloads

Yes

No

Large transaction delay occurs on SAN level solutions

Allows Read Only Access to Database Replica

Yes

No

Database engine is unable to read/write to SAN LUNs in sync mode

Allows Native SQL Backup on Database Replica

Yes

No

Replication Traffic Compressed

Yes

Compression ratios less efficient than SQL Server

Can Reduce Traffic by Sending Metadata Only

Yes

No

Single Management & Configuration Interface

Yes

No

Single Technology for both HA and DR

Yes

No

SAP Basis and/or DBA team Can Manage Entire Solution

Yes

No

*See appendix for additional details

Summary: SQL Server AlwaysOn HA/DR solution will deliver better performance, especially over longer distances at lower cost, lower complexity and is simpler to design, implement, monitor and activate when required. SAN level solutions require additional licensing and require participation of the SAN administrator.

9. Synchronous vs. Asynchronous AlwaysOn Replication?

As discussed earlier the focus of this blog is Synchronous DR solutions. There are very good reasons why modern highly interconnected business applications with multiple interfaces to external systems should be protected with a RPO = 0 (Recovery Point Objective).

If an Asynchronous is required (for example Production system in Europe and DR in USA) then AlwaysOn can be used in Asynchronous mode. Care must be taken to monitor the transaction delays on various applications and between company owned and controlled applications and external systems.

10. How are SAPMNT and other File Systems Replicated?

Windows 2012 R2 Hyper-V replica or other asynchronous Virtualization based replication technologies are recommended. Hyper-V replica is built into Windows 2012 and higher releases.

It is simple and easy to configure Windows 2012 R2 Hyper-V Replica:

Hyper-V Replica compresses replication traffic between Hyper-V hosts

It is recommended to select a replication interval of 5 minutes or 15 minutes. SAP application servers would generally not require 30 second replication interval.

SAP ABAP and SAP Java application servers do not store files that must be synchronously replicated with a DR system. SAP application servers contain:

a. SAP Kernel (executables) – these are critical but are updated infrequently

b. trace files such as dev_w0 – these are log files, contain no business data and are not critical

c. job logs – not critical to the operation of a SAP system

d. spool – TempSe object can be stored on the file system. These again are probably not critical, especially in the context of a major DR event

SAP application servers must not be used as file servers. A dedicated file server should be used for file based interfaces. ABAP programs should reference ABAP Logical File Names defined in transaction FILE . Logical File Names can be mapped to \\FileServerHost\Share

If required a dedicated file server can be replicated using Hyper-V Replica every 30 seconds.

If required Java systems can be re-synchronized with the database copy. In most cases this happens automatically at startup. For more information see

710663 - AS Java Bootstrap Synchronization

Make sure the bootstrap.properties file contains the property element.resynch

The three possible values for this property are as follows: detect, force & skip

Important Notes, Blogs, Whitepapers and Links

http://blogs.technet.com/b/virtualization/

http://blogs.technet.com/b/virtualization/archive/2013/10/22/what-s-new-in-windows-server-2012-...

http://technet.microsoft.com/en-us/library/hh831716.aspx

http://blogs.msdn.com/b/sqlalwayson/

Backup Strategies Note has been released. It is recommended to review Note 1878886 - Backup Strategies for SQL Server .

New functionality in SQL Server 2014 – Part 5 – Backup/Restore Enhancements details how to backup SQL Server from a DR site direct to Windows Azure Cloud. Backup to Windows Azure is available on SQL 2005 through to SQL 2014. There is no requirement to duplicate backup/restore infrastructure in a secondary data center, at least for SQL Server databases. New functionality in SQL Server 2014 – Part 5 – Backup/Restore Enhancements

Blogs from http://www.aidanfinn.com/

A Converged Networks Design For SMB 3.0 Storage & SMB Direct Live Migration

Windows Server 2012 R2 Hyper-V – Cross-Version Live Migration

Windows Server 2012 R2 Hyper-V – Live Migration Improvements (Compression and SMB 3.0)

Appendix

SAN level replication tools compared include:

EMC RecoverPoint/SRDF/VPLEX http://www.emc.com/collateral/hardware/technical-documentation/300-006-714-srdf-zero-data-loss-... (SRDF/S at 200km has serious performance impact on DB loads)

Hitachi TruCopy http://www.hds.com/products/storage-software/truecopy-remote-replication.html

HP CA www.hp.com/go/multisitereplication

NetApp Sync+Snap Mirror http://www.netapp.com/us/media/ds-2957.pdf

IBM PPRC/Global Mirror http://publib.boulder.ibm.com/infocenter/dsichelp/ds8000ic/index.jsp?topic=/com.ibm.storage.ssi... (Metro Mirror up to 300km in Synchronous mode supported, but performance on DBMS loads very poor)

Software based replication tools include: