+++ UPDATE 23.11.2011 +++
After the release of revision 20 (SPS 03) of the HANA database, some more changes concerning command syntax and system object naming had been identified.
The commands in this blog post have been adapted to reflect this.
+++ UPDATE 23.11.2011 +++
+++ UPDATE 24.10.2011 ++++
I've been informed that with the current HANA revisions, starting with rev.16 which is available for download in the SAP Service Marketplace since 10th of September, a altered syntax for taking backups is used.
So, to make them easier to follow in the future, I changed the examples in this blog accordingly.
+++ UPDATE 24.10.2011 ++++
When you check the SAP In-Memory Database Backup and Recovery Guide on https://help.sap.com/hana you'll find on page 13 (4.3.2 Using SQL Commands) a first reference to a command line sql client:
hdbsql.
The example that follows explains how to create a very simple SQL script that can be fed into the hdbsql tool in batch mode.
That way a super simple schedulable backup command is created, e.g. for scheduling via CRON or any other scheduler tool.
This is what we find in the PDF (syntax updated - see update note above😞
|
Of course this is what most customers and partners do and use in their first HANA implementation.
And in the cases I've seen, for -u <user> the SYSTEM user was used.
This basically leads to a situation where there is a plain text sql script file that contains the full logon data for the SYSTEM user of your HANA box.
But are there alternatives? Sure there are!
Instead create a backup operater user that is not allowed to do anything else then performing backups.
Let's check how to do this step by step:
Logon to the HANA box as SYSTEM. You can do all the steps as well in HANA Studio, but as I assume most of you already are familiar with the user management there, so I present the command line version here (I chose a reddish color for the SYSTEM account sessions, just to make it a bit easier to recognize them - definitively NOT for decorative purposes...):
|
So, I'm logged on as SYSTEM user. Great! Let's go and create the user for backups!
(+++ 24.10.2011 - changed the logon from -U b to -U S more clarity +++)
|
Ok, I always forget - we have a password rule build into HANA and minimal password lenght is 8 characters.
Let's try again!
|
Grrr... I forgot again... this is really something I don't like about password rules... but anyway: one more try!
|
Success! Yes! Time for the first coffee break! (Ok, just kiddin' we're nearly done...)
Let's connect with this user and try to take a backup!
|
This is also something I already had a few support messages for.
The very first time you logon with a specific user to HANA you are forced to change your password.
Unfortunately there usually is no dialogue for this (it's present only in HANA Studio but which end-user has access to this?), so one either has to have access to a SQL command line (like we happen to have right now) or somebody must have taken care of this, by e.g. implementing a little macro in Excel for this.
Anyhow, there should be a better general solution, but for now just move on and change the password:
|
Worked like a charme. Next step: take the backup!
|
Dang! We didn't provide any of the required system privileges yet.
In a separate command line window I logon again as SYSTEM and run the following:
|
That was easy cheesy, wan't it?
Back to our backop-session and try once again:
|
We finally did it! Now we've got a safe user that can logon to the HANA DB but is only able to create backups.
Very nice.
Still we would have to put the logon data into the backup script file. But we have a second step to go...
The MaxDB veterans amongst the readers of this blog will already have recognized it:
HDBSQL is a ported version of SQLCLI the command line SQL client for SAPs very own MaxDB.
SQLCLI had a way to store user credentials and logon data called XUSER (I even wrote about it way back...).
And guess what: this had been ported as well.
It's called hdbuserstore now but works pretty much the same way.
ATTENTION: hdbuserstore is part of the HANA client package ONLY. I wasn't able to find it on a vanilla installation of HANA, so if you want to use it on the server (e.g. for the backup use case I present here) you've to install the HANA client on the server manually.
For this demonstration I simply used the HANA client on my laptop. To start, let's review the command line options for this tool:
|
In short the programm allows us to create, display and delete sets of logon data.
These sets have a
Let's go straight ahead and create an entry:
|
As you see, I've used
I'd wish the hdbuserstore command would provide more feedback in case everything went OK, something along "OK, profile b successfully created!" or similar, but today it's more along "no news are good news"...
Anyhow, we can review our newly created logon profile:
|
I guess we have to provide exactly what we want to see:
|
Et voilá, there is our entry.
One thing to keep in mind is that these entries are acessible only by the operating system user that created it.
So in order to use this for the CRONJOB backup, you'd have to logon to the user cron executed the backup script with and create the logon profile there!
Now we put it all together and change the backup script to
|
With this file we can now call the backup just like this:
|
Again, we don't get any output here if no errors occur.
This can become a bit annoying, especially when problems occur and debugging of the script should be done.
For that you can add the -f (show the SQL command) parameter and/or the -t (printout debugging information) parameter.
The output then looks like this:
|
or like this:
|
That's it once again!
As always I hope you liked the hands-on style of my blog and that you share your experiences with HANA.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
21 | |
16 | |
13 | |
13 | |
10 | |
10 | |
7 | |
7 | |
7 | |
7 |