Temporal Table in SQL 2016

Most common challenges faced while managing large data:

  • How to maintain historical data.
  • How to handle data changes.
  • Impact on having triggers on table to capture every single change in data for auditing.
  • How to recover accidental changes.
  • Calculating trends over time.

To address these challenges Microsoft has introduced a system-versioned temporal table, a new type of user table in SQL Server 2016. A temporal tables are designed to maintain a full history of data changes which allows you to find the state of data at any point in time. This is completely manage by database engine. When you create Temporal table system creates two table 1- Current table & 2. History table.

Limitations:

  • FileTable & FILESTREAM features are not supported.
  • CASCADE option can be used in case of referencing tables.
  • INSTEAD OF triggers are not supported, though AFTER triggers are supported.

Every temporal table has two explicit defined columns with a datetime2 data type (period columns), that you can use as start (SysStartTime) and end (SysEndTime) periods for which row id valid.

Capture

How to create Temporal Table:

Capture

How to query Temporal Table:

Capture

Managing Data Retention:

Data grows very fast when we track each & every change from transnational table, historical data. So how long we should keep the data available in history table and how to move it out of table when retention period expires. Retention period can be decided based on business requirement, however to move data we have multiple options:

An interesting topic to learn more about. Refer to msdn:- https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

 

Advertisements

Replicate Azure IaaS VMs

Microsoft has announced the public preview of disaster recovery for Azure IaaS virtual machines (VMs) using Azure Site Recovery (ASR). With this new capability of Azure Backup for IaaS VM, you can also protect IaaS based applications running on Azure by replicating on to different Azure regions.

Features highlights:

  • Offered “as-a-Service” – No need of additional infrastructure.
  • Simplified experience – Enables Cross-region DR.
  • Application-aware security – Control a fail over.
  • Non-disruptive DR drills

Capture

Source:https://azure.microsoft.com/en-us/blog/announcing-disaster-recovery-for-azure-iaas-vms-using-asr/