And what to do with the historic data?

Hello dear readers, after nine months absent with many things to do and deserved vacations, I’m here again. This time I decided to share an interesting focus about how to handle historic data, taking advantage of two new features, availables since the SQL Server 2016 version, System Versioned Tables (now SVT) and Database Stretch.

What is Database Stretch?

It’s a new Azure based feature which allows to release space in the local storage, sending data to a cloud storage.

 Why to send historic data to the cloud?

There are two reasons that supports this decision:

1. The history data is bigger than current data.
2. The history data is rarely queried (except in BI applications where is very important).

The current data is also known as warm data because is queried frequently, and the historic data as cold data, because the reason said in the point 2.

Advantage of this approach:

1. Data (current and historic) is always available
2. This data handling method is transparent for applications, no additional code required.
3. Effortless historic data handling, because using SVT makes automatic the process to send the data to the history tables, SQL Server automatically takes charge of this task and is not necessary to write complex queries to fetch data.

How to implement it?

1. Enable SVT in the table which will store the historic data.
2. Select the same table for Stretching.
3. Sign-in in Azure.
4. Continue working normally.

Let’s see a short demo:

First step: enable SVT with this script:

The data set used for this sample can be obtained from Barcelona Open Data Catalog, it can be downloaded here. The data files are available in CSV and XML format, with SSIS is possible to load them in SQL Server. The sample script is here.

In the object explorer in SQL Server Management Studio, is possible to note that the table icon is a clock, this means that the table is SVT type, when it properties are shown, under the icon is the history table, and after a SELECT * query in both tables, here are the results:

Executing an update and then the previous SELECT * query, the history table now has data:

If we delete some rows and then execute the same SELECT sentence: delete rows are sent to history tables and current table doesn’t contain them.

Now, it’s time to do right click in the history table, in the context menu select: “Stretch” -> Enable.

Next.

Select the table to be sent to Azure, in this case the history table. In this example all rows are going uploaded to Azure, but using filtering conditions is possible to send just desired records to the cloud database, everything depends of how many data wants the business to hold, in summary, the retention policy.

This one would be the dialog to filter data

Next step, Sign in in Azure.

Provide login and password.

In Microsoft Azure configuration we have to set all connection parameters, be careful with the Azure Region, because not all regions allows stretching.

This key is used to encrypt the SQL Server credentials used to connect to Azure.

Finally a firewall rule which allows the connection from the local SQL Server to the cloud database.

Configuration summary.

Waiting for the wizard:

Everything OK, and now it’s time to click Close.

Finally is possible to see how has changed the database icon (stretch database).

With the following query is possible to show in the same dataset the current record and the history record, this capability is also useful for trending analytics and slowly changing dimensions in BI applications.

The main advantage for using this (SVT), is to fetch data from both tables (history and current) with the same query, without writing JOINS or UNION, the historic data handling has been implemented without additional coding because SQL Server controls the complete process, effectively and effortless.

Further information:

https://msdn.microsoft.com/en-us/library/mt590207.aspx

https://msdn.microsoft.com/en-us/library/mt591018.aspx

Para la versión en castellano de este artículo hacer clic aquí.