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