In this section I am going to cover a few more administration tasks like creating, backing up and restoring system and tenant databases, licensing.

System and Tenant Databases

There are two types of databases System (single) and Tenant (multiple), all the databases share the same installation of database system software, the same computing resources, and the same system administration. However, each database is self-contained and fully isolated with its own

• Set of database users
• Database catalog
• Repository
• Persistence
• Backups
• Traces and logs

I will have a section on licensing later but to make you aware you can obtain a license for each tenant database which allows you to use the database at a specific memory capacity level and if you can exceed it these are called enforce and unenforced but I will cover this in greater detail later.

Below is a SAP Hana system diagram, as you can see all SAP Instances have one System database, then you can have one or more tenant databases for your applications.

Tenant Databases

In this section we are going to create, view, backup and restore a tenant database using either the Hana DB studio, XS Apps, or commandline. In the below screenshot you can use the XS Advanced cockpit to manage tenant databases, see my XS Services section for more details, you can create or delete tenant databases.

When you create tenant database you need to provided a number of details such as name, password, etc

You can also create a tenant database using the Open SQL Console in Hana DB studio, using a the create database command

In the below we create a tenant database using the create database command, we user a SYSTEM user and a password.

It might take a few minutes to complete you can watch the progress at the bottom in the progress tab

Eventually (after 3mins in my case) the tenant database is created

You can then connect to it in the normal way, notice we specify that its a tenant database (multiple container)

In the below screenshot I have highlighted the system database and the tenants of which you can have up to 20.

To connect to a specific tenant database using the hdbsql command you use the -d option, notice the dbname when you have connected

If you use the XS services you can register a new tenant database in the Hana cockpit

Once registered you then have access to many of the view that are available for the database such as cpu usage, memory useage, etc.

Backing Up Databases

I have already touched on backups in my Hana Database Studio section, here I am going to cover it from a commandline point of view, however before we begin we need to perform a couple of tasks, the first we need to obtain the SQL Port number of the instance we can do this either via the commandline or via Hana DB studio (we will see this in a moment), connect to the systemdb database using hdbsql then type \mu and \al commands, then enter the SQL below

SELECT DATABASE_NAME, SQL_PORT FROM SYS_DATABASES.M_SERVICES WHERE DATABASE_NAME='SYSTEMDB' and SERVICE_NAME='nameserver' and COORDINATOR_TYPE= 'MASTER'

The SQL Port number is returned in my case 39013

You can also obtain the SQL Port number as per below using Hana DB Studio

SAP Hana uses a secure user store called the hdbuserstore that is used to securely store the connection information of SAP HANA system [i.e. database] on client side [i.e. application]. The secure user stores SAP HANA connection information like database host with Index server SQL port, user, and its password. Using this information stored in hdbuserstore, client applications can connect to the database without having to enter that user information every time. The backup command which we will see in a moment uses a key from the store to access the database. Below i list the store, you can see two keys are already created BACKUPKEY (which I created) and HXESAPDBCTRLHXE (created by the installation). To create a key you use the hdbuserstore command as per below

hdbuserstore SET <key name> <host>:<SQL Port>@<tenant db> <username> <password>

hdbuserstore SET DATADISKDBKEY hxehost:39013@DATADISK SYSTEM Password123

To perform a backup we use the hdbsql command and pass a string as below below, I specified a /backups directory which you can see two files have been created a database and log backup. There are a number of backups commands that you can use and will leave you to the SAP documentation

If we return to the Hana DB Studio -> backup catalog we can see that they backups has been created as per above.

In the below table I have listed a number of different backup commands that you can use

"backup data using FILE ('DATADISK-FULL-BACKUP')"

"backup data FOR DATADISK using FILE ('/backups/COMPLETE_DATA_BACKUP','DATADISK') COMMENT 'Full tenant database DATADISK data backup of Type FILE'"

"backup data using FILE ('/backups/FULL_DATA_BACKUP') COMMENT 'Full SYSTEMDB data backup of Type FILE'"

"backup data INCREMENTAL using FILE ('/backups/INC_DATA_BACKUP')"

Recovering Databases

You can use the Hana DB Studio to recover a tenant, by right-clicking on the systemdb selecting Backup and Recovery -> Recover System or Recover tenant, if you don't see these options for the systemdb then you have configred Hana DB Studio in correctly, make sure when you add the systemdb you select multiple container options and the system database radio button.

If you do not see the above for the systemdb then make sure you have added the SYSTEMDB to Studio as per below

Once you have selected to recover a tenant you are taken to a number of recovery screens the first being which tenant you wish to recover

Next is the type of recovery you wish to performed, recovery from the last backup, point-in-time recovery or even specify the backup to recover, you even have the option to a specific log position.

Next you have to locate where the backup catalog is located (this is not the actual backup), I left it as the default

When recovering a databse it must be offline and hence why we get a request the offlining the tenant database

Next the catalog will retrieve all the backup, you can now select which one to recover too, notice that the tenant database has been stopped (red box white cross)

Hopefully the actual backup location should be picked up automatically, if not then you can specify a location here

Then you get an option to select some of the advanced options for the recovery, again I left the default values here

Lastly we get to the review screen, click finish to start the recovery

A recovery progress screen then pops up

Finally we get the recovery completion screen

The tenant database will still be in a stopped status, to start it you have two choices you can either use the Open SQL console and type the below command

alter system start database <tenant database>

After a period of time the red box with a cross turns to green, you can right-click and refresh

The other way to start a tenant database you can use the XS Hana Cockpit, select the systemdb -> database management

You are taken to all the databases (systemdb and tenants), here you can start a database

Once the database has started the screen will refresh

Stopping and Starting Databases

You have a number options you can use the Open SQL console and HDBSQL using the commands below

alter system start database <database name>

hdbsql -U SYSTEMDBKEY "alter system start database <database name>"

alter system stop database <database name>

hdbsql -U SYSTEMDBKEY "alter system stop database <database name>"

You can also use the Hana DB Studio to stop/start the system db, however this method is only for the systemdb

You can also use the SAP Hana Cockpit

Licensing

In this brief section I am going to cover licensing, you can use the Hana DB studio to obtain the license for the system or a tenant, to access the license right-click the database (system or tenant) and select properties

In properties select the license link, notice the license type

A permanent license can be seen below, notice that you are license by memory in this case 1TB limit

You can have either a permanent or a temporary license, the type can be either of the below

• Enforced - the database is locked down when the current memory consumption of SAP HANA exceeds the licensed amount of memory plus some tolerance. If this happens, either SAP HANA needs to be restarted, or a new license key that covers the amount of memory in use needs to be installed.
• Unenforced - the operation of SAP HANA is not affected if its memory consumption exceeds the licensed amount of memory.

In lockdown mode, it is not possible to query the database. Only a user with the system privilege LICENSE ADMIN can connect to the database and execute license-related queries, such as, obtain previous license data, install a new license key, and delete installed license keys. The database cannot be backed up in lockdown mode.

To install a new license use Hana DB Studio and in the license screen click on the Install License Key button, then select the license key file.

There are many different types of licenses (and costs) from Enterprise, Standard, Hana One, Express (free up to 32GB memory limit).

For the commandline die hards you can use the below to view the license

Althrough not easily readable you can see things like product limit, expiration, etc

You can use the SET SYSTEM LICENSE supplying the key that you receive from SAP>

User, Roles and System privileges

Each database (system or tenant) has its own users and roles specific to that database, however a number of default users and roles are already created for you. Select the database and you can see the Security section, expand this and you can see the users and roles

To create a new user right-click on the users and select new user

The new user setup screen appears, here you enter the users name, password and even valid dates if the user is a temporary one.

Next we start giving the user privileges to grant access to the database, there are many roles already created

If the user requires admin access you can add additional system privileges

You have many other privileges as such as object, packages, application, etc

You have the option to add some user parameters to the user which will be used by applications, Hana itself, here I have set the users email address.

Next we can create roles with specific privileges, there might not be a default role that already exists with the privileges that you require so you can create your own specific role that you can give to users.

In the role creation screen you give it a name and add the privileges

As I mentioned each database has it own specific users and roles, this new tenant only has a handful of users created after creating it.