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 !!!
SQLXpertise 