How to Configure SAP Applications for SQL Server Database Mirroring
Configuring the database mirroring with SAP applications mainly comprises of three steps, listed below:
The first step is related to the preparation, and is related in such a manner, SAP uses database schemas within SQL Server. SAP makes a SQL Server login for mapping the different database users who own the different schemas existing within the SAP database. This type of generally is created on the mirror side also. However, when the same login are created on the mirror side, an ID which is different from that on the principal system is allotted to such a login. Therefore, causing a mismatch between the ID of the newly created login stored in the master database of the mirror server and the ID which has been stored in the mirrored database. The main aim is to ensure that the IDs of the SQL logins in the master database of the mirror align with the IDs which were mapped in the mirrored database. The best way for achieving this is to create a back-up of the master database on the principal side and restoring it on the mirror side. The simplest way to achieve the same IDs for the SQL Server logins is by creating the principal and mirror side, is installing the SQL Server on the mirror side, on the principal side, backing up the master database, and then restoring the backup on the mirror side. Before initiating the restoring the backup of the principal side on the mirror side, you should ensure that tempdb is placed on the same drives on the mirror and on the principal. Post the restoration of the master database, user has to delete the principal server name from the restored master database on the mirror side with sp_dropserver. For adding the server name of the mirror utilize the stored procedure sp_addserver. Then begin the process of synchronizing the principal with the mirror side.
The second step is synchronizing the mirror side and the principle. This is performed with any kind of online backup of the principle side and the restoration of that backup on the mirror side with the option of not opening the database for user transactions. Later, you should apply all the transaction log backups which were created on the principle side. During this time, when all the transaction log backups have been restored, don’t back up the transaction logs on the principle side and establish mirroring between principle and mirror as listed in SQL Server 2005 Books Online, or whitepapers on MSDN (links provided above). Once mirroring has been successfully done, on the principal server, you can re-enable the regular backups of the transaction log.
The third step is preparing the SAP ABAP and Java stack for knowing the mirroring configuration. During an automatic or manual failover, would mean that in the SAP profiles and environment you have to supply the name of the mirror server. All these changed parameters are enabled in the absence of the principal server, by restarting SAP processes or by initiating SAP instances for knowing that a mirror server exists and the name of that mirror server. The following changes are essential for the ABAP stack running against SQL Server 2005.
User environment of the <sid>adm user.
Changing the environment parameter MSSQL_SERVER. The parameter generally entails the value of the database server assigned. Alter it by adding the mirror server name along with the failover database as follows:
Changes in Default.pfl
The parameter dbs/mss/server, which typically contains the database server name needs to be modified for containing the information on the mirror. It appears like:
DBHOST parameter in the TMS domain profile
For keeping the transport and correction system running inspite of the fact that the present database server is the mirror after a failover, in the TMS domain profile, you should alter the DBHOST parameter. Generally, the database server name is assigned in the DBHOST. Similarly, in the other two cases you should allocate the alternate mirror name and the database name. The parameter appears like:
Note: The parameters for the server name have a maximum length of 64 characters. If this length exceeds exceeded then refer to note 2020535 for a quick workaround.
As SAP has altered the Programming API from OLE DB to ODBC for supporting SQL Server 2008, there is a small change to be made in the spelling of the failover partner.
Therefore the entries for the three locations above would appear like:
- MSSQL_SERVER=<principal>;Failover_Partner= <mirror>;Database=<DBname>
- dbs/mss/server=<principal>;Failover_Partner=<mirror>;Database=<DBname >
- DBHOST=<principal>;Failover_Partner= <mirror>;Database=<DBname>
You can see the difference just being a '_' between the terms, partner and failover.
By using the Microsoft JDBC driver 1.2, the failover partner has to be configured in the Secure Store of the JAVA instance. The Secure store can be easily accessed using the SAP J2EE Engine Config Tool. The entry must be modified under the Secure Store is the entry of 'jdbc/pool/<SID>/Url'. The entry found usually appears looks like:
jdbc:sqlserver://<dbserver name>:1433;databasename=< SID>
Incase the entry begins with 'jdbc:nwmss:sqlserver' then the DataDirect driver still is in use. At this stage, it is not practical to continue before exchanging the JDBC driver as per the OSS note #1109274. The entry above now has to be changed for including the Failover Partner. So the entry would look like:
jdbc:sqlserver://<dbserver name>:1433; failoverpartner=<mirror name>; databasename=<SID>
The user takes notes that for all the three interfaces the spelling of the Failover Partner is different. Quite unusual, but this is how it works. The user is advised to be careful while modifying the entries.
You would be required to restart the SAP service for getting all of this in a working mode.