Table of Contents

Open all
Close all
Preface
23
So You Want to Be an SAP HANA Database Administrator?
24
The SAP HANA Landscape Used for This Book
26
Management Tools
27
Who Is This Book For?
28
How Is This Book Organized?
28
Acknowledgements
31
1 SAP HANA Overview
33
1.1 The Column Store
33
1.2 Hardware Requirements
36
1.3 Editions
37
1.3.1 SAP HANA, Platform Edition
37
1.3.2 SAP HANA, Express Edition
39
1.3.3 SAP HANA, Developer Edition
39
1.3.4 SAP HANA Cloud
39
1.4 Use Cases
40
1.4.1 SAP HANA as the Primary Database for the ABAP Platform and SAP NetWeaver
41
1.4.2 SAP HANA as a Data Mart
42
1.4.3 SAP liveCache on SAP HANA
43
1.4.4 SAP HANA Accelerators
43
1.4.5 SAP HANA as a Development Platform
44
1.5 Data Provisioning
46
1.5.1 SAP Landscape Transformation Replication Server
46
1.5.2 Mobilink
48
1.5.3 Direct Extractor Connection
49
1.5.4 SAP Data Services for ETL-Based Replication
49
1.5.5 SAP Replication Server for Log-Based Replication
50
1.6 Advanced Analytics
50
1.6.1 Machine Learning and SAP HANA, Predictive Option
51
1.6.2 SAP HANA, Spatial and Graph Option
51
1.6.3 Text Analytics and Search
52
1.6.4 SAP HANA, Streaming Analytics Option
53
1.7 Data Integration
54
1.7.1 SAP HANA Smart Data Access
54
1.7.2 SAP HANA Smart Data Integration and SAP HANA Smart Data Quality
55
1.8 Summary
55
2 Architecture of the SAP HANA System
57
2.1 The Basics
57
2.1.1 System Database
58
2.1.2 Tenant Databases
59
2.1.3 SAP HANA License Keys
61
2.1.4 Cross-Database Access
62
2.1.5 Database Isolation
62
2.2 Services and Processes
63
2.3 SAP HANA XSA Runtime Platform
67
2.4 Data Persistency
70
2.5 Multihost or Distributed SAP HANA Systems
71
2.5.1 Data and Table Partitioning
73
2.5.2 Single-Level Partitioning
75
2.5.3 Multilevel Partitioning
75
2.5.4 Heterogeneous Partitioning
76
2.6 High Availability
76
2.7 Backup and Recovery
78
2.8 Summary
80
3 Administration Tools
81
3.1 SAP HANA Cockpit
81
3.1.1 Navigation
81
3.1.2 Setup and Configuration
85
3.1.3 Monitoring and Administration
91
3.1.4 Starting and Stopping Instances and Databases
94
3.1.5 Configuring the System
96
3.1.6 Monitoring Performance
99
3.1.7 Installing the SAP HANA License
101
3.1.8 Executing SQL Statements
102
3.2 SAP HANA Studio
103
3.2.1 Setup and Configuration
104
3.2.2 Monitoring and Administration
105
3.2.3 Starting and Stopping Instances and Databases
108
3.2.4 Configuring the System
109
3.2.5 Monitoring Performance
111
3.2.6 Installing the SAP HANA License
112
3.2.7 Executing SQL Statements
112
3.3 Command-Line Tools
113
3.3.1 Starting and Stopping Instances and Databases
114
3.3.2 Monitoring and Administration
115
3.3.3 Configuring the System
117
3.3.4 Executing SQL Statements
119
3.3.5 Using hdbsql
121
3.4 DBA Cockpit for SAP HANA
124
3.5 Additional Tools
127
3.5.1 SAP Solution Manager
127
3.5.2 SAP Landscape Management
128
3.6 Summary
128
4 Instance Administration
131
4.1 Structure of an SAP HANA Instance
132
4.1.1 SAP HANA System ID and Instance Number
133
4.1.2 Users and Groups
133
4.1.3 Directory Structure
134
4.1.4 Process Tree
135
4.1.5 Services and Ports
136
4.1.6 SAP HANA Database Directories
137
4.1.7 INI Files: Database Parameters
141
4.1.8 Trace Files
143
4.1.9 Command Aliases for Directory Navigation
144
4.2 Starting and Stopping the Instance
145
4.2.1 With the Command Line
145
4.2.2 With SAP HANA Studio
145
4.2.3 With SAP HANA Cockpit
148
4.2.4 Starting and Stopping Tenant Databases
148
4.2.5 Checking the Instance and Database Status
150
4.2.6 Managing the SAP Start Service
150
4.3 Maintaining Parameters
152
4.3.1 With SQL Statements
153
4.3.2 With the SAP HANA Cockpit
157
4.3.3 With Transaction DBACOCKPIT
159
4.3.4 With SAP HANA Studio
161
4.3.5 With setParameter.py
162
4.3.6 Protecting against Unsupported Parameter Values
164
4.4 Managing Memory
165
4.4.1 Memory Layout
165
4.4.2 When and How Memory Is Allocated
166
4.4.3 Memory Shortages
167
4.4.4 Heap Memory and Shared Memory
168
4.4.5 Memory for SQL Statements
170
4.4.6 Host Memory Statistics
171
4.4.7 Monitoring Memory Usage
172
4.5 Sessions and Transactions
176
4.5.1 Monitoring Sessions
178
4.5.2 Monitoring Transactions
179
4.5.3 Monitoring Locks
182
4.6 Load Management and Distribution
184
4.6.1 Admission Control
185
4.6.2 Processor Affinity
188
4.6.3 Parallel Execution of SQL Statements
191
4.6.4 Workload Classes
193
4.6.5 Workload Settings for Users
201
4.6.6 Metadata
201
4.7 Special Utilities
202
4.7.1 hdbcons
202
4.7.2 hdbrsutil
204
4.7.3 hdbnsutil
204
4.8 Managing Redo Logs
205
4.8.1 Log Segments
206
4.8.2 Log Modes
206
4.8.3 Running with Logging Disabled
207
4.8.4 Handling a Full Log Situation
208
4.8.5 Information about Log Segments
211
4.9 Summary
216
5 Database Administration
217
5.1 Managing Tenant Databases
218
5.1.1 Creating a Tenant Database
218
5.1.2 Starting and Stopping Tenants
220
5.1.3 Renaming a Tenant
221
5.1.4 Deleting Tenant Databases
222
5.1.5 Working with Fallback Snapshots
223
5.1.6 Working with Services
224
5.1.7 Working with Alerts
226
5.1.8 Preventing System Changes in Tenant Databases
228
5.1.9 Restricting and Disabling Features on Tenant Databases
231
5.2 Managing Data and Log Volumes
231
5.2.1 Data and Log Volumes Overview
232
5.2.2 Savepoint Operation
233
5.2.3 Data Volumes
235
5.2.4 Partitioning of the Data Volumes
238
5.2.5 Log Volumes
241
5.2.6 Reclaiming Space
246
5.3 Database Corruptions
248
5.3.1 Verifying Logical Consistency
249
5.3.2 Verifying Physical Consistency
254
5.4 Backup and Recovery
256
5.4.1 What Is Disaster Recovery?
256
5.4.2 Savepoints and Redo Logs
258
5.4.3 Backup Types
259
5.4.4 Backup Targets
273
5.4.5 Backup Configuration
282
5.4.6 Running Backups
284
5.4.7 Database Recoveries
290
5.4.8 Diagnostic Files for Backup and Recovery
315
5.5 Landscape Management
316
5.5.1 Moving or Cloning an SAP HANA System Using SAP HANA Lifecycle Manager Tools
316
5.5.2 Renaming an SAP HANA System
318
5.5.3 Copying a System Using System Replication
318
5.5.4 Copying and Moving Tenant Databases Using Database Replication
319
5.5.5 Copying a Database Using Backup and Restore
330
5.6 Network Configuration
334
5.6.1 Network Zones
334
5.6.2 Ports and Connections
335
5.6.3 Client Connections
338
5.7 Summary
339
6 System Replication
341
6.1 Architecture
343
6.1.1 Storage Replication
343
6.1.2 System Replication Overview
345
6.1.3 Storage versus System Replication
354
6.1.4 Backup and Recovery
355
6.1.5 Hostname Resolution
356
6.1.6 System Replication Parameters
360
6.2 Configuring and Monitoring System Replication
365
6.2.1 Prerequisites and Process Overview
366
6.2.2 Using the SAP HANA Cockpit
369
6.2.3 Using SAP HANA Studio
373
6.2.4 Using the Command Line
376
6.3 Takeover and Failback
381
6.3.1 Using the SAP HANA Cockpit
383
6.3.2 Using SAP HANA Studio
384
6.3.3 Using the Command Line
385
6.4 Disable System Replication
386
6.4.1 Using the SAP HANA Cockpit
387
6.4.2 Using SAP HANA Studio
387
6.4.3 Using the Command Line
389
6.5 Multitier System Replication
390
6.5.1 Architecture
390
6.5.2 Multitier via the SAP HANA Cockpit
394
6.5.3 Multitier via SAP HANA Studio
397
6.5.4 Multitier via the Command Line
399
6.5.5 Performing a Takeover
401
6.5.6 Restore to the Original Configuration
404
6.6 SAP HANA, Active/Active Read-Enabled Option
406
6.7 Secondary Time Travel
410
6.8 Zero-Downtime Maintenance with SAP Solutions
414
6.9 Hardware Exchange via System Replication
417
6.10 Summary
418
7 Scale-Out Systems and High Availability
419
7.1 Architecture
419
7.1.1 Scale Up Versus Scale Out
419
7.1.2 Multiple Node System Overview
421
7.1.3 Host Grouping to Separate Hot and Warm Data
425
7.1.4 Host Roles
425
7.1.5 File System Layout
427
7.2 Network Layout
428
7.2.1 Host Name Resolution
428
7.2.2 Client Connections
431
7.2.3 Application Connection after Takeover
432
7.3 Host Autofailover
433
7.3.1 Network-Attached Storage
434
7.3.2 Storage Area Network
435
7.4 Administration and Monitoring
436
7.4.1 Starting and Stopping
436
7.4.2 Monitoring
437
7.4.3 Configure Host Failover
443
7.5 Configuration and Setup
445
7.5.1 Adding Hosts
445
7.5.2 Distributing Tenant Databases
449
7.5.3 Data Redistribution
451
7.5.4 Removing Hosts
455
7.5.5 Adding/Removing Host Roles
457
7.6 Takeover and Failback
457
7.6.1 Host Takeover Detection
458
7.6.2 Takeover Execution
459
7.6.3 Failback Execution
464
7.7 Summary
464
8 Data Tiering
465
8.1 Data Tiering Options
466
8.2 Persistent Memory
470
8.2.1 Persistent Memory Overview
470
8.2.2 Configuration Parameters
472
8.2.3 Enabling Persistent Memory on Database Objects
473
8.2.4 Unloading Tables from Memory
474
8.2.5 Monitoring Views
474
8.2.6 Persistent Memory Monitor
475
8.3 Native Storage Extensions
476
8.4 SAP HANA Extension Node
480
8.4.1 Architecture
481
8.4.2 Configuration
482
8.4.3 Backup and Recovery
483
8.4.4 High Availability
483
8.5 SAP HANA Dynamic Tiering
484
8.5.1 Dynamic Tiering Overview
484
8.5.2 Architecture
485
8.5.3 Configuration
487
8.5.4 Database Backup and Recovery
488
8.5.5 High Availability
488
8.6 Integration with SAP IQ
489
8.7 Integration with Hadoop
491
8.8 Summary
493
9 Planning and Setting Up the SAP HANA Landscape
495
9.1 Architecture
495
9.2 Cloud Deployments
496
9.3 On-Premise Deployments
498
9.3.1 The Appliance
500
9.3.2 SAP HANA Tailored Data Center Integration
501
9.3.3 Appliance versus SAP HANA Tailored Data Center Integration
503
9.3.4 Scale Up versus Scale Out
505
9.3.5 SAP HANA Hardware Directory
505
9.3.6 Deployment Options
506
9.3.7 Software and Hardware Virtualization
512
9.3.8 Codeployment of SAP HANA with SAP NetWeaver AS ABAP/Java on One System
515
9.3.9 Deployment Options Compared
517
9.4 Capacity Planning
519
9.4.1 Quick Sizer
519
9.4.2 Sizing Reports
520
9.4.3 T-Shirt Sizing
533
9.4.4 Easy and Lazy Sizing
533
9.5 Infrastructure Design
536
9.5.1 Appliance
536
9.5.2 SAP HANA Tailored Data Center Integration
537
9.6 Network Layout
540
9.6.1 Client Zone
542
9.6.2 Internal Zone
543
9.6.3 Storage Zone
545
9.6.4 Backup Zone
546
9.7 High-Availability Clusters
547
9.8 Virtualization
550
9.8.1 Virtualization on VMware
551
9.8.2 Virtualization on IBM Power VM
555
9.9 Operating System Platforms
557
9.10 Reference Architecture
557
9.11 Summary
561
10 Installation and Updates
563
10.1 Release Cycle of SAP HANA
563
10.2 Preparing for Installation and Upgrades
565
10.2.1 Hardware Requirements
566
10.2.2 Software Requirements
566
10.2.3 SAP Hardware and Cloud Measurement Tool
584
10.3 Using Lifecycle Management Tools
588
10.3.1 SAP HANA Lifecycle Manager
589
10.3.2 Graphical User Interface
591
10.3.3 Command Line Interface
592
10.3.4 Web User Interface
593
10.3.5 Executing Tasks in a Distributed System
596
10.3.6 Optimized or Flexible Downtime
597
10.4 Downloading the Software
598
10.5 Installing an SAP HANA System
600
10.5.1 Users and Configuration Files Created during the Installation
600
10.5.2 Preparing the Installation Media
601
10.5.3 Installing a Single System
603
10.5.4 Installing a Multiple-Host System
612
10.5.5 Postprocessing Actions
617
10.6 Updating an SAP HANA System
620
10.6.1 Preparing the Update Media
621
10.6.2 Updating a Single System
626
10.6.3 Updating a Multiple Host System
634
10.6.4 Updating Using System Replication and Zero-Downtime Maintenance
635
10.6.5 Postprocessing Actions
637
10.7 Uninstalling SAP HANA
639
10.8 Installing and Updating the SAP HANA Client
640
10.8.1 Installation on UNIX, Linux, and macOS
641
10.8.2 Installation on Microsoft Windows
642
10.9 Installing and Updating the SAP HANA Cockpit
644
10.10 Installing and Updating SAP HANA Studio
648
10.11 Summary
651
11 Database Objects
653
11.1 Object Attributes
654
11.1.1 Database Catalog
654
11.1.2 Schemas, Users, and Ownership
655
11.1.3 Object Names
657
11.1.4 The Object ID
658
11.1.5 Object Dependencies
658
11.2 Tables
659
11.2.1 Row Store and Column Store
660
11.2.2 Table Columns
662
11.2.3 Displaying the Structure of a Table
665
11.3 Indexes
667
11.4 Triggers
668
11.5 Constraints
669
11.6 Views
670
11.6.1 SQL Views
670
11.6.2 Column Views
674
11.7 Sequences
676
11.7.1 Usage Example
677
11.7.2 Numbering Gaps and Chronology
679
11.8 Stored Procedures
680
11.8.1 Creating a Stored Procedure
680
11.8.2 Calling the Stored Procedure
682
11.9 Functions
682
11.9.1 Creating a Function
683
11.9.2 Using the Function
684
11.10 Synonyms
686
11.11 Summary
687
12 Working with Tables
689
12.1 The Storage Models: Row and Column Stores
691
12.1.1 Row-Store Tables
691
12.1.2 Column Store Tables
693
12.1.3 Usage Guidelines: Row Store or Column Store?
694
12.2 Compression
695
12.2.1 Dictionary Compression
696
12.2.2 Optimized Compression
698
12.2.3 When Is Compression Used?
702
12.3 Delta Store and Delta Merge
703
12.3.1 The Delta Merge Process
704
12.3.2 When Does a Delta Merge Happen?
706
12.4 Partitioning
711
12.4.1 Partition Placement
712
12.4.2 Single-Level Partitioning
712
12.4.3 Multilevel Partitioning
723
12.4.4 Special Operations on Partitioned Tables
727
12.4.5 Heterogeneous Partitioning
732
12.4.6 How Many Partitions Should a Table Have?
733
12.5 Table Classification and Distribution
734
12.5.1 Table Classification
734
12.5.2 Table Placement Rules
736
12.5.3 Table Placement Location
737
12.5.4 Example of Table Placement
739
12.6 Memory and Persistence Layer
745
12.6.1 Loading Data into Memory
746
12.6.2 Preloading Tables
746
12.6.3 Loading and Unloading Tables
747
12.6.4 Savepoints
748
12.7 Special Table Types
749
12.7.1 Temporary Tables
749
12.7.2 Temporal Tables
756
12.7.3 Flexible Schema Tables
764
12.7.4 Table DUMMY
766
12.8 Indexes
768
12.8.1 Row-Store Index Types
769
12.8.2 Column-Store Index Types
771
12.8.3 Index Persistence to Disk
777
12.9 Multiversion Concurrency Control
778
12.9.1 Read Stability and MVCC
779
12.9.2 Row-Store Garbage Collection
780
12.9.3 MVCC in the Column Store
781
12.10 Export and Import
781
12.10.1 Output Formats
782
12.10.2 Export
782
12.10.3 Import
787
12.10.4 Export and Import with SAP HANA Cockpit
787
12.10.5 Export and Import with SAP HANA Studio
790
12.11 Consistency Checks
794
12.11.1 Stored Procedures
794
12.11.2 Manual Consistency Checks
795
12.11.3 Automatic Table Consistency Check
796
12.11.4 Results of the Checks
796
12.11.5 Consistency Checks at Startup
797
12.12 Metadata
797
12.12.1 Row and Column Store
797
12.12.2 Compression
797
12.12.3 Delta Merge
798
12.12.4 Partitioning
799
12.12.5 Table Classification and Distribution
800
12.12.6 Persistence
801
12.12.7 Temporary Tables
801
12.12.8 Temporal Tables
802
12.12.9 Indexes
803
12.12.10 Multiversion Concurrency Control
803
12.12.11 Export and Import
804
12.12.12 Consistency Checks
805
12.13 Summary
805
13 Security
807
13.1 Security Patches
809
13.2 User Management
810
13.2.1 Standard Database Users
811
13.2.2 Restricted User Accounts
812
13.2.3 Internal User Accounts
812
13.2.4 Creating and Managing User Accounts
814
13.2.5 The SYSTEM User
822
13.2.6 User Groups
824
13.3 Database Privileges
826
13.3.1 Privileges Managed in the Catalog
828
13.3.2 Privileges Managed in the Repository of SAP HANA
833
13.3.3 Privilege Types
834
13.4 Database Roles
842
13.4.1 Creating and Maintaining Roles
843
13.4.2 Catalog and Repository Roles
844
13.4.3 Standard Catalog Roles
847
13.5 Troubleshooting Authorization Issues
848
13.5.1 Finding Information on Granted Privileges and Roles
848
13.5.2 Tracing Missing Authorizations
849
13.6 Authentication Methods
854
13.6.1 Username and Password Authentication
855
13.6.2 LDAP Authentication
861
13.6.3 Supported Third-Party Authentication Providers
862
13.7 Auditing the Database
863
13.7.1 Audit Policies
864
13.7.2 System-Wide Audit Policies
866
13.7.3 Default Audit Policies
867
13.7.4 Configuration and Audit Policy Management
867
13.7.5 Recommended Audit Policies and Best Practices
875
13.8 Encryption
877
13.8.1 Server-Side Data Encryption
877
13.8.2 Persistency Encryption
890
13.8.3 Client Encryption Settings
894
13.8.4 Managing Certificates
900
13.9 Increase the System Isolation Level
902
13.10 Cross-Database Authorizations
906
13.11 Summary
909
14 Performance Analysis
911
14.1 System Performance Monitoring
912
14.1.1 SAP HANA Cockpit
912
14.1.2 SAP HANA Studio
915
14.1.3 DBA Cockpit
915
14.1.4 SQL Statements
916
14.2 SQL Statement Analysis
917
14.2.1 EXPLAIN PLAN
918
14.2.2 SQL Plan Cache and Expensive SQL Statements
922
14.2.3 PlanViz
930
14.2.4 DBA Cockpit
937
14.2.5 SQL Trace: Transaction ST05
939
14.3 Optimizer Statistics
946
14.4 Summary
948
15 Monitoring and Troubleshooting
949
15.1 Monitoring Views
950
15.1.1 Views in the SYS_DATABASES Schema
951
15.1.2 Reset Points for Monitoring Views
952
15.2 Statistics Service Views
953
15.3 Displaying System Views
955
15.4 Diagnostic Files
955
15.4.1 Trace Files
955
15.4.2 Dump Files
958
15.4.3 Additional Files
960
15.4.4 Viewing Diagnostic Files
960
15.5 Alerts
966
15.5.1 Statistics Views for Alerts
966
15.5.2 Displaying and Managing Alerts
968
15.6 SAP’s SQL Statement Collection
978
15.6.1 Installing the Scripts
979
15.6.2 Structure of the SQL Scripts
980
15.6.3 Editing Scripts
981
15.6.4 Version and Feature-Dependent Scripts
983
15.6.5 Running the Scripts
983
15.7 Mini Checks
991
15.8 SQL Trace
994
15.8.1 Activating the Trace
995
15.8.2 Displaying the Trace
998
15.9 Additional Traces
1002
15.9.1 Database Trace
1002
15.9.2 User-Specific and End-to-End Traces
1004
15.9.3 Kernel Profile Trace
1004
15.9.4 Kernel Sentinel
1004
15.9.5 Client-Side Traces
1004
15.10 Summary
1005
A Setting Up Your Trial System
1009
A.1 Install SAP HANA, Express Edition
1010
A.1.1 Download and Install the Java SDK
1010
A.1.2 Download and Install a Hypervisor
1011
A.1.3 Download the Download Manager
1011
A.1.4 Install your Virtual Machine
1012
A.2 Get SAP HANA with SAP Cloud Appliance Library
1013
A.2.1 SAP Cloud Appliance Library Overview
1014
A.2.2 Create an Account and Subscription in the Public Cloud
1015
A.2.3 Set Up Your SAP Cloud Appliance Library
1016
A.2.4 Deploying Solutions
1017
A.2.5 Connect to Your System and Start Using the Software
1020
A.3 Summary
1021
B References and Further Reading
1023
B.1 Accessing the SAP Documentation
1023
B.2 SAP Documentation and Manuals
1027
B.3 SAP Product Support
1028
B.3.1 Architecture/General
1029
B.3.2 Management and Tools
1031
B.3.3 Monitoring, Analysis, and Troubleshooting
1031
B.3.4 Backup and recovery
1032
B.3.5 Security
1033
B.3.6 Performance
1033
B.3.7 High Availability and Replication
1033
B.3.8 Special Features and Operations
1034
B.4 Further Reading from SAP PRESS
1034
C The Authors
1035
Index
1037