Do you want to learn why Database backups are different from file backups? Click HERE to find out.
Do you want to learn why
are different from file backups?
Click HERE to find out.
Backing up Databases using Repostor and TSM
Open Source Databases
Our businesses and institutions face multiple threats from hackers, ransomware threats, and terrorist attacks. Much of our important data is held in Open Source databases, such as Maria DB or Mongo DB. Open Source databases do not always have a rich legacy of backup utilities, leaving customers to create their own processes for taking reliable backups and getting them off-site.
We may have to go back to the traditional method of backing these databases, by taking them down, then backing up the native data or by using the DBMS to backup to local disk. However if you want to be sure that you can recover from your backups, you need to use the Database Management System (DBMS) tool as this understands the database layout, and often lets you take a copy of the database while it is open and in use. You also need to be able to interface this DBMS with a reliable backup store that keeps the data secure for the correct time, deletes it when it is no longer required, and stores it in more than one physical location in case of a disaster.
What you need is a complete, secure solution that can cope with Open Source databases. A solution that takes reliable copies of database data, stores it offsite without needing too much expensive network capacity, potentially even in the Cloud, and ensures that those databases can be recovered from that backup.
Repostor Sofware is designed to manage open source databases, but instead of trying to ‘re-invent the wheel’, they make use of exisiting backup facilities and interface between them. Repostor supports a number of Open source databases, including:
- SAP IQ
- SAP ASE
Repostor uses the DBMS from each database to ensure a complete backup. It has two components:
back to top
Repostor Data Protector
Repostor’s Data Protector takes a backup produced by the database system and moves that backup data offsite for disaster recovery and long term protection. Data Protector uses IBM Spectrum Protect, more commonly still known as TSM, as the data store. This means that you get all the facilities that TSM offers for backup storage, such as backup retention rules, mirrored remote site storage and Cloud storage. Repostor Data Protector (RDP) works as an interface between your existing IBM Spectrum Protect environment and your database. Each database type has its own RDP, for example RDP for Sybase IQ. The RDP is installed on the same server as the database and includes a Repostor device driver which emulates 32 rdpdev virtual tape devices. This means that from the viewpoint of the DBMS, it appears to be writing data out to tape devices.
The RDP needs a Spectrum Protect Backup Archive (BA) client and a Spectrum Protect API installed on the database server. The RDP then passes the database backup data through the BA client using the API interface. The RDP will also intercept any restore data coming from the BA client, and passes it back to the DBMS.
The RDP uses the DMBS tools, triggered by either a TSM schedule or a native scheduler to initiate a the backup on the database server. The type of backups supported are dependent on what type of backup the DBMS itself supports. Three options are potentially available; full backup, incremental backup and log backup, but some databases do not support all three options. The TSM client is set to use both client-side deduplication and compression, which limits the amound of physical backup data that is transferred over the LAN or SAN to the Spectrum Protect server. This speeds up the transfer process, and reduces the requirement for fast network links.
For example, the RDP for MySQL supports full backups and transaction log backups. It uses the native tools 'mysqldump' and 'mysqlbinlog' and communicates directly with the TSM Server without storing any dump files locally. As RDP transfers data to TSM server using the TSM API, this means data is immediately stored offsite and potentially in more than one offsite location.
Backup data Retention is managed by TSM. In general, RDP uses the database name for the backup file name, so the filename is always the same. You identify different backups by the time stamp. This means that it is important to define the Retain Extra Versions parameter in the copy group construct correctly, as this will determine how many backups of each database are kept.
The RDP for MySQL, MariaDB and PostgreSQ can backup the transaction logs (WAL in PostgreSQL), and these are saved in TSM as 'archive' objects. The retention policy for archive should be the same as for backups, as there is no point in keeping the transaction logs for any longer than the last full backup.
DBAs can use their own queries the find out which backups exist, and these queries are passed by Repostor to the TSM server, then the result passed back. This enables the DBAs to query and run their own restores using their DBMS commands.
back to top
Repostor Snap Protector
Repostor Snap Protector extends Data Protector by taking an almost continuous copy of a database as changes are made to it, which will protect against accidental damage and malicious data hacking. Snap Protector is intended for larger databases and companies that cannot afford data loss. It would also be possible to recover a database from a snapshot very quickly using the reverse snap process. Snap Protector splits the database storage up into blocks, then the first time it backs up the database, it copies all the blocks. After the first backup, it only copies those blocks that have changed and every time a database block is changed, it copies those changed blocks off to Spectrum Protect.
This means that the backups after the first backup are very fast and provide an almost continuous backup of the database. Snap Protector is really designed to work with bigger databases that are stored on a Linux platform and it supports MongoDB, MariaDB, MySQL and PostgreSQL. Significant features of Snap Protector include:
- Safest and fastest backup possible as Block to Block copy is faster than going through application
- Fastest recovery time
- Nearly instant and continuous backup
- Continuous offsite disaster recovery
- Simple to use, One command to perform backup / restore / rollback
- Only block changes are sent to the Spectrum Protect Server, and on a recovery, only changed blocks will be restored from the Spectrum Protect Server
- Block level incremental forever combined with deduplication reduces network load
It is important to get the TSM retentions correct for Snap Protector, to ensure your backup data is avaiable if you need to do a restore. The retentions are not the same as RDP. You need to define your TSM client node with 'backdel=yes' and pick up a copy group that has both 'versions data exists' and 'versions data deleted' set to 'no limit'. It may even be best to define retentions for Snap Protector clients in a different TSM Policy Set to avoid confusion.
back to top
This is just an overview of a typical install, based on the configuration for RDP for Sybase (SAP) IQ. Check out the installation manuals for each RDP product, as there will be slight differences for each database type. The install is for a typical Linux TSM client install, with the Linux host called LINP0001. AIX and Windows clients are similar, but the path names are different.
l. Work required at the TSM Server
Register a client node on the TSM Server called LINP0001_iq. LINP0001 is the name of the Linux host. Adjust the names to suit your environment. If there is no suitable Policy Set and Copy Group for your backup retention requirements, you may need to define these also.
2. Work required at the TSM Client
Assuming you already have a TSM BA client installed, you need to create a new TSM options file for the database backup, with a corresponding stanza in the dsm.sys file. On Linux, both files are in /opt/tivoli/tsm/client/ba/bin. The exact opt file name will depend on the database type. For this example, create a file called dsm.iq.opt file with this content:
Create a stanza for this servername in the dsm.sys file like this. The SErvernames in the dsm.iq.opt file and the dsm.sys file must match.
Create soft links from the TSM API directory to the ba/bin directory for both dsm.sys and dsm.opt. In particular, you need to link the dsm.opt file in the API directory to the dsm.iq.opt file in the ba director.
ln -s /opt/tivoli/tsm/client/ba/bin/dsm.sys /opt/tivoli/tsm/client/api/bin64/dsm.sys
ln -s /opt/tivoli/tsm/client/ba/bin/dsm.iq.opt /opt/tivoli/tsm/client/api/bin64/dsm.opt
Check that all users that will be working with RDP can read the sys and opt files, and can write to the rdp log files. Be sure to check the user that will run the backup and restore commands. If they do not have access, use the 'chmod' command to grant that access.
Check that the TSM client side can access the TSM server, using the userid that will run the backup and restore commands.
3. Install and Configure the Repostor software.
Install both the rdpdev and the rdp database packages. Check the Repostor website for the correct RDP package for your database, and ensure you are using the correct rdpdev package for your OS version.
The rdpdev driver will need to be started after installation on Linux, using the command:
4. Sort out Profiles and Permissions
The profile for the DBA user needs to have the PATH and DSMI_CONFIG variables set to include the Repostor binary directory. For a Linux bash shell, you do this in the .bash_profile file in the users home directory.
If you do not use the SAP IQ database userid to run the RDP commands, you may need to set the IQ environment. The path and name of the IQ environment file will depend on your installation. The sample here is /opt/sap/IQ.sh.
The environment is set with a dot ('.'), followed by a space ' ', and then the path to the file. As in the previous section, this can be done in the .bash_profile
Sort out the IP alias if necessary. How you do this will depend on your installation standards. You may find that the instance name is not the same as the hostname, You may have a dedicated IP for the instance, or you may use an alias for the hostname, but in all cases the instance name used with the '-S' option needs to be reachable. So if you do not already have a DNS name for this IP, you need to add one, either with an alias in local /etc/hosts file, or an alias in DNS. An example of adding an alias to /etc/hosts is:
# IP address # Hostname # Alias
18.104.22.168 servername aliasname
Verify that the SAP IQ userid that will be used for the dbisql connection has the correct permissions to be able to connect to TSM. You specify this userid and password with the RDP command options '-u' and '-p' .
dbisql -onerror exit -nogui -c "UID=DBA;PWD=sql;dbn=DBNAME;eng=IQserver"
Warning: Interactive SQL is currently configured to quit if a SQL statement fails to execute.
Verify that the userid within IQ has the correct permissions to be able to perform a backup, but first check that you have enough space in the filespace to take a full database backup. The pathname /tmp/iqbackup can be altered, but it must exist. Substitute your own userid, password and database name in this command.
dbisql -onerror exit -nogui -c "UID=DBA;PWD=sql;dbn=DBNAME;eng=IQserver" "BACKUP DATABASE TO '/tmp/iqbackup/iqfull';"
5. Install the RDP License.
RDP licences are for backup volumes not individual databases and refer to TSM servers, so if you adding a extra database this step might not be necessary. If you are running a POC (Proof of Concept) and not yet have received license keys, the RDP will automatically create a TEMP license key when the first backup is run. This TEMP license will be valid for 30 days.
The commands below is a sample for creating a license.dat template file with CONTRACT name 'MYCORP' and two SERVER licenses
updatelicense CONTRACT MYCORP
updatelicense SERVER b7777e8e0ceca181ba71016c4863bfbe
updatelicense SERVER b2747e7a0aeca371ca52014c3464cfad
6. Run a test backup
Verify the whole process works by running the sybaseiqbackup command, using the user and password to dbisql that you verified in step 4. Here is a sample with user 'dba', password 'sql', engine 'IQserver', database 'DBNAME' and port number '2638' :
sybaseiqbackup -u dba -p sql -S IQserver -s DBNAME -P 2638 -v
7. Define a backup script
Finally, setup a backup script that you can schedule from TSM. The following is a sample backup script, but you will need to substitute your own file names and paths. The operating system variable LOGNAME is set to the OS user that should run the RDP commands, in this case 'root':
### Set IQ Environment
### Set PATH & DSMI_CONFIG
### Set LOGNAME
### Run sybaseiqbackup command, send output to logfile under /tmp
sybaseiqbackup -u dba -p sql -S IQserver -s DBNAME -P 2638 -v >/tmp/iqbackup.log 2>&1
back to top
Repostor Backup Commmands
The following are a few examples of Repostor backup and restore commands for Sybase (SAP) IQ databases hosted on Linux. Most commands show the userid and password hardcoded, which is a security risk. Repostor has the ability to store these values in a password file, but that is not explained here. The Repostor manuals provide full details on how to implement this. The second command is an example that uses the password file.
For Full backups, the RDP command 'sybaseiqbackup' command calls on the IQ native tool 'backup database' and for incremental backup, the RDP command 'sybaseiqbackup' calls the IQ tool 'backup database INCREMENTAL'. In both cases Repostor sends the data to the TSM Server through the rdpdev device. SAP IQ supports two ways of doing incremental backups: 'INCREMENTAL SINCE FULL' – backup all changes since last FULL backup and 'INCREMENTAL' - backup all changes since the last FULL or INCREMENTAL backup.
To take a full backup of database DBNAME using port number 2641
sybaseiqbackup -u USER -p PASSWORD -S INSTANCENAME -s DBNAME -P 2641 -v
This example takes a full backup, but uses a DBFHIDE file so as not to expose the userid and password. You would need to substitute your own path and filename:
export REPOSTOR_IQ_DBFHIDE=/PATH/FileName sybaseiqbackup -E -S INSTANCENAME -s DBNAME -P 2641 -v
This example takes a full backup connecting with shared memory using the '-m' parameter. This is explained fully in the manuals:
sybaseiqbackup sybaseiqbackup -u USER -p PASSWORD -M -S INSTANCENAME -s DBNAME -P 2641 -v
This example takes a full backup, but with an encrypted connection:
sybaseiqbackup -u USER -p PASSWORD -S INSTANCENAME -P 2641 -s DBNAME -v
This example takes a full backup, specifying rdpdev20 as the base rdpdev device (virtual tape drive) The second sample uses the parameter '-C5' which means it uses 5 streams and so would use virtual drives rdpdev20-rdpdev24. You would use specific drives to avoid drive contention, and if you run a backup with 5 streams for performance, you must specify 5 streams to restore from this backup.
sybaseiqbackup -u USER -p PASSWORD -S INSTANCENAME -s DBNAME -P 2641 -v
sybaseiqbackup -u USER -p PASSWORD -S INSTANCENAME -s DBNAME -P 2641 -C5 -v
This example uses the '-i' parameter, which means it takes an incremental backup of database DBNAME using port number 2641. This will back up all changes since the last full backup.
sybaseiqbackup -u USER -p PASSWORD -S INSTANCENAME -s DBNAME -P 2641 -i -v
This example will restore database DBNAME from the active (most recent) backup:
sybaseiqrestore -u USER -p PASSWORD -S INSTANCENAME -s DBNAME
This example performs a point in time restore to the time specified with the '-t' option. Please note that the format of the time for the '-t' flag is the same as shown by the sybaseiqquery command output:
sybaseiqrestore -u USER -p PASSWORD -S INSTANCENAME -s DBNAME -t 2018-03-17.12:45:17
The RDP allows the DBA teams to have more access to their backups by providing not only the backup & restore commands, but also allows them to list out the existing backups. This is done by use of the sybaseiqquery command.
In the output, the (A) indicates the latest ('Active') backup, and the (I) indicates older ('Inactive') backups.
The sybaseiqquery command output will show the same number of versions that are retained the TSM retention policy that the RDP node uses.
For example to query full backups:
# sybaseiqquery -S INSTANCENAME -s
version state owner instance database date time size
------ ----- ----- -------- -------- ------------------ --------
1 (A) root instname iq1 2020-03-17.11:13:35 58887972
2 (I) root instname iq1 2020-02-30.12:32:19 53536756
And to query incremental backups:
# sybaseiqquery -S INSTANCENAME -s dbname -i
version state owner instance database date time size
------ ----- ----- -------- -------- ------------------- --------
1 (A) root instname dbname 2020-03-20.15:43:53 6404892
back to top