Online Tutorials & Training Materials |
Register Login

Synchronous Mirroring- SQL Server Database Mirroring and SAP Applications

|| || 0

Synchronous Mirroring- SQL Server Database Mirroring and SAP Applications

For the process of synchronous mirroring, the primary server confirms the transaction to the SAP application post receiving the acknowledgement from the mirrored server. This configuration is primarily lays down a two-phased transactional consistency. Rather than having only a single copy of the data present on shared storage, one see two copies which are consistent and separate. By this database mirroring configuration, the user expects a performance impact. In this manner, the SAP ABAP application server based logic is executed along with asynchronous database updates, it is fine to expect that the performance will mainly affect the SAP update processes and eventual SAP batch processes.

The dialog part of the SAP business logic will not be impacted much, however the Java-based SAP logic will be impacted. The performance impact will be due to the fact that the principle server pauses for an acknowledgement a commit issued by SAP logic until the database mirror confirms that it continued the transaction log buffer sent. Since the transport layer for these packages is quite normal TCP/IP (as there are no other protocols supported), all the delays caused on waiting for the mirror acknowledge are properly calculated out of the transmission of the packages along with the time taken for persisting the transaction Log records on the mirror side.

Both the distance and network bandwidth are major decisive factors for this more variable part of the delay. A very long distance or a very small network bandwidth can majorly affect the scalability of a SAP system. Very high database response times for the process of upgradation of the SAP ABAP processing generally lead to severe blocking lock scenarios around tables which have been storing number ranges or other important resources. Hence the distance for covering with database mirroring is required to be moderate, for a SAP OLTP kind of workload which is less than 100 miles with experience collected. The average output of a network link which is between the principal and mirror server should ideally sustain 5-10MB/sec specifically for medium to large systems and 10-20MB/sec for high-end systems.

Synchronous Mirroring with Failover

All the automatic failover requires adding a witness instance of SQL Server, which can also be a Microsoft SQL Server 2005 Express Edition or can also be any other Edition of SQL Server in automatic failover clusters, the witness server provides the quorum logic necessary in which two of the three servers have to agree during a failover. Incase the database on the principal server goes down, then all the witness along with the mirrored servers make a quorum and then decide to bring the mirrored database on the mirror server online and finally redirect all the clients to the mirrored server. All the SAP components are not able to leverage an automatic failover by database mirroring. This is because the client re-direct functionally is deployed in ADO.NET and in the new SQL Server Native Access Client (SNAC). SNAC is covering ODBC and OLE DB. Since SAP executables of the SAP ABAP stack are utilizing SNAC when connected to SQL Server 2005, the SAP ABAP based components can utilize and leverage the automatic failover capabilities of database mirroring. However, the JDBC drivers utilized by SAP earlier, did not support this automatic client re-direct feature. Therefore, the SAP components based on the SAP Java stack was unable to use automatic failover capabilities and required to rely on the manual failover. All of this was altered with SAP using the Microsoft JDBC driver 1.2 for SQL Server 2005. This driver is utilized by default since releasing Netweaver 7.0 SR3. This driver can be used for all Netweaver 7.0 versions and Netweaver 2004 versions. Please refer to the OSS doc #1109274 and #639702 for more details. By using the Microsoft JDBC driver Failover in Database Mirroring is possible and can be fully supported also.

The user has to be careful that the automatic failover is slightly more complicated than the failover logics of MSCS. On the contrary to MSCS, database mirroring should consider the mirroring state in for avoiding situations where the mirror becomes productive, however it does not receive all committed transactions from the former principal. For instance, the database mirroring may fail because of some storage problems and can even get into a suspended mode, however the witness still receives responses on its communication path to the principal. This refers to the fact that the witness does not recognize an outage of the principal server however only knows mirroring is in a suspended mode because of some failure conditions. Incase, the database instance is either rebooted few minutes later, or if the SQL Server instance is shut down few later, then the witness will detect that the mirrored database on the principal server is unavailable. However, no automatic failover takes place as the database mirroring is in a suspended state some minutes before. In this scenario, the user has to perform the failover manually with the possible risk that there were committed transactions which have been executed on the principal server post stopping the mirroring because of failure conditions. The distance between the two database servers should be limited, with this mode. From a workload scenario, the distances of less than 100 miles is ideal keeping in mind an excellent network bandwidth. However, in case of a failover, there could be a major impact on the performance caused by all the communication between the mirror server and the SAP application tier and the which also goes the very same distance as well. The utilization of synchronous database mirroring with automatic failover is perfect for scenarios where MSCS for SQL Server was utilized earlier. Depending on the traffic between the database server and application server instances, the throughput conditions may be more challenging, than for a pure synchronous solution which comes without automatic failover.

Related Articles