Prepare other self-managed databases for Backup and DR Service

Before you can back up the following self-managed database instances and data, you must add the database server as a host to Backup and DR Service. For this to work, most databases require some settings and other preparation steps. The procedures on this page are for most self-managed databases:

For	Review	Then use
IBM Db2	Backup and DR for IBM Db2	Db2 database prerequisites
MariaDB	Backup and DR for MariaDB	MariaDB database prerequisites
MySQL	Backup and DR for MySQL	MySQL database prerequisites
PostgreSQL	Backup and DR for PostgreSQL	PostgreSQL database prerequisites
SAP ASE	Backup and DR for SAP ASE	SAP ASE database prerequisites
SAP IQ	Backup and DR for SAP IQ	SAP IQ database prerequisites
SAP MaxDB	Backup and DR for SAP MaxDB	SAP MaxDB database prerequisites
SAP HANA	Backup and DR for SAP HANA	Prepare SAP HANA databases for backup
Microsoft SQL Server	Backup and DR for SQL Server	Prepare SQL server databases for Backup and DR Service
Oracle	Backup and DR for Oracle	Prerequisites for backing up an Oracle database and its following pages

For all other Backup and DR supported databases, follow the steps below.

Db2 database prerequisites

• Each database must be using Automatic storage mode, or else only instances are discovered.

• Each database must be in archive log mode, and LOGARCHMETH1 must be set to local disk. To learn if the database is in circular mode or archive log mode, run the command db2 get db cfg for <database name>| grep LOGARCHMETH1

• If the database is in circular mode, then change the logging to archive mode before continuing. To set the LOGARCHMETH1 parameter, use the following:
  db2 update db cfg for <dbname> LOGARCHMETH1 using 'DISK:<location>'

• For HADR-standby database the reads on standby option must be enabled, that is the DB2_HADR_ROS parameter must be set to ON.

  Note: Third-party log backup location is not supported. Log backup location (LOGARCHMETH1) must be set to local disk for backup and recovery to succeed.

MariaDB database prerequisites

• The MariaDB database must be residing under LVM and it must not be the boot volume. Run mysql -e "select @@datadir" to get the database data path.

• The LVM volume from which the MariaDB volumes are provisioned should have at least 20% free space.

• Install the Backup and DR agent on the MariaDB server.

• Create a backup user with the privileges Reload, Select, Lock tables, and Super or Replication client. A backup username and password must be configured with host configuration.

  To create the backup user, complete the following:

  create user actuser identified by 'actpasswd';
  GRANT SELECT on *.* TO actuser;
  GRANT RELOAD on *.* TO actuser;
  GRANT LOCK TABLES on *.* TO actuser;
  GRANT SUPER on *.* TO actuser;
  GRANT REPLICATION CLIENT on *.* to actuser;
  SHOW GRANTS FOR CURRENT_USER;
  

  Note: If there are multiple MariaDB instances running on a server, then the backup username and password must be common for all MariaDB instances running on that server.

• MariaDB binary logging log_bin must be on to take log backups. To configure the binary log option, shut down the MariaDB server and edit the config file my.cnf or my.ini. Within the [mysqld] section of the configuration file, add the log-bin option.

  [mysqld]
  log_bin = /log1/mysql3306/mysql3306-bin.0000
  

• The mysql user must be set to /bin/bash in the /etc/passwd file

MySQL database prerequisites

• The MySQL database must be residing under LVM and it must not be the boot volume. Run mysql -e "select @@datadir" to get the database data path.

• For best results, the LVM volume from which the MySQL volumes are provisioned should have at least 20% free space.

• Install the Backup and DR agent on the MySQL server host

• Make sure that the MySQL instance is up and running

• The owner of MySQL database—OS user—must be enabled to login, for example, database OS user should have a working shell like /bin/bash.

  Command to enable OS user: usermod -s /bin/bash mysql

• The MySQL instance should have a database backup user that can connect from localhost. Example: create user actuser'@'localhost identified by 'mypassword';

  Note: If there are multiple MySQL instances running on a server, then the database backup username and password must be common for all MySQL instances running on that server.

• Privilege required for database backup user in case of volume-level snapshot backup and recovery:

  GRANT SELECT on *.* TO actuser;
  GRANT RELOAD on *.* TO actuser;
  GRANT LOCK TABLES on *.* TO actuser;
  GRANT SUPER on *.* TO actuser;
  GRANT REPLICATION CLIENT on *.* to actuser;
  SHOW GRANTS FOR CURRENT_USER;
  

• The following privileges are required for database backup user in case of Full+Incremental—file-based traditional—backup and recovery:

  GRANT ALL ON *.* TO 'actuser'@'localhost';
  SHOW GRANTS FOR CURRENT_USER;
  

• One user defined database must be present other than system databases—information_schema, mysql, performance_schema, or sys.

• MySQL binary logging—log_bin—must be on to take log backup. To configure the binary log option, shut down the MySQL server and edit the config file—my.cnf or my.ini. Within the [mysqld] section of the configuration file, add the log-bin option.

  [mysqld]
  log_bin = /log1/mysql3306/mysql3306-bin.0000
  

PostgreSQL database prerequisites

This section includes information about the PostgreSQL database prerequisites.

Protect PostgreSQL from the primary node

1. Create a backup user:

   create user actuser;

2. Provide privilege for backup:

   alter user actuser with superuser;
   alter user actuser with Replication;
   

3. PostgreSQL archive mode—archive_mode—must be set to ON to take log backups. To configure the archive log mode option, update the postgresql.conf file in the data directory of the instance and reload the PostgreSQL.

   archive_mode = on
    // Command to reload PostgreSQL
    pg_ctl reload -D <Postgres_datadir_path>
   ``
   

Example: pg_ctl reload -D /pgdata

Protect PostgreSQL from a standby node

1. Create a backup user:

   create user actuser;

2. Provide privilege for backup:

   alter user actuser with superuser;
   alter user actuser with Replication;
   

3. PostgreSQL archive mode—WAL—setup on standby node:

   Create the directory where we want to keep the WALs.
   Example assumes /pglog is the directory. Run the following steps as root user.

   mkdir /pglog
   chown -R postgres:postgres /pglog
   

   Configure the parameters for archiving.
   Example assumes $PGDATA is /pgdata/11/data. Run the below steps as postgres user.
   Add or update the parameters in the file /pgdata/11/data/postgresql.conf

    wal_level = replica
    archive_mode = always
    archive_command = 'test ! -f /pglog/%f && cp %p /pglog/%f'
   

   Restart the PostgreSQL:

    /usr/pgsql-11/bin/pg_ctl stop -D /pgdata/11/data
    /usr/pgsql-11/bin/pg_ctl start -D /pgdata/11/data
   

   Add or update entry in pg_hba.conf on Primary to accept database connection from standby.
   

   host postgres <db-user> <standby-ip/32> <connection method>
   

   Example: host postgres postgres 10.128.0.29/32 trust

   Test the archive generation (as postgres user):

   ls -l /pglog/
   

   Run this log switch command:

    /usr/pgsql-11/bin/psql -h 10.128.0.28 -p5432 -c "select pg_switch_wal();"
    sleep 5 # log shipping may take some time depending on network
    ls -l /pglog/
   

SAP ASE database prerequisites

Before you begin, on the SAP ASE server complete the following:

• A backup user account must be created with backup privilege—either oper_role or sa_role. A backup username and password must be configured with host configuration. Create the user with or without a password to run the quiesce command on the source with sa_role/or with quiesce privileges. To create the user with a password, run the following:

  isql -Usa -P<password> -S<SAP ASE_Server_Name>
  1>sp_addlogin actuser, '<password>'
  2>go
  1>grant role sa_role to actuser
  2>go
  

  To configure the user login without a password requirement, set the password to null, then run the following command:

  1>sp_configure "minimum password length", 0
  2>go
  1> sp_modifylogin actuser, "min passwd length",
  "0"
  2> go
  3> exit
  isql -Uactuser -P'<password>' -S<server_name>
  > sp_password '<password>', NULL
  >go
  >sp_configure "minimum password length", 8
  >go
  

  Note: If there are multiple SAP ASE instances running on a server, then the backup username and password must be common for all SAP ASE instance running on that server.

• Install the Backup and DR agent on the SAP ASE server.

• To enable transaction log backup, Truncate on chkpt false must be set at the database level for each database of the SAP ASE instance.

• The following is true for volume level backups:

  • The SAP ASE instance must reside under LVM and it must not be the boot volume.

  • The LVM volume from which the SAP ASE volumes are provisioned should have at least 20% free space.

Note: SAP ASE discovery and backup jobs may hang due to a known issue with sp_spaceused. The reasons and the resolution for long running sp_spaceused procedures are explained in the SAP note: "2443782 - execute sp_spaceused takes too long - SAP ASE".

SAP IQ database prerequisites

Before you begin, review the following on the SAP IQ server:

• SAP IQ PITR logging must be ON to take log backup. To configure PITR log option, you need these SAP IQ API:

  • ALTER DBSPACE IQ_SYSTEM_LOG RENAME /<pitr_log_location>

  • SET OPTION PUBLIC.IQ_POINT_IN_TIME_RECOVERY_LOGGING = ON`

  After enabling PITR logging, a database full backup is needed.

• The utility database needs to start by specifying the username and password, and dbname should be utility_db. For example: start_iq -n utility_db -su act,passwd

• The database backup username and password should be the same as the utility_db username and password and it must have DBA privileges. To create the backup user, for example:

  create user actuser identified by 'mypassword';
  grant dba to actuser;
  

  Note: If there are multiple SAP IQ instances running on a server, then the backup username/password must be common for all SAP IQ instances running on that server.
  • Install the Backup and DR agent on the SAP IQ server host.

SAP MaxDB database prerequisites

Before you begin, on the SAP MaxDB server:

• If there are multiple MaxDB instances running on a server, then the DB username and password must be common for all MaxDB instance running on that server.

• Autolog backup must be enabled. Use the following command to enable auto log backup:

  dbmcli -d <DBSID> -u <DBM_USERNAME>,<DBM_PASSWD> autolog_on
  <autolog_backup_template_name>
  

• For enabling the autolog backup, you need an autolog backup template.

To create a backup template, use the following command.

    dbmcli -d <DBSID> -u <DBM_USERNAME>,<DBM_PASSWD> backup_template_create
    <BACKUP_TEMPLATE_NAME> to file  <FULL_PATH_WITH_FILE_NAME> content log

• Log Overwrite area must be deactivated to enable autolog backup enable.

To deactivate log overwrite, use the following command:

    dbmcli -d <DBSID> -u <DBM_USERNAME>,<DBM_PASSWD> db_execute SET LOG AUTO
    OVERWRITE OFF.

• Install the Backup and DR agent on the SAP MaxDB server host.

What's next

After the database is prepared for Backup and DR, you can add the host and discover the databases.