2017-02-19

Dump devices

To create the backup you work with a dump database command. When such a command is executed manually by administrator it is quite common just to use int the backup command just a file, where the backup should be stored:

DUMP DATABASE master TO '/home/dumps/master_2017_02_19.dmp'

However, when the backups should be created always in the same location, always with the same script and metodology, than it is quite convenient to use backup devices. Backup device is a definition that points to some location on the system. This can be a file but this can also be a tape device. The easiest way to create a device is using following sp_addumpdevice command:

EXECUTE  sp_addumpdevice 'disk', 'dumpsdev','/home/dumps/sap01.dmp'

The first parameter designates the device as a file on disk or a tape device. The second parameter is  called a logical name, and it will be used in commands performing the dump. The last one is the physical name of a device, in case of file this is just the name of a file.

Having such a device following command can be executed:

DUMP DATABASE master TO dumpsdev

This dump of master database will be saved in the dumpsdev this is in the file /home/dumps/sap01.dmp. Should you forget the connections between logical names and physical names, always a procedure sp_helpdevice can be used to display, how the device has been defined:

Errors starting Backup Server

Following errors can be seen when starting Backup Server:

Error 1

[sap@SAPSybase ~]$ /opt/sap/ASE-16_0/bin/startserver -f /opt/sap/ASE-16_0/install/RUN_SAP01_BS 
[sap@SAPSybase ~]$ Backup Server/16.0 SP02 PL01/EBF 25184/P/Linux AMD Opteron/Enterprise Linux/ase160sp02pl00/4902/64-bit/OPT/Wed Sep 9 09:04:15 2015 Confidential property of SAP AG or an SAP affiliate company. Copyright 2015 SAP AG or an SAP affiliate company. All rights reserved. Unpublished rights reserved under U.S. copyright laws. This software contains confidential and trade secret information of SAP AG or an SAP affiliate company. Use, duplication or disclosure of the software and documentation by the U.S. Government is subject to restrictions set forth in a license agreement between the Government and SAP AG or an SAP affiliate company, or other written agreement specifying the Government's rights to use the software and any applicable FAR provisions, for example, FAR 52.227-19. SAP AG or an SAP affiliate company 
Logging Backup Server messages in file '/opt/sap/ASE-16_0/install/SAP01_BS.log' 
Open Server Error: 16253.10.0: srv_thread_props(): property SRV_T_LISTENADDR not valid for service thread .

This error was in my case - fake! Backup server has been started correctly! This can be easy verified:

Method 1 - showserver command in bash

In the bash session start:

showserver

An example output can be similar to:

[sap@SAPSybase ~]$ showserver
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S sap       1272  1271  0  80   0 -  6541 poll_s 16:30 pts/0    00:00:00 
  /opt/sap/ASE-16_0/bin/backupserver -e/opt/sap/ASE-16_0/install/SAP01_BS.log -N25 -C20 
  -I/opt/sap/interfaces -M/opt/sap/ASE-16_0/bin/sybmultbuf -SSAP01_BS
0 S sap       1111  1106 16  80   0 - 488674 futex_ 16:27 ?       00:00:31 
  /opt/sap/ASE-16_0/bin/dataserver -d/opt/sap/data/master.dat -e/opt/sap/ASE-16_0/install/SAP01.log 
  -c/opt/sap/ASE-16_0/SAP01.cfg -M/opt/sap/ASE-16_0 -N/opt/sap/ASE-16_0/sysam/SAP01.properties 
  -i/opt/sap -sSAP01

The second line tells that backup server is up and running

Backup: Backup Server

There is a special service associated with Sybase installation: Backup Server. It can be used to perform the backup or load:
  • remotely
  • directly from disk, bypassing the main Sybase process
The steps done during a backup or restore process performed this way are:
  • Sybase process (can be from a different machine) sends using Remote Procedure Call (RPC) a request to perform a backup/load operation
  • Backup can be divided into many stripes. It is Backup Server responsibility to decide how big the stripes will  be. Additionally those stripes (files) can be located on local drives, but also on remote shares making the backup less depended on the same hardware as the database itself!
  • It is possible that a local Backup Server will perform the backup on locally installed databases and send the data to remote Backup Server, which in turn will save the received backup on local tapes available on that remote machine.
Of course backup server needs to be installed and configured on the same machine as the Sybase. That is a requirement, because backup server process needs to have access to the same devices as Sybase server has. Backup server listens on dedicated port that is different than the Sybase port. The information regarding ports can be found in the interfaces file. Below, an example content of interfaces file can be found:

2017-02-12

Backup: Some facts

Well, backup is one of the most mandatory duty of database administrator. Let's first answer a couple of backups features:

  • If the database contains a small amount of data, but it takes a lot of disk space on the disk, than the backup will be small, as only used pages are backed up, unused pages are skipped
  • Every administrator should have a backup and recovery plan and from time to time an exercise should be done. The goal of the exercise is to test if all required factors are fulfilled to successfully restore the database
  • To perform backup of the database you need to have permissions. The database owner (dbo). Of course it is enough to be aliased to the dbo. That way sa role is also capable of performing backup of any database. Additionally every member of the server role oper_role also has right to dump any database. Assuming there is login named backup_login, you can assign it to oper_role using following command:

    sp_role "grant" , oper_role, backup_login

    or using the newer syntax:

    GRANT ROLE oper_role TO backup_login
    
    
  • Backup can be done while the database is in use, so generally backups should be performed quite often depending on the load on the server, the number of transactions, the importance of the data stored in the database. Backup increases the load on the server (CPU and disk) ~5%

SAP Adaprive Server Enterprise

SAP Adaprive Server Enterprise
SAP Adaprive Server Enterprise