Please refer to Part 1 - http://www.tek-analytics.com/bloginner.php?title=SAP-HANA-DYNAMIC-TIERING-PART-1
Please refer to Part 2 - http://www.tek-analytics.com/bloginner.php?title=SAP-HANA-DYNAMIC-TIERING-PART-2
Now speaking technical terms, how to create tables in extended storage?
Creating Hot & warm tables in Extended Storage
Tables created in extended storage show up in the schema catalog along with In-Memory tables with extension “EXTENDED”.
Once you have the extended table let’s create SQL Procedure to move data from In-Memory to Extended storage warm dynamic tables.
Let’s create a view to retrieve data from both Hot & Warm stores.
Now let’s talk about SAP HANA 2.0 - Dynamic Tiering
How is a Multistore table in HANA 2.0 different from extended table in HANA 1.0 ?
MULTISTORE TABLES FOR AGING DATA MANAGEMENT FROM HANA 2.0 SPS00+
By using a multistore table in your HANA system, you can "age out" older data using the SAP HANA Dynamic Tiering functionality, where you can still access and update older historical data, without a need to maintain two separate systems for current and historical archive as historical data is still needed sporadically for business analytics and decision-making. Organizations may need to keep older data primarily for auditing and compliance requirements.
SIMPLIFIED STORAGE USING PARTITIONING FOR BETTER PERFORMANCE OPTIMIZATION
Each multistore table contains at least two partitions: one in the SAP HANA default storage and one in extended storage. To fully utilize multistore table capabilities, it needs to be partitioned, you can choose which partitions experience the most frequent use, and store those partitions in default storage (In-Memory). You may then move data accessed less often to less expensive storage, like extended storage at your desired level of frequency (such as daily, weekly, monthly, annually, and so on), letting you move large amounts of data within a database to gain more In-Memory storage for performance. For tasks that require modifications to old historical data, you can reverse the process (through "anti-aging" of data) by bringing this data out from extended storage, back into default storage. When using a multistore table, the data is presented as a single table, and you can perform DML operations (such as inserting, deleting, and updating) regardless of whether the data is hot or cold. Tables without the time selection property do not allow you to restrict the UPDATE statement to affect just the column store or extended store partition in a multistore table, and could result in slower performance. For tables with time selection, however, you can use the WITH RANGE RESTRICTION (CURRENT |) clause to restrict the operation to partitions that are either current, or are more recent than the specified date. The faster and recommended method to move aged data is by partitioning, where you periodically move entire aged data partitions to extended storage as they become old and historical in terms of their value. Such partitions could be very large, and this method could move them at close to 1 terabyte per hour. You can create a multistore table from an existing non-partitioned column store HANA table.
How many Multistore Table Partition Types?
Multistore tables support single and multilevel partitioning.
WHAT IS A DELTA STORE IN HANA 2.0 & HOW DOES IT PLAY ROLE IN DYNAMIC TIERING?
The delta store uses row-level versioning (RLV) to allow more than one user to modify the same table concurrently, eliminating waits for transaction locks. RLV utilizes multi-version concurrency control (MVCC), for version management at the row level. The delta store can contain extended tables or multistore tables. You may create the delta store when you create extended storage, or you may alter extended storage to add a delta store later.
DYNAMIC TIERING EXTENDED STORAGE VIEW AT HIGH LEVEL IN HANA 1.0 VS 2.0
DYNAMIC TIERING SIZING
Dynamic Tiering system sizes are based on raw data size.
A starting point adjust cores (and memory) based on workload requirements
Sizing “Rules of Thumb” for Dynamic Tiering Storage
BACKUP & DISASTER RECOVERY (HANA 1.0 & 2.0)
*In case of HANA 1.0, HANA dynamic Tiering does not support HANA system replication.
*For a list of system replication features that Dynamic Tiering supports, see SAP Note 2356851
Limitations on SAP HANA Landscape when using Dynamic Tiering:
SAP HANA Dynamic Tiering 2.0 SP 02 (Released July 26, 2017) is the latest available version which has better performance & High Availability related improvements.
SPS 02 brings some exciting innovations to SAP HANA 2.0 Dynamic Tiering:
For More information on SAP HANA 2.0 SPS 02 check out Live Expert Series: What’s New in SAP HANA 2.0 SPS 02
Conclusion: Dynamic Tiering is continuously evolving to be a completely integrated data aging solution for SAP HANA to manage large volumes of aged data.
*Please note that at this point of time, Dynamic Tiering is not supported for SAP Business Suite on HANA & S/4 Hana. As dynamic tiering evolves to be more native to the HANA database, these applications will likely pick it up for large data volume use cases in future. But there are various other approaches to handle cold & hot data for SAP Business suite on HANA & S/4 HANA.
SAP HANA Dynamic Tiering Guides
SAP HANA Administration Guide
SAP HANA Dynamic Tiering Installation Guide
SAP HANA Dynamic Tiering Administration Guide
SAP HANA Dynamic Tiering Option Master Guide
SAP HANA Dynamic Tiering Option release notes
Let Tek Analytics Implement the solution for you. Please contact us @ firstname.lastname@example.org
NAVEEN MANNE @ Tek-Analytic
SAP BIG DATA & ADVANCED ANALYTICS|SAP HANA
* indicates functionality limitations/restrictions applicable.
DT is an abbreviation for Dynamic Tiering.
HA is an abbreviation for High Availability.