Connecting to SAP HANA from PowerShell


It has been quite some time since I blogged. And I can definitely feel the rust on my blogging gears as I feel my way around words. Please endure my wanderings.

I work on some interesting projects as part of my job. And, sometimes you have to fill in some shoes that no one else wants to get into. But, as they say, variety is the spice that keeps things interesting in life.

During the second half of 2014 I was filling/fitting in the shoes of a HANA DBA. Mostly, it was run of the mill stuff, make sure everything is fine; trawl through the OOM (Out Of Memory) dumps and figure out who or what caused the dump and raise a case with SAP in case we failed to figure out why, apply some recommendations from SAP notes etc. In short, life was boring. One of the things we wanted to do was setup interim monitoring while Solution Manager was being setup. Anyways, back to the topic, when I say monitoring, it was nothing fancy; just get the backup statuses and some memory consumption related information had to be pulled in and put in the form of a dashboard. I was able to do it manually for two days before I decided to write some PowerShell to get some extra time on my hands.

In my case the problem statement is a familiar one, something we are used to in the DBA world. So, it was:

  • Get backup data from a given SAP HANA instance.
    1. Create a connection to HANA instance of interest without using a DSN.
    2. Use this connection to run a query against the instance.
    3. Report the data.

And, I was reasonably sure that I was going to use it more than once and in more than one scenario.

Reading through various SAP blogs; I saw that most of the solutions published used the HANA studio (neat GUI), SAP GUI, Solution Manager, or required creating a DSN on the localhost. However there was one blog that describes how to make a connection to SAP Hana using Net Framework without a DSN. Since, I did not want a DSN this blog was perfect. The only prerequisite was that I install, ‘HANA Database client’ on the machine from where I was connecting.

In order to know, how to install the HANA Database Client, please refer Pages 10 through 13 in the PDF available at SAP HANA Database – Client installation and Update Guide.

Since we have broken down the solution into smaller steps, we have a pretty good idea what we need to tackle first i.e. “Create a connection to a given HANA instance, without using a DSN”. Because, once this is done we are good to go through a familiar process for getting the data.

<# .Synopsis    Get connection to a HANA database instance .DESCRIPTION    Create an OLEDb connection to a HANA database instance using a connection string.    HANA server port is defined by its instance number. When XX is the instance number,    the the port is 3XX15. .EXAMPLE    Get-HDBConnection -ServerName 'hana0001n' -InstanceNumber 61 #>

function Get-HDBConnection
{
    [CmdletBinding()]
    Param
    (
        # Name of the Linux host where HANA is running.
        [Parameter(Mandatory=$true,
                   ValueFromPipelineByPropertyName=$false,
                   Position=0)]
        [string]
        $ServerName,

        # Instance number of your HANA server.
        [Parameter(Mandatory=$true,
                   ValueFromPipelineByPropertyName=$false,
                   Position=1)]
        [int]
        $InstanceNumber
    )

    Begin
    {
        Write-Verbose -Message "Attempting to create connection string to $ServerName on port: " +
                                "3$($InstanceNumber.ToString())15"

        $hdbConnectionString = ""
        $hdbServerName = $("$ServerName" + ":" + "3$($InstanceNumber.ToString())15")

        if( [System.Intptr]::Size -ne 8){
		    #32-bit machine
		    $hdbConnectionString = [string]::Concat($hdbConnectionString, "Driver={HDBODBC32};")
        }else{
		    #64-bit machine.
		    $hdbConnectionString = [string]::Concat($hdbConnectionString, "Driver={HDBODBC32};")
        }
    }
    Process
    {
        $hdbCreds = $host.ui.PromptForCredential("HANA credentials", `
                                                  "Enter credentials for $($hdbServerName)", "", "")

        $hdbConnectionString = [string]::Concat($hdbConnectionString, "ServerNode=", $hdbServerName, ";")
	    $hdbConnectionString = [string]::Concat($hdbConnectionString, "UID=", $($hdbCreds.UserName), ";")
	    $hdbConnectionString = [string]::Concat($hdbConnectionString, "PWD=", `
                                                $($hdbCreds.GetNetworkCredential().Password), ";")

        Write-Verbose -Message "Connection string is: `n $hdbConnectionString"

        $hdbConn = New-Object System.Data.OleDb.OleDbConnection($hdbConnectionString)
        return $hdbConn
    }
    End
    {
    }
}

From here, all we need is to create a DB command that can be executed against the data source and a data adapter to fill the data table.


try
{
$hdbConnection = Get-HDBConnection $hdbServerName
$hdbCmdToRun = New-Object System.Data.OleDb.OleDbCommand($hdbSqlCmd,$hdbConnection)
$hdbCmdToRun.CommandTimeout = '300'
$hdbDataAdapter = New-Object system.Data.OleDb.OleDbDataAdapter($hdbCmdToRun)
$hdbDataTable = New-Object System.Data.DataTable

$hdbConnection.open()
[void]$hdbDataAdapter.fill($hdbDataTable)
$hdbConnection.close()

return $hdbDataTable
}
catch
{
$ex = $_.Exception
Write-Error $ex -Message "[Get-HDBData:Error-Querying-HANA]: " -ErrorAction Stop
}

finally
{
$hdbConnection.Dispose();
}

So, there you have it: TaDa! I know, who knew that connecting to a SAP HANA database was as simple as creating a connection string and using good’ol OLEDB to work the magic.

And here is a screenshot of this code in action.

SAP_HANA_BackupStatus

Getting HANA backup details

 

Advertisements
About

By profession, I’m a SQL Server Database Administrator. I love to poke my nose into different corners and see how stuff looks in there. I keep looking for new things to do as my mind refuses to settle on one topic.

Tagged with: ,
Posted in PowerShell
2 comments on “Connecting to SAP HANA from PowerShell
  1. Nitesh Keswani says:

    Hi I tried to use your code for connecting to a HANA instance from a box where HANA Client is installed. But I get the below error –

    New-Object : Exception calling “.ctor” with “1” argument(s): “An OLE DB Provider was not specified in the ConnectionStr
    ing. An example would be, ‘Provider=SQLOLEDB;’.”
    At C:\Users\C5195080\Desktop\Hana.ps1:30 char:30
    + $hdbConn = New-Object <<<< System.Data.OleDb.OleDbConnection($hdbConnectionString)
    + CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException
    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

    Any advice here?

    • SqlChow says:

      I may have come across this error. I received the error when running x64 version of powershell.exe, when my HANA client is x86 version or vice-versa.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: