Transcript SAP HANA - Best Practices for Modeling, Administrating, Operating

HANA - Best Practices for
Modeling, Administrating,
Operating and Monitoring
Dr. Bjarne Berg
This Presentation Comprises:
• Key Responsibilities of Setup and Maintenance
• Modeling in HANA and Virtualizing Data Layers
• MCOD, MCOS, Virtualization and MDC HANA Deployment Options
• Active and Passive Monitoring of HANA
• Conclusion
Key Responsibilities for HANA Admin and Installs
When to Update HANA
• SAP hardware partners ship HANA pre-configured and with the most recent
support package stack at the time when the HANA appliance is shipped
• The customer is responsible for the subsequent implementation of HANA
patches, revisions, or support packages, and support package stacks
• Systems that were installed with the HANA Unified Installer can use the
automated update procedure
• This requires a backup be completed, data replication to be suspended, and
the business made aware of the planned outage
It is recommended that you do this on a quarterly basis, or when other
systems are scheduled for maintenance at the same time (i.e., holidays)
Updating HANA Studio
• You can choose to update
the software
automatically based on
periodic updates with
SUM or execute the
software update
manually
• The Lifecycle
Management perspective
of the HANA Studio is
updated when you
update HANA Studio
To update the HANA studio, choose Help > Check for Updates
HANA System Monitoring Key Resources
Area
Tool
HANA Administration guide
System
Admin
Purpose
- How to use the HANA cockpit and HANA studio for system admin.
- Core functions of high-availability, disaster recovery & scalability
tinyurl.com/AdminHana
- Security administration
- How to manage and monitor applications for data provisioning and
custom applications built in the extended services (XS) framework.
Tool to manage system landscape connections and central
management of DB configurations
HANA Troubleshooting and
How to trouble shoot and fix DB performance issues and guidance
Performance Analysis Guide on general optimization.
How to monitor, setup and manage systems that have HANA
Multitenant DB Guide
multitenant DBs
Landscape Technical Operations Manual How to operate and administrate a HANA landscape.
Admin SAP DB Control Center (DCC) Guide on how to use DCC to monitor HANA and other databases
DBA Cockpit for HANA
Web Resource
tinyurl.com/DBACockpit
tinyurl.com/TroubleGuide
tinyurl.com/HanaDBs
tinyurl.com/TechOperations
tinyurl.com/databaseCC
This Presentation Comprises:
• Key Responsibilities of Setup and Maintenance
• Modeling in HANA and Virtualizing Data Layers
• MCOD, MCOS, Virtualization and MDC HANA Deployment Options
• Active and Passive Monitoring of HANA
• Conclusion
View Types in SAP HANA — Attribute Views
• Attribute views
consist of one or
more tables and are
used to qualify the
data in some way
• Attribute views are
the basic building
blocks in the SAP
HANA Studio
modeler
These views are reusable and are somewhat comparable to dimensions and master data in SAP Business
Warehouse (BW). Most attribute views are built on master data, but are not technically restricted to this.
View Types in HANA — Analytic Views
• Analytic views bring
transactional data
and attribute views
together
• Typically, this
involves simply
dragging one or
more attribute views
into the logical join
in the Scenario pane
and then adding
transactional data to
the Data Foundation
Once this is done, the attribute, the views, and the data in the Data Foundation can be
joined by clicking and dragging the fields you want to join from the various views and tables
View Types in HANA — Calculation Views
HANA Live views on top of
BusinessSuite on HANA, is basically
800+ views pre-built by SAP for
your use in real-time operational
reporting and analytics
HANA Live Calculation views in
the query category, is intended
for you to use in BusinessObjects
reporting and dashboarding tools
This Presentation Comprises:
• Key Responsibilities of Setup and Maintenance
• Modeling in HANA and Virtualizing Data Layers
• MCOD, MCOS, Virtualization and MDC HANA Deployment Options
• Active and Passive Monitoring of HANA
• Conclusion
Save Money with MCOD and MCOS
1. May not need separate hardware for sandbox & development environments
2.Using Multiple Components One Database (MCOD) and/or Multiple
Components One System (MCOS) you can simplify the number of hardware
environments you need
a) SAP BW on SAP HANA
b) SAP Finance and Controlling Accelerator for the material ledger
c) ERP operational reporting with SAP HANA
d) SAP Finance and Controlling Accelerator: Production Cost Planning
e) SAP Rapid Marts
f) SAP COPA Accelerator
g) SAP Operational Process Intelligence
h) SAP Cash Forecasting
i) SAP Application Accelerator/Suite Accelerator
j) Smart Meter Analytics
In addition to custom developed datamarts, all items above can run
in an MCOD setup (see SAP Note 1666670 for more details)
HANA MCOS Example from Real Company - BW Landscape
MCOS
Note that the QA and Production system are kept the same size so that performance tests
are accurate and so that the QA system can be used as for disaster recovery
NEW: Multitenant Database Containers (MDC) Deployments
•A tenant database is a single database
container
•You can save money by running
multiple tenant databases on a single
HANA system
•MDC is supported for production
systems and you can backup for each
tenant database
MDC became available with SP-9 of HANA in 2015
•You can manage resources such as
memory and CPU for each of the
tenant databases
MDC Deployments Details
•Can be used in Platform & Enterprise Cloud
•For on-premise it can replace most MCOS
deployments and many of the MCOD scenarios
•There is no virtualization overhead, and scaleout systems with standby nodes is supported
•You can use SQL to query across databases:
Individual database backups and restores
can be done from HANA Studio
• New
• You
SELECT *
FROM schema1.Customers AS tab1, db2.schema2.Customers as tab2
WHERE tab2.column2 = ‘Johnson’
NOTE: Attribute and analytic views must be converted to calculation views
to be used as remote tenant database objects
privilege “Database Admin” can separate admin access to each DB
can convert a HANA system to MDC, but it cannot be converted back
(command: hdbnsutil –convertToMultiDB)
MDC Monitoring
The HANA
cockpit can
monitor both
the system
and tenant
databases
You can connect to any system and
tenant database using HANA studio.
The DBA Cockpit
can also be used
to monitor
databases
This Presentation Comprises:
• Key Responsibilities of Setup and Maintenance
• Modeling in HANA and Virtualizing Data Layers
• MCOD, MCOS, Virtualization and MDC HANA Deployment Options
• Monitoring and Core Admin Tasks in HANA
• Conclusion
Monitoring with Admin Console in HANA Studio
System Landscape
• The Landscape tab verifies that the system is running and displays
the status of the relevant services:
• For each server within the HANA system, the following services
should be running:
•
•
•
nameserver
indexserver
preprocessor
•
•
•
statisticsserver
sapstartsrv
xsengine
If a distributed system is being used, this can be configured under the Configuration subtab
HANA Cockpit Monitoring in Fiori
The DBA Cockpit – HANA Monitoring
SAP Landscape and Virtualization Manager (LVM)
Monitoring with Alerts
• The statistics server is the tool used for monitoring in HANA and offers
real-time system resource alerts on vital information. There are 83 Alerts
available.
• Server crashes
or stoppages
• Hard disk
reaching critical
capacity
• CPU at risk of
experiencing
bottlenecks or
high stress
Configuring Alerts
• Customer Alerts can be created to assist in monitoring system performance
in the Administrator Editor under the Alerts tab
• Creating an administrative e-mail account is recommended in order to
isolate system monitoring information
• The recipients of alerts can be optionally modified to inform those who
should receive alert notifications instead of targeting different alerts to
specific email addresses
• Each alert has three specific thresholds for when the alert can be executed:
High, Medium, Low
The values for these thresholds can be defined as percentages. The scheduled times for when
the alerts should be triggered can also be set, the default is every six hours once a day.
Monitoring Availability with Alerts
Check
Type
Availability
ID
Time
Description
0
Intra-day
Identifies internal statistics server problem.
3
Intra-day
Identifies inactive services.
4
Intra-day
Restarted Services- services that have
restarted since the last time of the check.
21
Daily
22
Intra-day
23
Intra-day
24
Intra-day
31
Daily
41
Daily
70
Periodic
78
Daily
80
As
needed
Identifies internal DB events.
SAP Recommended Admin Action
Resolve the problem. For more information, see the
trace files. You may need to activate tracing first.
Investigate why the service is inactive, for example, by
checking the service's trace files.
Investigate why the service had to restart or be
restarted, for example, by checking service's trace files.
Resolve the event and then mark it as resolved by
executing the SQL statement ALTER SYSTEM SET EVENT
HANDLED '<host>:<port>' <id>.
Notification of all alerts- if any alerts since
the last check is triggered
Notification of medium and high priority
alerts- since the last check is triggered
Notification of high priority alerts- since the
last check is triggered
License expiry-If the disks to which data
and log files are written are full. A disk-full
event causes DB to stop
These alerts can trigger email blasts to specified
recipients. Investigate the alerts.
Obtain a valid license and install it. For the expiration
date, see the monitoring view M_LICENSE.
In-memory DataStore activation- If a
problem with the activation of an inmemory DataStore object exists
Consistency of internal system components
after system upgrade
For more information, see the table
Connection between systems in system
replication setup- closed connections
between primary/ secondary system.
If connections are closed, the primary system is no
longer being replicated. Investigate why connections are
closed (i.e., network problem) and resolve the issue.
Availability of asynchronous table
replication- Monitors error messages
related to asynch table replication.
Determine which tables encountered the table
replication error using system view
M_ASYNCHRONOUS_TABLE_REPLICAS, and check the
corresponding indexserver alert traces.
_SYS_STATISTICS.GLOBAL_DEC_EXTRACTOR_STATUS
and SAP Note
1665553.
Contact SAP support.
Monitoring Backups with Alerts
Check
Type
ID
28
Back-up
32
Time
Description
SAP Recommended Admin Action
Periodic
Most recent savepoint operation- How long ago the last
savepoint was defined, that is, how long ago a complete,
consistent image of the DB was persisted to disk.
Investigate why there was a delay defining the last savepoint and consider triggering
the operation manually by executing the SQL statement ALTER SYSTEM SAVEPOINT.
Periodic
Log mode LEGACY- If the DB is running in log mode "legacy". Log
mode "legacy" does not support point-in-recovery and is not
recommended for productive systems.
If you need point-in-time recovery, reconfigure the log mode of your system to
"normal". In the "persistence" section of the global.ini configuration file, set the
parameter "log_mode" to "normal" for the System layer. When you change the log
mode, you must restart the DB system to activate the changes. It is also recommended
that you perform a full data backup.
33
Periodic
35
36
37
Daily
Daily
Daily
38
Daily
54
Periodic
65
As needed
66
As needed
69
Periodic
72
Daily
Log mode OVERWRITE- If the DB is running in log mode
"overwrite". Log mode "overwrite" does not support point-inrecovery (only recovery to data backup) and is not
recommended for prod systems.
Existence of data backup
Status of most recent data backup
Age of most recent successful data backup
Status of most recent log backups- If the most recent log backups
for services and volumes were successful.
Savepoint duration- Identifies long-running savepoint
operations.
Runtime of the log backups currently running- If the most recent
log backup terminates in the given time.
Storage snapshot is prepared- if the period, during the DB is
prepared for a storage snapshot, exceeds threshold.
Enablement of automatic log backup- if automatic log backup is
enabled.
Number of log segments- segments in the log volume of each
service Check for number of log segments. Make sure that log
backups are being auto created and that there is enough space
Investigate why the service had to restart or be restarted, for example, by checking
service's trace files.
Perform a data backup as soon as possible.
Investigate why failed, resolve the problem, and perform a new data backup as soon
as possible.
Perform a data backup as soon as possible.
Investigate why the log backup failed and resolve the problem.
Check disk I/O performance.
Investigate why the log backup runs for too long, and resolve the issue.
Investigate why the storage snapshot was not confirmed or abandoned, and resolve
the issue.
Enable automatic log backup. For more details please see HANA Administration Guide.
Check whether the system has been frequently and unusually restarting services. If it
has, then resolve the root cause of this issue and create log backups as soon as
possible.
Monitoring Configuration and CPU with Alerts
Check
Type
ID
Time
3
As needed
Discrepancy between host server times- discrepancies in a scale-out system.
10
Periodic
Delta merge (mergdog) configuration- If the 'active' parameter in the
'mergedog' section of system configuration file(s) is 'yes'.
16
Periodic
Lock wait timeout configuration- if 'lock_waittimeout' parameter in
'transaction' section of indexserver.ini file is between 100,000 and 7,200,000.
26
Periodic
Unassigned volumes- Identifies volumes that are not assigned a service.
34
Daily
79
Periodic
5
Intra-day
Configur
ation
CPU
Description
If all volumes are available.
Configuration consistency of systems in system replication setup- Identifies
configuration parameters that do not have the same value on the primary
system and a secondary system.
Host CPU Usage- Determines the % CPU idle time on the host and therefore if
CPU resources are running low.
SAP Recommended Admin Action
Check operating system time settings.
mergedog is the system process that periodically checks column tables
to determine if a delta merge operation needs to be executed. Change
in SYSTEM layer the parameter active in section(s) mergedog to yes
In the 'transaction' section of the indexserver.ini file, set the
'lock_wait_timeout' parameter to a value between 100,000 and
7,200,000 for the System layer.
Investigate why the service had to restart or be restarted, for example,
by checking service's trace files.
Investigate why the volume is not assigned a service. I.e.., assigned
service is not active, the removal of a host failed, or the service removal
was performed incorrectly.
Investigate why the volume is not available.
The identified configuration parameter(s) should have the same value in
both systems, adjust the configuration. If different values are acceptable,
add the parameter(s) as an exception in global.ini/[inifile_checker].
Investigate CPU usage
Monitoring Files and Disk Usage with Alerts
Check
Type
ID
Time
46
As needed
50
Periodic
51
SAP Recommended Admin Action
RTEdump files- Identifies new runtime dump files (*rtedump*) have been
generated in the trace directory.
These files These contain information about, for example, build, loaded modules,
running threads, CPU, etc..Check contents of the dump files.
Number of diagnosis files- written by the system (excluding zip-files).
A large number of files can indicate a problem with the DB (i.e., problem with trace
file rotation or a high number of crashes). Investigate the diagnosis files.
Daily
Size of diagnosis files- very large file sizes can indicate a problem with DB.
Check the diagnosis files in the HANA studio for details.
52
Daily
Crashdump files- new files that have been generated in the trace directory
53
Daily
Pagedump files- new files that have been generated in the trace directory
56
Periodic
Python trace activity- If trace is active and for how long. Trace affects
performance.
If no longer required, deactivate the python trace in the relevant configuration file.
2
Intra-day
Disk Usage- Determines what % of each disk containing data, log, and trace files
is used. This includes space used by non-HANA files.
Investigate disk usage of processes. Increase disk space, for example by shrinking
volumes, deleting diagnosis files, or adding additional storage.
30
Intra-day
Check internal disk full event- If the disks to which data and log files are written
are full. A disk-full event causes your DB to stop and must be resolved.
Resolve the disk-full event: In the Admin Editor on the Overview tab, choose the
\"Disk Full Events\" link and mark the event as handled. Alternatively, execute the
SQL statements ALTER SYSTEM SET EVENT ACKNOWLEDGED '<host>:<port>' <id> and
ALTER SYSTEM SET EVENT HANDLED '<host>:<port>'<id>.
60
Periodic
Sync/Async read ratio- Identifies a bad trigger asynchronous read ratio.
61
Periodic
Sync/Async write ratio- Identifies a bad trigger asynchronous write ratio.
This means that asynchronous reads are blocking and behave almost like
synchronous reads. This might have negative impact on HANA I/O performance in
certain scenarios. Note 1930979.
77
Intra-day
DB disk usage- The total used disk space of the DB. All data, logs, traces and
backups are considered.
Investigate the disk usage of the DB. See system view M_DISK_USAGE for more
details.
Diag-nosis
Files
Description
Check the contents of the dump files.
Disk
Monitoring Memory Usage
• Memory in HANA is consumed for a variety of purposes:
• The operating systems and support files
• Proprietary code and stack of program files
• Column and row stores where data is stored
• Working space where computations occur, temporary results are stored, and shared user
memory consumption occurs
• HANA tracks memory from the perspective of the host. The
most important aspects are the following:
Physical memory – The max amount of physical (system) memory available on the host
Allocated memory – The memory pool reserved by HANA from the operating system
Used memory – The amount of memory from the pool that is actually used by HANA DB
HANA Memory Usage
• The physical memory on most hosts is from 256Gb -3 TB
• This is used to run the Linux OS, HANA, and any
additional programs that run on the host
• SQL statements can be used to obtain or edit memory
information. There is a set of predefined SQL statements
provided by SAP.
• Used memory include:
Program code and stack
Working space and data tables (heap and shared memory)
• The program code area houses the HANA database while
it is active. Various parts of HANA can share a common
program code.
• The stack is required to complete actual computations
Monitoring Memory with Alerts
Check Type
ID
1
Memory
Time
Intra-day
Description
Host physical memory usage- The % of total physical memory available on the
host
Row store fragmentation
Memory usage of name server- Determines what % of allocated shared
memory is being used by the name server on a host.
SAP Recommended Admin Action
All processes consuming memory are considered, including non-HANA processes. Investigate memory usage of
processes.
Implement SAP Note 1813245.
Increase the shared memory size of the name server. In the 'topology' section of the nameserver.ini file, increase the
value of the 'size' parameter.
3
Periodic
12
Intra-day
17
Periodic
Record count of non-partitioned column-store tables- Current table size is not
critical.
20
Periodic
Table growth rate of non-partitioned column-store table
27
Periodic
Record count of column-store table partitions
29
Periodic
Size of delta storage of column-store tables
Investigate the delta merge history in the monitoring view M_DELTA_MERGE_STATISTICS. Consider merging the table
delta manually.
40
Daily
Total memory usage of column-store tables- The % of the effective alloc limit
being consumed by individual column-store tables as a whole
This is the cumulative size of all of a table's columns and internal structures. Consider partitioning or repartitioning the
table.
43
Daily
Memory usage of services- % of effective alloc limit a service is using.
Check for services that consume a lot of memory.
44
Periodic
Licensed memory usage- % used.
Increase licensed amount of main memory. See the peak memory allocation since installation in the system view
M_LICENSE, column PRODUCT_USAGE
45
Periodic
Memory usage of main storage of column-store tables- % of effective alloc
limit consumed by column-store tables.
Consider partitioning or repartitioning the table.
55
Periodic
Columnstore unloads- # of columns that have been unloaded from memory.
Can indicate performance issues. Check sizing with respect to data distribution.
58
As needed
67
Periodic
Table growth of rowstore tables
Increase the size of the plan cache. In the 'sql' section of the indexserver.ini file, increase the value of the
'plan_cache_size' parameter.
Reduce the size by removing unused data
68
Periodic
Total memory usage of row store used by a service
Investigate memory usage by row store tables and consider cleanup of unused data
73
Periodic
Overflow ratio of rowstore version space.
74
Periodic
Overflow ratio of metadata version space.
75
Periodic
Rowstore version space skew- if rowstore version chain is too long.
81
Periodic
Cached view size- how much memory is occupied by cached view
Plan cache size- if the plan cache is too small.
Partitioning need only be considered if tables are expected to grow rapidly. A non-partitioned table cannot contain
more than 2,000,000,000 (2 billion) rows). Consider partitioning the table only if you expect it to grow rapidly.
Identify the connection or transaction that is blocking version garbage collection. You can do this in the HANA studio by
executing the "MVCC Blocker Connection" and "MVCC Blocker Transaction" statements available on the System
Information tab of the Administration editor. If possible, kill the blocking connection or transaction.
Increase size of the cached view. In the "view_cache" section of the indexserver.ini file, increase the value of the
"total_size" parameter.
Monitoring Security, Sessions and Transactions Alerts
Check
Type
ID
Time
57
Daily
Secure store file system (SSFS) consistency regarding the DB
Check and make sure that the secure storage file system (SSFS) is accessible and consistent
regarding the DB.
62
Daily
User passwords- Identifies DB users whose password is due to expire
with the PW policy. If it expires, the user will be locked. This may impact
application availability.
Change password of the DB user. It is recommended that you disable the password
lifetime check of technical users so that their password never expires (ALTER USER
<username> DISABLE PASSWORD LIFETIME).
63
Daily
Granting of SAP_INTERNAL_HANA_SUPPORT role- if the internal support
role is currently granted to any DB users.
Check if the corresponding users still need the role. If not, revoke the role from them.
64
Periodic
Total memory usage of table-based audit log- % of the effective
allocation limit is being consumed by the DB table used for table-based
audit logging.
Consider exporting the content of the table and then truncating the table.
25
Daily
Open connections- % of the max number of permitted SQL connections
open.
The max number of permitted connections is configured in the "session" section of the
indexserver.ini file.Investigate why max number is being approached.
39
Daily
Long-running SQL statements
Investigate the statement. For more info, see table
_SYS_STATISTICS.HOST_LONG_RUNNING_STATEMENTS.
Security
Sessions
Session
&
Transactions
System
42
Description
SAP Recommended Admin Action
As needed Long-idling cursors
47
Periodic
Long-running serializable transactions
48
Periodic
Long-running uncommitted write transactions
49
59
Periodic
Daily
83
Daily
Long-running blocking situations
Percentage of blocked transactions
Table consistency- the number of table consistency errors and affected
tables
Close cursor, uncommitted transaction, or the serializable transaction in the application,
kill connection, or by executing the SQL statement ALTER SYSTEM DISCONNECT SESSION
<LOGICAL_CONNECTION_ID>. For more information, see the tables
HOST_LONG_IDLE_CURSOR, HOST_LONG_SERIALIZABLE_TRANSACTION and
HOST_UNCOMMITTED_WRITE_TRANSACTION (_SYS_STATISTICS).
Investigate the blocking and blocked transactions and if appropriate cancel one of them.
Contact SAP support
More System Information in HANA Studio
Server Performance Information
• It is possible to monitor more detailed aspects of system performance on
the Performance tab in order to detect and fix performance issues.
• In the Thread view you can end the operation of a specific thread
Since multiple threads run together in one session and in one transaction, the operations
of all subsequent threads belonging to that session/transaction will also be terminated.
Managing Large Tables with Partitioning
• When column tables grow containing high data volumes, it would be
advantageous to split them “horizontally” into smaller partitions
• HANA automatically manages the partitions in the background which
simplifies the access and frontend development and gives the administrator
a key tool to manage disks, memory, and large column stores
• In a distributed (scale-out) HANA system, it is possible to place the
partitions on different nodes and thereby increase performance
exponentially due to more processors being available for the users
• In a partitioned schema, it is possible to have 2 billion rows per partition
with virtually no limit on how many partitions can be added
• As a result, this becomes a matter of hardware and landscape architecture
as opposed to a question of database limitation
Managing Large Tables with Partitioning
• There are three different ways of creating partitions from an
administration standpoint in HANA:
By ranges
By hash
By round-robin
• While more complex schemas are possible with multilevel
partitioning, these three options cover the basics used in the higher
level options.
In addition to these options, you application layer may offer additional software
options depending on the application you are running on top of HANA
Partitioning Column Tables by Range
• If data familiarity is acute, data can be partitioned by any range in a table
• The most common partition is by date, though it is possible to use material
numbers, postal codes, customer numbers, or anything else
• Partitioning by date increases query speed and limits data to a single node
• The maintenance of range partitions is somewhat higher than the other
options since new partitions must be constantly added as data outside the
existing partitions emerge, as is the case with time sensitive data
Example of partitioning by SQL:
CREATE COLUMN TABLE SALES (sales_order INT, customer_number INT, quantity INT,
PRIMARY KEY (sales_order))
PARTITION BY RANGE (sales_order)
(PARTITION 1 <=values < 100000000,
PARTITION 100000000 <== values <200000000,
PARTITION OTHERS)
Partitioning Column Tables by Hash
• Partitioning column stores by the hash does not require an in-depth
knowledge of the data
• Instead, partitions are created by an internal algorithm applied to one or
more fields in the database by the system itself. This is known as a hash
• The records are then assigned to the required partitions based on this
internal hash number
• The partitions can be created in SQL with defined rules such as the following:
If the table has a primary key, it must be included in the hash
If more than one column is added, and the table has a primary key, all fields used to partition on must be part of
the primary key
If the number of partitions is not defined, the system will determine the optimal number of partitions based on
the configuration. As a result, this is the recommended setting for most hash partitions
Example of partitioning by SQL:
CREATE COLUMN TABLE SALES (sales_order INT, customer_number INT, quantity INT, PRIMARY KEY
(sales_order, customer_number))
PARTITION BY HASH(sales_order, customer_number)
PARTITIONS 6
Partitioning Column Tables by Round-Robin
• In a round-robin partition, the system assigns records to the partitions on a
rotating basis
• While it makes for efficient assignments and requires no data familiarity, it
also means that removing partitions in the future will be more challenging
as both new and old data will be present in the same partitions
• The following syntax can be used in SQL to create the partitions:
CREATE COLUMN TABLE SALES (sales order INT, customer number INT, quantity INT)
PARTITION BY ROUNDROBIN
PARTITIONS 6
In this example, six partitions are being created and records are assigned on a
rotating basis. If the last statement is changed to PARTITIONS GET_NUM_SERVERS(),
the system will assign the optimal number of partitions based on the system
landscape. The only requirement is that the table does not contain a primary key.
Moving Files and Partitions for Load Balancing
• Periodically moving files and file partitions allow column tables to achieve better load
balancing across hosts and are useful for adding or removing a node from the system,
creating new partitions, and load balancing existing ones that have grown very large
• Before initiating this process, save the current distributions using the RESOURCE
ADMIN system privilege for recovery later in the event of an error
• From the Table Distribution Editor the catalog, schemas, and tables can be viewed
• A table can be moved to another location by right-clicking it, select Move Table. A
similar process is used for moving partitions to consolidating partitions to single hosts
• If a “disk full” event is triggered it will be display on alerts and will suspend the use of
the database. You can find information in Volumes tab, and if it is full due to other
temporary files being stores, they may be deleted. The event is then marked as
“handled” in the Overview tab ceasing the suspension of the database
Security Authentication
• HANA has two forms for authentication
security
Internal Authentication
Users are created in HANA database only
Authentication is handled by HANA
database via username/password
•
External User Repositories
Kerberos or Security Assertion
Markup Language (SAML)
•
Once authenticated, users are then
checked for authorization privileges
Database users can have:


Direct Privileges
Inherited Privileges
When Kerberos is used, the users in the key distribution center should be mapped to
the database users in HANA by making user’s principal name the external ID.
Privileges and Admin Roles
Privileges on users are SQL privileges
that users can grant on their user.
ATTACH DEBUGGER is the only
privilege that can be granted on a user
For example, User A can grant User B
the privilege ATTTACH DEBUGGER to
allow User B debug SQLScript code in
User A's session. User A is only user
who can grant this privilege
Changing Password Policy
To change a password
policy, right-click on the
HANA system in the
NAVIGATOR pane and
select OPEN SECURITY
Under the PASSWORD
POLICY tab you can change
all the settings to conform
to your company’s password
rules
HANA Design with High Availability
Supports recovery measures ranging from faults and software errors to
disasters that decommission an entire data center
Provides the ability to rapidly resume operations after a system outage
with minimal business loss (fault resilience)
Offers a service auto-restart functionality which automatically detects the
failure and restarts the stopped service process
Allows the assignment of up to 3 master servers as the name server in case
the active master name server fails, the system can restore itself to the
available standby master
The number of standby servers defined during installation cannot subsequently be reduced
without major work. However, standby servers can be added after installation.
High Availability and Fault Tolerance
• High Availability configuration
N active servers in one cluster
M standby server(s) in one cluster
Shared file system for all servers
• Failover
Server X fails
Server N+1 reads indexes from
shared storage and connects to
logical connection of server X
Scale out – Standby Server Configuration
HANA cold standby host
Standby host is kept ready for the event that a failover
situation occurs during production operation
Standby host is not used for database processing
All the database processes run on the
standby host, but they are idle and do
not allow SQL connections
This Presentation Comprises:
• Key Responsibilities of Setup and Maintenance
• Modeling in HANA and Virtualizing Data Layers
• MCOD, MCOS, Virtualization and MDC HANA Deployment Options
• Active and Passive Monitoring of HANA
• Conclusion
Reference to More Comprehensive Information
• www.sap-press.com/sap-hana_3687/
Bjarne Berg and Penny Silvia, HANA: An introduction (SAP PRESS, 3rd
Edition).
• www.amazon.com/SAP-BW-HANA-Migration-Handbook/dp/150852761X/
Bjarne Berg, Rob Frye and Joe Darlak: BW to HANA migration handbook
• www.saphana.com/welcome
SAP’s main page for all HANA-related information
• www.saphana.com/community/try
HANA Marketplace
• scn.sap.com/community/bw-hana
SAP BW powered by HANA on SCN
5 Key Insights
• The Software Update Manager (SUM) for HANA Support Package Stack
(SPS) can execute automatic updates of the Lifecycle management
perspective as part of self-update
• Alerts are system monitoring tools within HANA that provide useful
information to help prevent potential problems
• The HANA Cockpit, DBA Cockpit, HANA Studio and LVM displays alerts
and system monitoring information
• HANA supports synchronous backup between production system and
backup storage
• HANA is designed with Support for High Availability
Questions?
:
Dr. Bjarne Berg