port assignment in tenant databases

2477204 - FAQ: SAP HANA Services and Ports

You are interested in details about SAP HANA services and ports.

Environment

SAP HANA, platform edition

SAP HANA service port , KBA , slave , HAN-DB , SAP HANA Database , How To

About this page

Search for additional results.

Visit SAP Support Portal's SAP Notes and KBA Search .

Privacy | Terms of use | Legal Disclosure | Copyright | Trademark

Choose an SAP HANA Database

• How to create an instance of SAP HANA Cloud or SAP HANA, express edition
• How to connect to a SAP HANA Cloud or an SAP HANA, express edition database

Prerequisites

• A Microsoft Windows, Linux, or Mac computer
• A machine that can run SAP HANA, express edition if the SAP HANA Cloud trial or free tier is not used

This tutorial will provide tips and pointers on setting up an instance of SAP HANA running in the cloud or on-premise so that it can then be connected to using a few of the SAP HANA Client interfaces.

For more information on SAP HANA, consult What Is SAP HANA .

Access help from the SAP community or provide feedback on this tutorial by navigating to the Feedback link located on the top right of this page. For connections from additional sources such as SAP Analytics Cloud, Jupyter Notebooks, SAP Business Warehouse, SAP Data Intelligence Cloud, and SAP ERP Central Component see Connection Guides for SAP HANA Cloud . For connections to the SAP HANA Cloud, Data Lake, see the tutorial Use Clients to Query Data Lake Relational Engine . IMPORTANT : Complete the first 3 tutorials, and then you can select any of the following tutorials about connecting with different client interfaces.

There are multiple versions of SAP HANA. The information below is a list of links for the documentation of each version.

Here are a few benefits of using SAP HANA Cloud:

Software updates are automatically applied by SAP.

Hardware is managed by a cloud provider (e.g. AWS, Azure, or GCP).

Many data center locations to choose from as listed in the SAP Discovery Center

Backups are automatic and recovery can be initiated in SAP HANA Cloud Central.

The memory, compute and storage settings can be changed as your needs change. Note a few operations can be performed using service requests .

The ability is provided to expand data storage from in-memory, to native storage extensions, to a data lake, while providing a common access layer that enables you to have further control over performance and cost. See also Lower Your Data Management Costs With SAP HANA Cloud .

Here are a few differences between SAP HANA Cloud and an on-premise version:

Every SAP HANA Cloud instance is one SAP HANA database. An on-premise version can have multiple databases per install, which is known as multi-tenant.

Connections to an SAP HANA Cloud instance must be secure and require a minimum SAP HANA client version of 2.4.167.

The administration user for SAP HANA Cloud is named DBADMIN while for an SAP HANA 2.0 database it is SYSTEM. For additional details see Predefined Users , SAP HANA Cloud Administrator DBADMIN , and Predefined Users in HANA 2.0 .

Information on the instance size steps for SAP HANA Cloud, SAP HANA databases can be found at Create an SAP HANA Database Instance Using SAP HANA Cloud Central . Service plan, pricing and data center availability can be found at SAP HANA Cloud Service (SAP Discovery Center) . Details on limitations can be found at System Limitations . Compatibility information can be found at Compatibility with Other SAP HANA Versions .

To complete the tutorials in the mission, an SAP HANA instance is needed. Step 3 and 5 in this tutorial provide two different, free options that can be used to set up an SAP HANA instance. Only one of these steps needs to be completed if you currently do not have access to an SAP HANA instance.

The instructions on how to setup a free SAP HANA Cloud trial or free tier within the SAP Business Technology Platform (SAP BTP), are well covered in a number of other sources listed below. Trial is only available on the US10 landscape and is in a separate SAP BTP trial account whereas free tier is available in multiple production SAP BTP accounts and provides a seamless transition from a free tier to a paid plan.

Set Up Your SAP HANA Cloud, SAP HANA Database (free tier or trial) and Understand the Basics

SAP Learning Journey - Provisioning and Administering Databases in SAP HANA Cloud

SAP Discovery Center - SAP HANA Cloud, SAP HANA Database Fundamentals

Help Thomas Get Started with SAP HANA

For more information on SAP BTP see the following product pages and help documentation:

https://www.sap.com/products/business-technology-platform.html

https://developers.sap.com/topics/business-technology-platform.html

https://help.sap.com/docs/btp

Continue with this tutorial once you have created an SAP HANA Cloud trial or free tier instance as shown below.

Once the SAP HANA Cloud instance is created, take note of the SQL endpoint (host:port) needed to connect to the database. The endpoint can be obtained via the copy menu item. This will be needed in subsequent tutorials in the mission.

The SAP HANA Cloud, HANA database free tier or trial instances are shut down on a nightly basis and will need to be restarted before working with them the next day.

Open a SQL console for your database instance from SAP HANA Cloud Central.

If needed, you will be asked to enter the credentials for the database. Enter DBADMIN and the password that was specified when the instance was created.

The SAP HANA database explorer provides the ability to browse catalog objects and execute SQL statements from the SQL console. For more information, see the tutorial group Get Started with the SAP HANA Database Explorer , SQL Console and SAP HANA Database Explorer .

Run the following query to see the name of the database you are currently connected to.

A schema is a container for other database objects such as tables and views and the current schema can be seen via SQL as shown below. The DUMMY table is available in every HANA database that has one column and one row. It provides a convenient way to call a function or perform a simple test.

To see the list of services, enter the following SQL statement:

To see information about the database server, enter the following SQL statement:

Views starting with M_ are examples of monitoring views and contain statistics and status details.

Congratulations! You have connected to SAP HANA Cloud and performed a few queries.

This step only needs to be completed if you currently do not have access to an SAP HANA Instance and did not setup an SAP HANA instance through the SAP HANA Cloud Trial or free tier as explained in step 3.

SAP provides a free streamlined version of SAP HANA that runs on developer laptops called SAP HANA, express edition .

SAP HANA runs on a few versions of Linux. SAP HANA, express edition provides a binary install as well as virtual machine images that can be run on Microsoft Windows, macOS and Linux machines. This is described in the Getting Started with SAP HANA 2.0, express edition (Binary Installer Method) or Getting Started with SAP HANA 2.0, express edition (Virtual Machine Method) . A database-only option and a database + XS Advanced Applications option are available. The database + XS Advanced Applications install includes the SAP HANA cockpit, the SAP HANA database explorer, and the SAP HANA Web IDE for SAP HANA.

Further details can be found in the release notes .

Choose an option and proceed to install SAP HANA, express edition. The server-only install, which requires less memory, is sufficient to complete the tutorials in the mission. Be sure to note the SAP HANA database master password, as it will be needed later for connecting.

At this point, you should have a running instance of SAP HANA, express edition.

A default installation will contain one system database named SYSTEMDB and one tenant database named HXE .

The system database contains information about the tenant database(s) and is used for overall administration. The default port for the system database is 39013 . Later in this tutorial mission, a connection to the tenant database will be made and tables will be created and populated with data. For more information, see SAP HANA Tenant Databases Operations Guide .

The SAP HANA, express edition VM install includes a command line tool (part of the SAP HANA client install) called HDBSQL that can be used to query the database.

The following steps will demonstrate connecting to and examining a SAP HANA, express edition database.

Enter the following to connect to the system database:

Notice that the database being connected to is SYSTEMDB.

The example above uses localhost since hdbsql is running on the same machine that the database is trying to connect to. The host name, hxehost , is seen in the terminal above.

If hdbsql is not found and you are logged in with another user, try connecting as the user hxeadm and run the command again. Shell Copy su hxeadm If the hdbsql command cannot be found, simply read through the following examples. The installation and further examples of running HDBSQL will be covered in subsequent tutorials.

Determine the IP address of the machine that is running SAP HANA, express edition. Record the value as it will be needed in later tutorials in the mission. It is important to note the IP address or host name of the machine, as this will be needed in later tutorials.

The following are a some examples of interactive options followed by a SQL query, which when run against SYSTEMDB returns information about the databases running on the SAP HANA instance.

The \al or align interactive option when enabled increases the readability of the output.

The \s command shows status information. It shows that that the SAP System Identification (SID) is HXE , that the connected database is SYSTEMDB , and the currently logged in user is SYSTEM .

The result of the SELECT against the M_SERVICES table shows that there are two databases, named SYSTEMDB and HXE and that they are accessible on ports 39013 and 39015 .

The ‘\serverstats’ retrieves resource consumption information about the last executed SQL statement which can be helpful when diagnosing SQL statements.

The instance number can be derived from a port number using the second and third numbers in the port number 39013. In this case, the instance number is 90.

If the SQL statement returns more than one screen of text, entering a space will show the next screen of results. See also the pager option \pa .

Enter one of the below commands to exit from viewing the results of the select statement. Note that commands can be prefixed with a forward slash(\) or a colon(:).

Connect using the instance number and database name as shown below.

Enter the following to display database connection information.

Notice that this time the database connected to is HXE.

For further information, see Port Assignment in Tenant Databases and Connecting to SAP HANA Databases and Servers .

Congratulations! You now have access to an SAP HANA instance and understand some of the differences between SAP HANA Cloud and SAP HANA, express edition.

Which of the following statements are true? You can select more than one answer.

• With an SAP HANA, express edition, software updates are automatically applied by SAP
• With an SAP HANA Cloud instance, software updates are automatically applied by SAP
• SAP HANA, express edition can be run on developer laptops for free
• SAP HANA Cloud is available as a trial or free tier
• Overview of SAP HANA Cloud and SAP HANA, express edition
• SAP HANA Cloud
• Connect to SAP HANA Cloud
• SAP HANA, express edition
• Connect to SAP HANA, express edition
• Knowledge check

Challenge 7: Analytical Dashboard Using SAP S/4 Hana DB

< Previous Challenge - Home

Introduction

Contoso inc. is a manufacturing company planning to modernize its IT infrastructure. As a part of that efforts, they want to Generate self-service report from SAP HANA DB tables using Microsoft power BI desktop client and develop a self-service report for visualization and analysis.

Description

This exercise includes connecting power BI desktop client to HANA DB (using direct query with DB user and password), Extract data from the required tables (using semantic SQL query) and Generate self-service Job status report.

A high level architectural diagram can be found below.

Download and install SAP HANA client for windows from SAP Service Market Place.

Download Power BI desktop from windows power app platfrom.

Leverage SAP HANA studio (from Challenge 4) to create the user and required roles and previleges to execute the select query to populate the power BI desktop client.

For the HANA DB create the SCHEMA user with admin rights on SAP SCHEMA and assign appropriate roles and privileges to extract the data.

New user ID in HANA is able to connect to Tenant database where SAP application DATA is residing including the SAP Job log table TBTCO.

NOTE: Below link gives you more details on what is system DB and tenant DB in SAP HANA database.

SAP HANA Tenant database

Run the query both in System DB and Tenant DB to indentify the SQL port to connect from Power BI to SAP HANA.

NOTE: All the details will be found how to query the port both from SYSTEM DB and Tenant DB in SAP HANA from the below link.

Port Assignment in Tenant Databases in SAP HANA Database

SAP HANA ports will be 3NN40-3NN99, NN is the system number for HANA.

Leverage direct query for data connectivity Mode.

Report should have a matrix, a pie chart and a bar chart.

SAP HANA database has to be up and running when participants try to refresh the data from Power BI desktop.

Success Criteria

• Create PowerBI Dashboard displaying data from SAP HANA DB.
• Generate self-service report from SAP HANA DB tables using Microsoft power BI desktop client and develop a self-service report for visualization and analysis.
• Publish the visualization from Power BI desktop to power BI Service for broader user community.

Learning Resources

Use SAP HANA in Power BI - Power BI

Power Query SAP HANA database connector

Direct Query in Power BI DirectQuery for SAP HANA in Power BI - Power BI

Bring your SAP HANA data to life with Microsoft Power BI Bring your SAP HANA data to life with Microsoft Power BI

SAP HANA DB administration Database Administration Tasks at a Glance

SAP Blog on connect Microsoft power BI desktop to HANA Connect Microsoft Power BI Desktop to a HANA System in SCP using SAP Cloud Connector Service Channels

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Learn how to provision new tenants and register them in the catalog

• 7 contributors

In this tutorial, you learn how to provision and catalog SaaS patterns. You also learn how they're implemented in the Wingtip Tickets SaaS database-per-tenant application. You create and initialize new tenant databases and register them in the application's tenant catalog. The catalog is a database that maintains the mapping between the SaaS application's many tenants and their data. The catalog plays an important role in directing application and management requests to the correct database.

In this tutorial, you learn how to:

• Provision a single new tenant.
• Provision a batch of additional tenants.

To complete this tutorial, make sure the following prerequisites are completed:

• The Wingtip Tickets SaaS database-per-tenant app is deployed. To deploy it in less than five minutes, see Deploy and explore the Wingtip Tickets SaaS database-per-tenant application .
• Azure PowerShell is installed. For more information, see Get started with Azure PowerShell .

Introduction to the SaaS catalog pattern

In a database-backed multitenant SaaS application, it's important to know where information for each tenant is stored. In the SaaS catalog pattern, a catalog database is used to hold the mapping between each tenant and the database in which their data is stored. This pattern applies whenever tenant data is distributed across multiple databases.

Each tenant is identified by a key in the catalog, which is mapped to the location of their database. In the Wingtip Tickets app, the key is formed from a hash of the tenant's name. This scheme allows the app to construct the key from the tenant name included in the application URL. Other tenant key schemes can be used.

The catalog allows the name or location of the database to be changed with minimal impact on the application. In a multitenant database model, this capability also accommodates moving a tenant between databases. The catalog also can be used to indicate whether a tenant or database is offline for maintenance or other actions. This capability is explored in the Restore single tenant tutorial .

The catalog also can store additional tenant or database metadata, such as the schema version, service plan, or SLAs offered to tenants. The catalog can store other information that enables application management, customer support, or DevOps.

Beyond the SaaS application, the catalog can enable database tools. In the Wingtip Tickets SaaS database-per-tenant sample, the catalog is used to enable cross-tenant query, which is explored in the Ad hoc reporting tutorial . Cross-database job management is explored in the Schema management and Tenant analytics tutorials.

In the Wingtip Tickets SaaS samples, the catalog is implemented by using the Shard Management features of the Elastic Database client library (EDCL) . The EDCL is available in Java and the .NET Framework. The EDCL enables an application to create, manage, and use a database-backed shard map.

A shard map contains a list of shards (databases) and the mapping between keys (tenants) and shards. EDCL functions are used during tenant provisioning to create the entries in the shard map. They're used at run time by applications to connect to the correct database. EDCL caches connection information to minimize traffic to the catalog database and speed up the application.

The mapping data is accessible in the catalog database, but don't edit it . Edit mapping data by using Elastic Database Client Library APIs only. Directly manipulating the mapping data risks corrupting the catalog and isn't supported.

Introduction to the SaaS provisioning pattern

When you add a new tenant in a SaaS application that uses a single-tenant database model, you must provision a new tenant database. The database must be created in the appropriate location and service tier. It also must be initialized with the appropriate schema and reference data. And it must be registered in the catalog under the appropriate tenant key.

Different approaches to database provisioning can be used. You can execute SQL scripts, deploy a bacpac, or copy a template database.

Database provisioning needs to be part of your schema management strategy. You must make sure that new databases are provisioned with the latest schema. This requirement is explored in the Schema management tutorial .

The Wingtip Tickets database-per-tenant app provisions new tenants by copying a template database named basetenantdb , which is deployed on the catalog server. Provisioning can be integrated into the application as part of a sign-up experience. It also can be supported offline by using scripts. This tutorial explores provisioning by using PowerShell.

Provisioning scripts copy the basetenantdb database to create a new tenant database in an elastic pool. The tenant database is created in the tenant server mapped to the newtenant DNS alias. This alias maintains a reference to the server used to provision new tenants and is updated to point to a recovery tenant server in the disaster recovery tutorials ( DR using georestore , DR using georeplication ). The scripts then initialize the database with tenant-specific information and register it in the catalog shard map. Tenant databases are given names based on the tenant name. This naming scheme isn't a critical part of the pattern. The catalog maps the tenant key to the database name, so any naming convention can be used.

Get the Wingtip Tickets SaaS database-per-tenant application scripts

The Wingtip Tickets SaaS scripts and application source code are available in the WingtipTicketsSaaS-DbPerTenant GitHub repo. Check out the general guidance for steps to download and unblock the Wingtip Tickets SaaS scripts.

Provision and catalog detailed walkthrough

To understand how the Wingtip Tickets application implements new tenant provisioning, add a breakpoint and follow the workflow while you provision a tenant.

In the PowerShell ISE, open ...\Learning Modules\ProvisionAndCatalog\ Demo-ProvisionAndCatalog.ps1 and set the following parameters:

• $TenantName = the name of the new venue (for example, Bushwillow Blues ).
• $VenueType = one of the predefined venue types: blues, classicalmusic, dance, jazz, judo, motor racing, multipurpose, opera, rockmusic, soccer .
• $DemoScenario = 1 , Provision a single tenant .

To add a breakpoint, put your cursor anywhere on the line that says New-Tenant ` . Then press F9.

To run the script, press F5.

After the script execution stops at the breakpoint, press F11 to step into the code.

Trace the script's execution by using the Debug menu options. Press F10 and F11 to step over or into the called functions. For more information about debugging PowerShell scripts, see Tips on working with and debugging PowerShell scripts .

You don't need to explicitly follow this workflow. It explains how to debug the script.

Import the CatalogAndDatabaseManagement.psm1 module. It provides a catalog and tenant-level abstraction over the Shard Management functions. This module encapsulates much of the catalog pattern and is worth exploring.

Import the SubscriptionManagement.psm1 module. It contains functions for signing in to Azure and selecting the Azure subscription you want to work with.

Get configuration details. Step into Get-Configuration by using F11, and see how the app config is specified. Resource names and other app-specific values are defined here. Don't change these values until you are familiar with the scripts.

Get the catalog object. Step into Get-Catalog, which composes and returns a catalog object that's used in the higher-level script. This function uses Shard Management functions that are imported from AzureShardManagement.psm1 . The catalog object is composed of the following elements:

• $catalogServerFullyQualifiedName is constructed by using the standard stem plus your user name: catalog-<user>.database.windows .net .
• $catalogDatabaseName is retrieved from the config: tenantcatalog .
• $shardMapManager object is initialized from the catalog database.
• $shardMap object is initialized from the tenantcatalog shard map in the catalog database. A catalog object is composed and returned. It's used in the higher-level script.

Calculate the new tenant key. A hash function is used to create the tenant key from the tenant name.

Check if the tenant key already exists. The catalog is checked to make sure the key is available.

The tenant database is provisioned with New-TenantDatabase. Use F11 to step into how the database is provisioned by using an Azure Resource Manager template .

The database name is constructed from the tenant name to make it clear which shard belongs to which tenant. You also can use other database naming conventions. A Resource Manager template creates a tenant database by copying a template database ( baseTenantDB ) on the catalog server. As an alternative, you can create a database and initialize it by importing a bacpac. Or you can execute an initialization script from a well-known location.

The Resource Manager template is in the …\Learning Modules\Common\ folder: tenantdatabasecopytemplate.json

The tenant database is further initialized. The venue (tenant) name and the venue type are added. You also can do other initialization here.

The tenant database is registered in the catalog. It's registered with Add-TenantDatabaseToCatalog by using the tenant key. Use F11 to step into the details:

• The catalog database is added to the shard map (the list of known databases).
• The mapping that links the key value to the shard is created.
• Additional metadata about the tenant (the venue's name) is added to the Tenants table in the catalog. The Tenants table isn't part of the Shard Management schema, and it isn't installed by the EDCL. This table illustrates how the catalog database can be extended to support additional application-specific data.

After provisioning completes, execution returns to the original Demo-ProvisionAndCatalog script. The Events page opens for the new tenant in the browser.

Provision a batch of tenants

This exercise provisions a batch of 17 tenants. We recommend that you provision this batch of tenants before starting other Wingtip Tickets SaaS database-per-tenant tutorials. There are more than just a few databases to work with.

In the PowerShell ISE, open ...\Learning Modules\ProvisionAndCatalog\ Demo-ProvisionAndCatalog.ps1 . Change the $DemoScenario parameter to 3:

• $DemoScenario = 3 , Provision a batch of tenants .

The script deploys a batch of additional tenants. It uses an Azure Resource Manager template that controls the batch and delegates provisioning of each database to a linked template. Using templates in this way allows Azure Resource Manager to broker the provisioning process for your script. The templates provision databases in parallel and handle retries, if needed. The script is idempotent, so if it fails or stops for any reason, run it again.

Verify the batch of tenants that successfully deployed

In the Azure portal , browse to your list of servers and open the tenants1 server. Select SQL databases , and verify that the batch of 17 additional databases is now in the list.

Other provisioning patterns

Other provisioning patterns not included in this tutorial:

Pre-provisioning databases : The pre-provisioning pattern exploits the fact that databases in an elastic pool don't add extra cost. Billing is for the elastic pool, not the databases. Idle databases consume no resources. By pre-provisioning databases in a pool and allocating them when needed, you can reduce the time to add tenants. The number of databases pre-provisioned can be adjusted as needed to keep a buffer suitable for the anticipated provisioning rate.

Auto-provisioning : In the auto-provisioning pattern, a provisioning service provisions servers, pools, and databases automatically, as needed. If you want, you can include pre-provisioning databases in elastic pools. If databases are decommissioned and deleted, gaps in elastic pools can be filled by the provisioning service. Such a service can be simple or complex, such as handling provisioning across multiple geographies and setting up geo-replication for disaster recovery.

With the auto-provisioning pattern, a client application or script submits a provisioning request to a queue to be processed by the provisioning service. It then polls the service to determine completion. If pre-provisioning is used, requests are handled quickly. The service provisions a replacement database in the background.

In this tutorial you learned how to:

• Step into the details of provisioning tenants and registering them into the catalog.

Try the Performance monitoring tutorial .

Additional resources

• Additional tutorials that build on the Wingtip Tickets SaaS database-per-tenant application
• Elastic database client library
• Debug scripts in the Windows PowerShell ISE

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for