Background
With the release of Microsoft SQL Server 2016 a lot of new features were introduced, one of which was Temporal Tables, a feature that gives you the ability to view the state of your data at a given point in time. This means you can go back in time with your data. Another very popular feature is Stretch Databases which allows for remote archiving of data to Azure Stretch region.
Discussion
I am very fascinated by the concept of temporal tables and started to play around with it as early as CTP2 was released. As usual, I started to think about where I can use this in our environment and what would the benefit to the business be. The benefits that I could think of was some form of Data Auditing, Data warehouse Slowly Changing Dimensions and maybe even anomaly detection, but with this added abilities I very soon found that I am now consuming far more space than usual. Logging every event in your data seems like a good idea until you start running out of space.
So my next steps were to find a way to minimize the space the history tables use. So I started to think about what new features did Microsoft include in MS SQL 2016 that can help me with this problem, and naturally I started to think of Azure and how I can utilize this. I then started to think of how I can use this new feature (Stretch Databases) in conjunction with my history tables.
Considerations
I know the one biggest challenge we have with moving data into Azure is our country’s data governance laws. So be sure to have a chat with your companies legal and compliance department before you decide to move company data into Azure.
Microsoft has just recently changed the way Stretch Databases get stored and processed in Azure, meaning that they also changed the way the pricing model works around this. From my experience, this works out quite expensive with the new pricing model.
Prerequisites
- Azure Subscription
- Azure SQL Server
- SQL Server 2016
Objective
We will create a simple temporal (History) table and then set up the history table to be stretched to Azure. This will allow us to utilize the great new features of SQL Server 2016 and at the same time play with Azure. The goal is not to show how Temporal Tables work as there are more than enough great articles available to show you how this works. We want to be able to use Temporal Tables without the added space constraint.
Solution
First, let’s create a temporal table with some sample data. I created a simple Author table with some randomly generated data in the TemporalDemo database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
USE Master; GO DROP DATABASE IF EXISTS TemporalDemo CREATE DATABASE TemporalDemo; GO USE TemporalDemo; GO CREATE TABLE Author ( Id INT IDENTITY(1,2) PRIMARY KEY NOT NULL, Name VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL, EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (StartTime,EndTime) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Author_History) ); GO SET IDENTITY_INSERT [dbo].[Author] ON INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (1, N'Stefanie', N'Regina') INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (3, N'Sandy', N'Roy') INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (5, N'Lee', N'Dewayne') INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (7, N'Regina', N'Beth') INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (9, N'Daniel', N'Jolene') INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (11, N'Dennis', N'Nicolas') INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (13, N'Myra', N'Tricia') INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (15, N'Teddy', N'Hilary') INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (17, N'Annie', N'Shane') SET IDENTITY_INSERT [dbo].[Author] OFF GO |
We have now created a sample database with a temporal table and inserted some dummy data for demonstration purposes.
Now the next step will be to ensure that there is some data in the history table that we have created. We will do this by updating all records where the author name starts with a “T”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
UPDATE Author SET Surname = CONCAT(Surname,'_',LEFT(Name,1)) WHERE to Name LIKE 'T%' SELECT * FROM Author FOR SYSTEM_TIME ALL WHERE Name LIKE 'T%' SELECT * FROM Author_History |
From the above scripts we are returning all the records that we have updated to show that the system versioning caught the changes we made and just to be sure we also query the History table, we have created directly (this is not recommended by Microsoft). The following screenshot shows the expected output.
You will notice the first select statement returns the changed record before and after the change, this is indicated by the “end time” field.
Once we are happy with the data we can proceed to prepare the database for Stretch archive.
|
1 2 3 4 5 6 7 |
sp_configure 'remote data archive', 1 GO RECONFIGURE; GO |
We first have to enable remote data archive for the instance before we can setup Stretch, this can be achieved by running the script above. Before you go ahead please ensure that you have added a firewall rule to your azure SQL server to allow connections from your current IP. The steps to do this can be found here. Next, we want to configure the database to connect to Azure for the Stretch functionality.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperSecurePassword' CREATE DATABASE SCOPED CREDENTIAL remoteArchive WITH IDENTITY = 'AzureSqlServerUsername' , SECRET = 'AzureSqlServerSupersecurepassword' GO ALTER DATABASE TemporalDemo SET REMOTE_DATA_ARCHIVE = ON ( SERVER = '*********.database.windows.net' , CREDENTIAL = remoteArchive ) ; GO; |
With the above scripts, we created a master encryption key for our database and created a database scoped credential that we will use to connect to our Azure SQL server. Once this is created we can alter our database to connect to our Azure SQL server by setting REMOTE_DATA_ARCHIVE = ON for our test database. This might take a few moments and once this is done we are ready to enable remote archive for our history table.
|
1 2 3 4 5 6 |
ALTER TABLE Author_History--THE NAME OF OUR HISTORY TABLE SET ( REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND) ) |
We have now setup our history table to stretch to Azure, you can check this by looking at the query plan when you query the history table. You will also notice that it might be a lot slower when you query the history table now as SQL now have to first fetch the data from your remote data archive.
|
1 2 3 4 5 6 |
SELECT * FROM Author FOR SYSTEM_TIME ALL WHERE Name LIKE 'T%' |
This can also be visible in your Azure portal if you go to your SQL Server, you will see SQL Server has created a new database in Azure. According to Microsoft, this is a special region for Stretch and not just a normal Azure SQL Database.
Final Thoughts
This is a great way to use temporal tables and not having to worry about the added space it will consume. I know your executions will be a lot slower now, but let’s be honest how often are you going to retrieve data from the history tables? If you are going to do this very often then maybe you should rather be thinking of adding a clustered columnstore index on the history tables rather than stretching the history tables. Maybe I will write a post on how to achieve this as well.
If archiving is the only objective then maybe you should consider using Azure BLOB storage and create external tables to the BLOB storage. Will take some development, but we have implemented a solution like this for an APS as this was the only way we could do backups on the APS. This will not allow you to utilize the benefits Temporal Tables at all which was sort of the point of this article.
Jean-Pierre speaks at the Johannesburg SQL User Group, trying to give back to the SQL community as much as possible. He loves to tinker with SQL Server and see how he can approach a problem with a different angle.
View all posts by Jean-Pierre Voogt
- Running with running totals in SQL Server - July 29, 2016
- The new SQL Server 2016 sample database - July 22, 2016
- Storing Twitter feeds with Microsoft Flow in Azure SQL Database - June 29, 2016