SAP ASE HADR - A Case Study

[former_member185112]

A few months back, we implemented ASE HADR (Always-on option) in our environment. So I thought of sharing my experience (learning/do's/don't) through this blog to the wider community so that it can be useful for someone who is planning to implement HADR feature of SAP ASE.

(A) Background of our environment:

1. Application environment:
   
   
   
   1. Frontend - Non-SAP J2EE based front-end
   
   
   2. Backend - Sybase ASE
   
   
   
   


2. Source data servers: 3 ASE Servers with separate or common users with differing permission levels


3. Replication setup:
   
   
   
   1. All 3 types of replication to support OLAP and DR scenario(MSA, WarmStandBy, Table level)
   
   
   2. Cross Replication among these 3 data servers back and forth
   
   
   3. Replication server - SAP SRS
   
   
   
   

(B) Goal - Simplify the environment + have HA solution

We planned the project in two phases as mentioned below.

1. Phase#1 - Consolidated 3 ASEs into a single ASE - by this we avoided the cross replication among these ASEs


2. Phase#2 - Move from consolidated environment to ASE HADR

Advantages of this approach:

1. This approach gave us a single ASE with HA capabilities, which makes it really easy to support.


2. Eliminated the replication complexity by the consolidation of 3 ASE servers.


3. Cost reduction - by reducing the number of servers required in our landscape.

Thanks to SAP Engineering for taking away the SRS complexity by the new component called RMA!

(C) Some learning that might be useful for you:

Installation can be easily done by following the steps in the HADR User Guide that is available on the SAP website. In this blog, I will only be touching on the points that need extra care as a DBA during the implementation.

(C.1) Converting existing SMP ASE into ASE HADR:

ASE HADR also provides an option wherein you can convert existing stand-alone SMP ASE into HADR ASE - If you are using this approach, then, following points might be useful to you:

• Analyze suids from 1 to 6 in your primary ASE which you plan to convert into the Always-on option.


• SMP ASE has 2 default logins(sa, probe) with suid as 1 and 2 whereas ASE HADR comes with additional logins, so in total it has 6 system logins(sa, probe, tech_user, jstask, DR_main, DR_admin).


• Therefore you need to free up the suid from 3-6, in case they are used by some other login.


• If you don't do this exercise, there will be issues later on.


• As per SAP guidelines - in HADR setup, both ASEs must be identical in configuration, passwords, File System structure etc.

(C.2) Fresh Setup on new servers:

1. The installation process is simple, follow the guide and you will be all set. This installation will set up an ASE and a RepServer on the first host and same two major components on the 2nd host. There are other components as well like(RMA, Cockpit, saphostagent).


2. Is it possible to have a particular database out of this HADR setup? (let say you don't want to replicate one particular database in this HADr setup) - The answer is yes, this is possible. Just load the database both sides, but don't enable replication(Note - You need to disable the rep-agent for the databases that don't participate in HA immediately after bringing the db online otherwise this will cause the trouble for other databases when you enable the replication for other databases. And this scenario is only true when your source database has repagent activated for this non-participating database, otherwise, no changes required).


3. For the databases that participate in HA, doesn't require any manual intervention, RMA does all the required cleanup(secondary truncation point) and new setup that is required.


4. How to load/sync master database? - As a DBA this is the first question that comes to our mind, that how to load the master database from an existing source in HADR. So here is the approach that we used -
   
   
   
   1. BCP-OUT syslogins, sysprotects(if you have specific roles, permissions etc., if not then syslogins also is fine) from SOURCE and BCP-IN into the to-be Primary and Companion both servers separately
   
   
   2. Perform Auto-materialization (command can be found in the installation guide, sap_materialize auto...)
   
   
   3. Please note - replication for master database is different from user databases; in the master database, only sprocs/commands gets replicated, tables don't replicate; that is why we did the initial load of two tables and later on, login creation or any other change will replicate automatically.
   
   
   
   


5. How to re-materialize - this is how we did it:
   
   
   
   1. Disable the replication for the db in question(the db that require re-syncing)
   
   
   2. Enable the replication.
   
   
   3. Materialize the database - Auto or external.
   
   
   
   


6. Materialization choices -
   
   
   
   1. Automatic - performs the automatic materialization; Guide has examples for this.
   
   
   2. External - load the source dump both sides and finish the materialization
      
      
      
      1. Execute this command in RMA - sap_materialize external, <source_logical_hostname>, <target_logical_hostname>, <database>
      
      
      2. Perform the dbloads here
      
      
      3. Complete the materialization - sap_materialize finish, <source_logical_hostname>, <target_logical_hostname>, <database>
      
      
      
      
   
   
   
   


7. If you have more then 5 databases that participate in HADR - Consider increasing the Repserver memory. You can use sap_tune_rs command in RMA.


8. During any kind of maintenance, don't disable replication for master or cluster-id database, this might create problems. This doesn't mean that you cannot disable replication for master and clusterid database, you can, of course, do that. However please note in such scenarios, master must be the first one in the list because it contains information about all other databases.


9. Don't enable replication in RMA before bringing the database online


10. If you have opted for synchronous replication type for zero-data-loss with the server on two different datacenters then:
    
    
    
    1. Consider monitoring the ASE log file for the switch from the syn-to-async mode and frequency. This will give you an idea if there are any issues. You can analyze if a switch is happening too frequently. The Error number for this is - 9416.
    
    
    2. We have our datacenters 15 miles apart(primay and companion) and which is connected through high speed connection (500 mbps i guess). We have not seen these switches in our environment.
    
    
    
    

(D) Cut-over planning

"How much downtime is required for cutover/switchover?" This is the first question that you receive from the business. Below is the method that we used to minimize the downtime. The overall idea is that you load the database dump first while your OLTP is still running and then you take the downtime only for the transaction log dump and load -

1. Start few hours before actual downtime(you can decide it based on the size of databases and transaction log) and take the final database dump for all databases


2. Transfer the dumps(FTP) to new Primary and Companion. Load the databases on both ASEs(primary and companion)


3. Till this point source ASE is up and accessible to users - downtime does not start yet
   ****Start the Downtime now *****


4. Stop the ASE for transaction use


5. Take the final transaction log dump


6. Ftp to primary and companion


7. Load to both sites


8. Now enable the replication


9. Run materialization commands for each database


10. Release the new ASE to users


11. Update application reference to point to new ASE
    *****Your downtime ends here*****


12. So the saving of time is huge if you have large databases. Definitely, transaction-log dump size will be less or you can perform that during the low load time so that t-log sizes are even less.


13. You can also upgrade/implement the HA aware clients which give flexibility to automatically connect the application to the available server in case one goes down.

Please note - Enabling replication is a time-taking task, so if you have more databases then you need to reserve time for each database, you can measure this during your test env migration and based on that the production estimation can be given.

(E) Verdict and value proposition

• With 'synchronous' replication functionality you have Zero-Data-Loss HA capability which safeguards the business process. A great value addition!


• You can guarantee High Availability of your environment with this feature. Failover to the standby server is so instant(few seconds), your application will not even notice the switch. This is incredible!


• If you compare it with other HA options like OS and Storage based HA solutions, we find these are more expensive and require support from other experts like Storage team and Sys Admin if you want to perform a fail-over or for regular maintenance. However, with this feature in ASE, you as a DBA have the full control over the failover and don't need any support from other experts which in-turn saves the cost.


• Applications can be made intelligent enough to identify the availability of the server and automatically switch over to the available server which is a completely seamless experience.Following methods can be used to utilize the full potential of the product:
  
  
  
  • Implement/upgrade to HA-aware clients,
  
  
  • For old clients that use ini /interface file for connection, append the 2nd query line with standby server info.
  
  
  • For dumb clients - use floating IP. Fault Manager can be configured to automate the required changes at the time of failover (floating IP will be attached to the available server at the time of failover).
  
  
  
  


• Our environment is setup with 'synchronous' replication with two servers in two different data centers which are more than 15 miles apart from each other(this also serve as DR site).

Please note - above views are from our specific project and based on my experience. This can be different for your scenario based on different permutations and combinations. So please use them as guidelines and take the informed decision based on your specific scenario. Our environment is running for a couple of months now and so far we have not seen any issues.

Thanks to SAP engineering for making this great product!  Thanks!