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.
- Range Partitioning (Single)
- Hash-Range and Range-Range Partitioning (multilevel)
- Time-Selection Partitioning (multilevel)
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
- 2.5x data compression factor (raw data compressed to <40% of its original size)
- Metadata and versioning space: 5% of compressed data • Temp store: 20% of compressed data
- For any DT system size, ensure that the storage system can provide 50MB / sec / core of throughput
- Size RLV transaction log at 8GB * 2 * (number of cores) Cores
- 1.5 core / concurrent query • 1 core can load 10MB / sec of raw data
- In the sizing boxes above, round up the number of cores to match a hardware vendor’s most compatible server model RAM
- 16GB / core (assumes use of delta enabled extended tables for concurrent writes) Network
- 10GBit / sec dedicated network between HANA and DT server
- Add networks as needed, so that HANA DT network is completely isolated from storage network
BACKUP & DISASTER RECOVERY (HANA 1.0 & 2.0)
- HANA backup manages backup of both hot & warm stores
- Point in time recovery is supported*
- Data backups with log backups allow restore to Point in time or most recent point before the crash.
- Data backups alone only allow restore to n-1 backup if the recent backup is incomplete due to crash.
- Storage failures will depend upon storage vendor disk mirroring & fault tolerance capabilities.
- SAP HANA systems with extended storage supports system replication from HANA 2.0 SPS00 , a high availability(HA) feature that maintains a secondary system for fault and disaster recovery support.
- There are few limitations for Dynamic Tiering from system replication perspective in initial versions.
*In case of HANA 1.0, HANA dynamic Tiering does not support HANA system replication.
- Dynamic Tiering only supports System PKI SSL for system replication. To set up System PKI for internal system replication for Dynamic Tiering, follow the steps in “Secure Internal Communication between Sites in System Replication Scenarios” in the SAP HANA Security Guide.
- SAP HANA Dynamic Tiering is not recommended to be used in production within MDC systems up to SPS12
*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:
- Two-tier asynchronous system replication
- Three-tier system replication
- New option for hypervisor vendors to self-certify their virtualization environments for SAP HANA dynamic tiering.
- Improved performance of data transfer from the dynamic tiering server back to HANA (anti-aging)
- Smarter caching for cross-store join operations (caching of HANA data in dynamic tiering, to improve performance of repetitive queries that join data in HANA with data in 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 @ [email protected]
* indicates functionality limitations/restrictions applicable.
DT is an abbreviation for Dynamic Tiering.
HA is an abbreviation for High Availability.