ABSTRACT
Businesses needs to run operations with zero disruption during normal operations, control periods and during high transaction processing as well. This is mandatory irrespective of the companies’ transformations, business process simplifications, mergers & acquisitions etc that happens. As many companies run their productions systems on highly available infrastructure with a Primary and Secondary/Standby instances in order to run Business As Usual during Planned/Unplanned Maintenance Activities. But the question in discussion how companies are consuming the Primary and Secondary/Standby equally even during normal Business Operations. Are they able to share the loads between the Primary and Secondary HW when one is Active and the other is passive. In this article we have addressed how companies can use their HW investments wisely, efficiently with exceptional performance without changing their Roles.
Problem statement
Business runs their transaction, expensive analytical read/write queries on the Primary Database Instance. These expensive queries are executed from various business units example Finance, Order Mgmt etc many such queries are CPU and memory intensive, thereby demands a high level of hardware resources. This has a significant performance impact during normal operations and as well during control periods where heavy transaction posting happens. Enterprise companies are liable to close their books during their month/year end closes on time for revenue purposes. Even though their production HW can scale these processes but when it reaches the HW threshold in terms of capacity it leads to disruptive operations. Many of these queries possess a threat in the form of performance and unable to run operations at scale.
As you can see in Figure: 1 all the queries are sent directly to the Primary HW and that puts a stress on the Primary DB, whereas the Secondary/Standby is idle as the database gets only replicated from the Primary.
Our solution
HPE IT has adopted a very unique approach by leveraging our HPE Superdome Flex which is a high end server computer in the market. We have our Primary and Secondary SD Flex that hosts HANA DB’s on which SAP S4 operates.
We have setup out of the box solution that has contributed in supporting HPE business operations to share the load equally between our Primary and Secondary HANA Databases that runs S4. By using this solution we demonstrated where HPE Business can run their business needy read only reports & queries that demands huge amount of memory and CPU resources, to run on the secondary/standby Superdome flex which is idle 99. % We were successfully able to divert such queries from the Primary SD Flex to this Secondary/standby SD Flex using a hint based routing. This has significantly reduced the resources on the Primary HW. This construct helped in the areas of Business Continuity to a great extent and presents a case of how efficiently we can use our HPE hardware investments. The advantage of this solution is that when a failover happens and the roles are changed between the two HW instances the queries are still diverted irrespective of the roles. This approach also sets the stage for “HPE Superdome to Follow the Sun Model Architecture”
As you can see in Figure: 2 all the expensive sql read queries and high memory intensive queries are diverted to the secondary HW and that releases the stress on the Primary DB. Both Primary / Secondary shared the loads and still have an active/active replication
Evidence the solution works
We did a comparison of the query execution and memory consumption prior to this enablement and post enablement on both of our Primary and Standby Superdome Flex.
Figure: 3 Observation on the Primary SD flex on utilization before the enablement of this solution. On March 2020 when we ran our month end close there were total of 23K sql executions and 25TB of memory consumption over a period of one week
Figure: 4 Observations on the Primary and Secondary/Standby SD flex on the utilization post enablement of our solution. These metrics were captured during the April which was our quarter end close, which is where HPE’s most business critical operation happens. During such control periods there will be enough loads. This clearly shows how Primary and Secondary/Standby SD Flex shares the load equally.
Competitive approaches
Many other RDBMS have this level of solution but they all lack in terms of performance yet.
References
https://blogs.sap.com/2017/06/22/making-use-of-an-activeactive-read-only-hana-database-in-s4-hana/
SAP HANA Administration: Active/Active Read Enabled - SAP HANA Academy – Video Tutorial