Table of Contents
AzAcSnap command to orchestrate the online DB2 backup:
AzAcSnap Configuration (azacsnap.json)
Recover to time of backup (snapshot)
Recover to the last transaction
Version
1.0 July 2023
This document is for SAP/DB2 11.5.8 using Microsoft Azure NetApp Files and AzAcSnap version 8 or later.
|
(!) Note
Any use of AzAcSnap Preview features requires manual user acknowledgement by adding `--preview` to the command line. Previews are provided "as-is," "with all faults," and "as available," and are excluded from the service level agreements and limited warranty (see Supplemental Terms of Use for Microsoft Azure Previews).
You can check the latest AzAcSnap release notes (https://aka.ms/azacsnap-release-notes) and listed preview features (https://aka.ms/azacsnap-preview) to verify feature support status.
|
Authors
Phil Jensen, Principal Software Engineer
Holger Zecha, SAP Solution Architect
Scott McCullough, SAP Solution Architect
Overview
Corporations today require their SAP applications to be available 24 hours a day, 7 days a week. Consistent levels of performance are expected regardless of the ever-increasing data and need for routine maintenance tasks such as system backups. Performing backups of SAP databases are a critical task and can have a significant performance effect on the production SAP systems. With backup windows shrinking and the amount of data that needs to be backed up still increasing, it is difficult to define a time when backups can be performed with minimal impact on the business processes. The time needed to restore and recover SAP systems is of particular concern. That is because the downtime of SAP production and nonproduction systems must be minimized to minimize both the data loss and the cost to the business.
The following summarizes the SAP DB2 backup and recovery challenges:
- Performance impact on production SAP systems. Conventional backups typically lead to a significant performance impact on the production SAP system. That is because there is a heavy load on the database server, the storage system, and the storage network during traditional streaming-based backups.
- Shrinking backup windows. Conventional backups can be taken only during times when little dialog or batch activities take place on the SAP system. The scheduling of backups becomes more and more difficult to define when the SAP systems are in use 24/7.
- Rapid data growth. Rapid data growth, together with shrinking backup windows, results in ongoing investments into the backup infrastructure. Incremental or differential backups can address these issues, but this option results in a very slow, cumbersome, and complex restoration process that is harder to test and train and maybe error prone during an actual restore/recovery scenario.
- Increasing cost of downtime. Unplanned downtime of an SAP system always has a financial impact on the business. A significant part of the unplanned downtime is the time that is needed to restore and recover the SAP system after a failure. The backup and recovery architecture must be designed based on an acceptable RTO.
- Backup and recovery time included in SAP upgrade projects. The project plan for an SAP upgrade always includes at least three backups of the SAP database. The time needed to perform these backups dramatically cuts down the total available time for the upgrade process. The go/no-go decision is generally based on the amount of time required to restore and recover the database from the backup that was previously created. The option to restore very quickly allows more time to solve problems that might occur during the upgrade process rather than just restore the system back to its previous state.
Azure NetApp Files snapshot technology can be used to create online database backups within minutes. Because a snapshot does not move any physical data blocks on the storage platform. The time needed to create a snapshot is independent of the size of the database. The use of snapshot technology also has no performance impact on the live SAP system. That is because the Azure NetApp Files snapshots do not move or copy data blocks when the snapshot is created or when data in the active file system is changed. Therefore, the creation of snapshots can be scheduled without having to consider peak dialog or batch activity periods. Azure SAP customers leveraging Azure NetApp Files typically schedule multiple online snapshots during the day; for example, scheduling snapshots every four or six hours is common. These snapshots are typically kept for three to five days. For long-term retention older snapshots are then typically vaulted using Azure NetApp Files backup to Azure storage account.
Snapshots also provide key advantages for the restore and recovery operation. Azure NetApp Files ‘Revert Volume’ functionality allows restoration of the entire database to any point in time based on the available snapshots. This restore process is performed near-instantaneously, independent of the size of the database. Because several online snapshots are created during the day, the actual time needed for the recovery process is dramatically reduced, as opposed to a traditional backup approach. A restore operation can be performed using a snapshot that is only a few hours old (rather than up to 24 hours old); therefore, fewer transaction logs need to be applied. As a result, the mean time to recover (RTO), which is the time needed for restore and recovery operations, is reduced to just several minutes compared to multiple hours with conventional single-cycle tape backups.
Azure NetApp Files snapshots are stored on the same volume as the active online data. Therefore, it is recommended to use Azure NetApp Files backup or Azure NetApp Files cross-region replication (CRR) to a secondary Azure NetApp File paired region to safeguard the data from accidental deletions.
This document describes how to back up and recover an SAP NetWeaver/DB2 database when using Azure NetApp Files with AzAcSnap orchestration tool to protect the database. The two scenarios that are covered are:
- Recover to the time of backup
- Recover to the latest transaction
Disclaimer
As with anything especially in the IT field, there are multiple ways to accomplish a task. This document is by no means the only way to accomplish this task, it should be used as a guide and can be adjusted per individual circumstances.
Assumptions
The setup of AzAcSnap is not covered within this document. Setup instructions for AzAcSnap with DB2 can be found here.
The management of the DB2 log archives are not covered here.
|
(i) Important
It is critical the log archives that are created after AzAcSnap has completed a snapshot of the database are available during a forward recovery.
|
In this document the recovery of the SAP DB2 system is being performed on the same system (not a secondary system). The new control file feature introduced in DB2 11.5.7 is not being used.
The DB2 database is configured in log retention mode:
(LOGARCHMETH1) = DISK:/db2/G87/log_archive/
Lab System Overview
The lab system was created following the DB2 Installation Guide on Azure NetApp Files. Volume layout is slightly different; however variations can occur as long as you understand the ramifications.
Host: g87db2
Host OS: SLES 15 SP4 for SAP
DB2 version: 11.5.8.0
SAP NW: 7.5
Azure NetApp Files (ANF) NFSv4.1 volumes.
Mount points
|
Azure NetApp Files Volume |
Azure NetApp Files Sub-Directory |
File System |
|
g87-db2shared |
g87 |
/db2/G87 |
|
g87-db2shared |
sapmnt |
/sapmnt/G87 |
|
g87-db2shared |
usrsap |
/usr/sap/G87 |
|
g87-db2shared |
db2_software |
/db2/G87/db2_software |
|
g87-db2log-archive |
backup |
/db2/G87/backup |
|
g87-db2log-archive |
db2dump |
/db2/G87/db2dump |
|
g87-db2log-archive |
log_archive |
/db2/G87/log_archive |
|
g87-db2log |
log_dir |
/db2/G87/log_dir |
|
g87-db2saptmp |
saptmp1 |
/db2/G87/saptmp1 |
|
g87-db2data |
db2g87 |
/db2/G87/db2g87 |
|
g87-db2data |
sapdata1 |
/db2/G87/sapdata1 |
|
g87-db2data |
sapdata1 |
/db2/G87/sapdata2 |
|
g87-db2data |
sapdata1 |
/db2/G87/sapdata3 |
|
g87-db2data |
sapdata1 |
/db2/G87/sapdata4 |
/etc/fstab:
# SAP Mounts
10.1.9.6:/g87-db2shared/g87 /db2/G87 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2shared/db2_software /db2/G87/db2_software nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2shared/usrsap /usr/sap/G87 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2shared/sapmnt /sapmnt/G87 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
# DB2 Mounts
10.1.9.6:/g87-db2data/db2g87 /db2/G87/db2g87 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2data/sapdata1 /db2/G87/sapdata1 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2data/sapdata2 /db2/G87/sapdata2 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2data/sapdata3 /db2/G87/sapdata3 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2data/sapdata4 /db2/G87/sapdata4 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2saptmp/saptmp1 /db2/G87/saptmp1 nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2log/log_dir /db2/G87/log_dir nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2log-archive/log_archive /db2/G87/log_archive nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2log-archive/db2dump /db2/G87/db2dump nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
10.1.9.6:/g87-db2log-archive/backup /db2/G87/backup nfs4 rw,hard,nconnect=8,sync,rsize=262144,wsize=262144,vers=4.1,tcp 0 0
DB2 Protected File Paths
These Azure NetApp Files volumes must be included within the AzAcSnap.json data section for a successful snapshot. Optionally, you can add additional volumes to the other section to capture additional volumes.
As the db2SID UID issue the following command (lab system output) to display the DB2 protected file paths:
db2 "select substr(path,1,100) from sysibmadm.dbpaths where type not like 'LOGPATH'"
You must include all Azure NetApp Files volumes that contain the file systems generated by the above db2 select command. In addition, to perform a fast restore you should not include any other file system within these Azure NetApp Files volumes other than the ones specified from the “select…” command.
|
g87-db2data |
/db2/G87/sapdata4/NODE0000/G87#USER1I.container003 /db2/G87/sapdata3/NODE0000/G87#USER1I.container002 /db2/G87/sapdata2/NODE0000/G87#USER1I.container001 /db2/G87/sapdata1/NODE0000/G87#USER1I.container000 /db2/G87/sapdata4/NODE0000/G87#USER1D.container003 /db2/G87/sapdata3/NODE0000/G87#USER1D.container002 /db2/G87/sapdata2/NODE0000/G87#USER1D.container001 /db2/G87/sapdata1/NODE0000/G87#USER1D.container000 /db2/G87/sapdata4/NODE0000/G87#SOURCEI.container003 /db2/G87/sapdata3/NODE0000/G87#SOURCEI.container002 /db2/G87/sapdata2/NODE0000/G87#SOURCEI.container001 /db2/G87/sapdata1/NODE0000/G87#SOURCEI.container000 /db2/G87/sapdata4/NODE0000/G87#SOURCED.container003 /db2/G87/sapdata3/NODE0000/G87#SOURCED.container002 /db2/G87/sapdata2/NODE0000/G87#SOURCED.container001 /db2/G87/sapdata1/NODE0000/G87#SOURCED.container000 /db2/G87/sapdata4/NODE0000/G87#DIMI.container003 /db2/G87/sapdata3/NODE0000/G87#DIMI.container002 /db2/G87/sapdata2/NODE0000/G87#DIMI.container001 /db2/G87/sapdata1/NODE0000/G87#DIMI.container000 /db2/G87/sapdata4/NODE0000/G87#DIMD.container003 /db2/G87/sapdata3/NODE0000/G87#DIMD.container002 /db2/G87/sapdata3/NODE0000/G87#POOLD.container002 /db2/G87/sapdata2/NODE0000/G87#POOLD.container001 /db2/G87/sapdata1/NODE0000/G87#POOLD.container000 /db2/G87/sapdata4/NODE0000/SYSTOOLSPACE.container003 /db2/G87/sapdata3/NODE0000/SYSTOOLSPACE.container002 /db2/G87/sapdata2/NODE0000/SYSTOOLSPACE.container001 /db2/G87/sapdata1/NODE0000/SYSTOOLSPACE.container000 /db2/G87/sapdata4/NODE0000/SYSCATSPACE.container003 /db2/G87/sapdata3/NODE0000/SYSCATSPACE.container002 /db2/G87/sapdata2/NODE0000/SYSCATSPACE.container001 /db2/G87/sapdata1/NODE0000/SYSCATSPACE.container000 /db2/G87/db2g87/NODE0000/sqldbdir/ /db2/G87/db2g87/NODE0000/SQL00001/ /db2/G87/db2g87/NODE0000/SQL00001/MEMBER0000/ |
|
g87-db2saptmp |
/db2/G87/saptmp1/NODE0000/temp16/SYSTOOLSTMPSPACE.directory000/ /db2/G87/saptmp1/NODE0000/temp16/PSAPTEMP16.directory000/ |
AzAcSnap backups
AzAcSnap command to orchestrate the online DB2 backup:
azacsnap --preview -c backup -vv --retention 3 --prefix G87_DB2 --volume data
Option definitions can be found in the AzAcSnap documentation.
|
(!) Note
During AzAcSnap operations when DB2 is in “write suspend” mode all write operations will be paused to the database. Azure NetApp Files snapshots are near instant, however the communication between AzAcSnap and the Azure resource manager may take several seconds to complete communications. No transactions are lost during this period and this operation should have no impact to the overall system availability.
|
AzAcSnap Configuration (azacsnap.json)
Ensure the azacsnap UID has the following:
- dbSIDadm group member
- $PATH includes path to the db2 executables
- Environment variable INSTHOME is set properly
INSTHOME="/db2/G87/home/db2g87"
if [ -f ${INSTHOME}/sqllib/db2profile ]; then
. ${INSTHOME}/sqllib/db2profile
fi
These Azure NetApp Files volumes will be captured within an Azure NetApp Files storage snapshot while DB2 is in write suspend mode. These volumes were derived from the previous step finding the protected paths.
|
g87-db2data |
g87-db2saptmp |
azacsnap.json file:
{
"version": "8b",
"logPath": "./logs",
"securityPath": "./security",
"comments": [
"G87"
],
"database": [
{
"hana": null,
"oracle": null,
"db2": {
"serverAddress": "localhost",
"sid": "G87",
"instanceUser": "db2g87",
"hliStorage": [],
"anfStorage": [
{
"anfBackup": "none",
"dataVolume": [
{
"resourceId": "/subscriptions/xxxxxx-xxxx-xxxxx-xxxx-xxxxxxxxxxx/resourceGroups/rg-mcscott/providers/Microsoft.NetApp/netAppAccounts/SAP-EastUS/capacityPools/sap-premium-mqos/volumes/g87-db2data",
"authFile": "azureauth.json", "subscription": " xxxxxx-xxxx-xxxxx-xxxx-xxxxxxxxxxx",
"resourceGroupName": "rg-mcscott",
"accountName": "SAP-EastUS",
"poolName": "sap-premium-mqos",
"volume": "g87-db2data"
},
{
"resourceId": "/subscriptions/ xxxxxx-xxxx-xxxxx-xxxx-xxxxxxxxxxx /resourceGroups/rg-mcscott/providers/Microsoft.NetApp/netAppAccounts/SAP-EastUS/capacityPools/sap-premium-mqos/volumes/g87-db2saptmp",
"authFile": "azureauth.json",
"subscription": " xxxxxx-xxxx-xxxxx-xxxx-xxxxxxxxxxx",
"resourceGroupName": "rg-mcscott",
"accountName": "SAP-EastUS",
"poolName": "sap-premium-mqos",
"volume": "g87-db2saptmp"
}
],
"otherVolume": []
}
],
"amdStorage": []
}
}
]
}
Recovery from snapshots
Recover to time of backup (snapshot)
This example will recover the SAP DB2 database to the time of backup (no forward recovery). The example will use printer entries within SAP NW transaction SPAD to demonstrate recovery.
- Current list of output devices
- Perform AzAcSnap backup operation with backup results:
azacsnap --preview -c backup -vv --retention 3 --prefix G87_DB2 --volume data
- Create new spool entry “XXXX”
Recover the database to the time of backup that will show only device entries “LP01, URD4”. -
Shutdown R3 and the database.
- Restore Azure NetApp Files snapshot
(x) Caution
The following operations are destructive as we will be reverting the Azure NetApp Files SAP data volumes (g87-db2data, g87-saptmp) back in time to the snapshot created via AzAcSnap. Optionally, you could create new a volume from this snapshot to keep the data currently in the live file system.
(o) Tip
You can use the Azure portal or AzAcSnap to retrieve snapshot names for these volumes.
azacsnap -c details --previewg87-db2data
G87_DB2__F5A1D921F8C
g87-saptmp
G87_DB2__F5A1D921F8C
You must select the Azure NetApp Files snapshot name you wish to use. Optionally, you can leverage AzAcSnap to revert the Azure NetApp Files volume.
AzAcSnap command to revert the volumes to the latest snapshot:
acacsnap -c restore --restore revertvolume --dbsid G87 --preview
Leveraging the Azure Portal select your Azure NetApp Files subscription as well as the capacity pool and volumes that contain the DB2 volumes. Select ‘Snapshots’ and then the appropriate snapshot name.
The Azure NetApp Files volumes have been reverted to the time of the backup. This operation regardless of size takes only seconds.
- Recover the database to the time of backup.
As db2SID start the DB2 instance:
- Start the DB2 recovery
db2inidb G87 as mirror
- Query the DB2 database to retrieve status
db2 rollforward db G87 query status - Recover the DB2 instance to 1 second after the “Last committed transaction”.
This will recover the DB2 instance to the time of backup.
db2 rollforward db G87 to 2023-06-28-17.02.48.000000
- Stop the recovery and open the database instance
db2 rollforward db G87 stop
- Start SAP R/3 and verify printer entries within SPAD.
Successfully completed recovery to time of backup.
Recover to the last transaction
This section describes how to recover an SAP DB2 database when using Azure NetApp Files with AzAcSnap orchestration tool to protect the database to the last transaction.
The example will use printer entries within SAP NW transaction SPAD to demonstrate recovery.
- Current list of output devices
- Perform AzAcSnap backup operation with backup results:
azacsnap --preview -c backup -vv --retention 3 --prefix G87_DB2 --volume data
- Create new spool entry “ZZZZ”
Recover the database with forward recovery to show newly created spool device “ZZZZ”.
- Shutdown R3 and the database.
- Restore Azure NetApp Files snapshots
(x) Caution
The following operations are destructive as we will be reverting the Azure NetApp Files SAP data volumes (g87-db2data, g87-saptmp) back in time to the snapshot created via AzAcSnap. Optionally, you could create a new volume from this snapshot as to keep the data currently in the live file system.
(o) Tip
You can use the Azure portal or AzAcSnap to retrieve snapshot names for these volumes.
azacsnap -c details --previewg87-db2data G87_DB2__F5A1F83E7E1 g87-saptmp G87_DB2__F5A1F83E7E1
In this example we will use AzAcSnap command to revert the volumes to the latest snapshot
acacsnap -c restore --restore revertvolume --dbsid G87 --preview
- Recover the database to the last transaction.
As db2SID start the DB2 instance
- Start the DB2 recovery
db2inidb G87 as mirror
- Query the DB2 database to retrieve status
db2 rollforward db G87 query status
- Recover the DB2 instance to the latest transaction.
db2 rollforward db G87 to end of logs
(this may take several minutes to complete depending on the number of logs)
- Stop the recovery and open the database instance
db2 rollforward db G87 stop
- Start SAP and verify printer entries.
Successfully completed recovery to latest transactions.