Table of Contents

Version

Authors

Overview

Disclaimer

Assumptions

Lab System Overview

Mount points

DB2 Protected File Paths

AzAcSnap backups

AzAcSnap command to orchestrate the online DB2 backup:

AzAcSnap Configuration (azacsnap.json)

Recovery from snapshots

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.

 

 

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. 

 

 

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

 

           

/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.

 

 

 

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.

 

 

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.

 

 

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.

 

1. Current list of output devices
   
   

    

2. Perform AzAcSnap backup operation with backup results:
   
   

   azacsnap --preview  -c backup -vv --retention 3 --prefix G87_DB2 --volume data

   

    

3. Create new spool entry “XXXX”
   
   

   
   Recover the database to the time of backup that will show only device entries “LP01, URD4”.
   
   

4. Shutdown R3 and the database.
   
   

5. 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 --preview

   g87-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. 

 

6. Recover the database to the time of backup.
   
   As db2SID start the DB2 instance:
   

    

7. Start the DB2 recovery
   
   

   db2inidb G87 as mirror

   

    

8. Query the DB2 database to retrieve status
   
   

   db2 rollforward db G87 query status

   

    

9. 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
   

   

 

10. Stop the recovery and open the database instance
    
    

    db2 rollforward db G87 stop

    

 

11. 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.

 

1. Current list of output devices
   
   

 

2. Perform AzAcSnap backup operation with backup results:
   
   

   azacsnap --preview  -c backup -vv --retention 3 --prefix G87_DB2 --volume data

   

 

3. Create new spool entry “ZZZZ”
   
   
   
   Recover the database with forward recovery to show newly created spool device “ZZZZ”.
   
   

5. Shutdown R3 and the database.
    
6. 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 --preview

   g87-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

   

 

6. Recover the database to the last transaction.
   
   As db2SID start the DB2 instance
   

 

7. Start the DB2 recovery
   
   

   db2inidb G87 as mirror

   

 

8. Query the DB2 database to retrieve status
   
   

   db2 rollforward db G87 query status

   

    

9. 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)

 

11. Stop the recovery and open the database instance
    
    

    db2 rollforward db G87 stop

    

     

12. Start SAP and verify printer entries.
    
    

Successfully completed recovery to latest transactions.