Temporal Tables – Part 5- Simple Guide to implement History Tables using SQL Server 2016/2017– Storing History data in Azure using Stretch Database

In previous posts, we saw

Part 1 – How to create Temporal Tables

Part 2 – Accessing History data from Temporal Tables

Part 3 – Modifying Temporal Tables, Disabling System versioning and why we need to disable ?

Part 4 – Managing Temporal Tables and History Data

In this post we will see how to store the history data in Azure using Stretch database

Step 1: Right click on Database name and click on Tasks –> Stretch –> Enable


Step 2: Select the tables which you would like to enable Stretch


Step 3: Click on “Entire Table” hyperlink in Migrate column to customize the criteria for Stretch, by default the Entire table will be stored in SQL Azure

Please specify the name of the Stretch Criteria, Please select the Column based on which you want to apply the filter criteria , select the Condition and then specify the value



After specify the criteria, click on “Check” to validate, once you “Success”, Please click “Done” to continue

Step 4:  Please review the selected tables and click “Next” to continue


Step 5: Please sign in to your Azure account, if you are not already signed in, Please select the Subscription, Azure region

We can create a new SQL Azure server or use an existing server, in this demo I am using an existing server

After providing details, click “Next” to continue


Step 6: Please provide the Public IP Address range for which you need to enable access, If you have only one Public IP Address, Please specify that for both From and To and then click Next to proceed

FYI, If you created a new server, by default this step will enable the Firewall for Azure services


Step 7: Review the settings selected and also review the estimated Pricing, this will change based on the number of tables and size of data you are planning to store in SQL Azure

Please click “Finish” to start the Stretch enabling process


We can review the Stretch deployment process in below screenshot, based on your selecting, the steps shown below can change


Once all the steps are completed, we can see status as “Passed”, if you want to see the verbose details on what happened in each step, we can review the Logs, To access the logs, Please click on Hyperlink, This will be very helpful when there is any error occurred


How to monitor Stretch databases ?

We can now see the Icon for the database has changed


To monitor the progress of Stretch, Please right click on Database Name –> Tasks –> Stretch –> Monitor


In Monitor window, we can see the Source server, Target Azure server, Database and list of tables being stretched and number of rows uploaded


We can also connect to the SQL Azure server and access the history data as shown below




How to disable Stretch for a particular table / database ?

To disable Stretch for a table, right click on table name –> Stretch –> Disable

You have 2 options

1. Bring data back from Azure – This will download all the data from SQL Azure and update your local server

2. Leave data in Azure – This will leave the data as is in SQL Azure server and new data will be stored locally

Please choose the option based on your requirement


In this demo, we are using “Bring data back from Azure” option, Please click “Yes” to confirm the disable process


Depending on the size of the data, the process will run and you will see the below message


After disabling Stretch on all the tables, we can disable the Stretch at the database level by right clicking on Database name –> Tasks –> Stretch –> Disable


FYI, If you try disabling the Stretch in your database, before disabling Stretch in the tables, you will see this below validation message


Once the Stretch disable process is completed, we will see this below completed message


FYI, You can use the Stretch option not just for Temporal History tables, you can use this for any other tables where you want to store historical data or least accessed data remotely for reference or compliance purposes

Hope you find this post helpful !!!

%d bloggers like this: