This post is part of the serie 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the serie!
- What's new in SQL Server 2016: Json
- Comparing execution plans with SQL Server 2016
- Bootstrap + SASS = ♥
- What's new in SQL Server 2016: Dynamic Data Masking
- What's new in SQL Server 2016: Row-Level Security
- What's new in SQL Server 2016: Stretch Database (this post)
- What's new in SQL Server 2016: Live Query Statistics
- What's new in SQL Server 2016: Temporal Tables
- What's new in SQL Server 2016: Always encrypted
- SQL Server 2016 SP1 - Licensing changes
The final version of SQL Server 2016 has been released recently. So it's time to take a look at the new features provided by this version. Today we will discover the Stretch Database.
Stretch Database allows you to archive tables containing historical data. For example, an Order table may contain very recent data, but also older data. The idea is to keep recent data in the local database and archive the older rows in Azure in a completely transparent way. SQL Server is able to move data automatically and load data from the local and remote database in a transparent manner. Currently Stretch Database migrates the entire table to Azure, which means that your schema must separate current data from historical data in 2 separate tables, if you want to keep recent data locally.
It is a completely transparent functionality for the user (queries are the same) to save storage on our servers, but you have to pay for SQL Azure.
To activate the feature you must execute the following command:
EXEC sp_configure 'remote data archive' , '1'; RECONFIGURE;
Then the easiest way is to use the GUI for configuration. We start by activating the functionality at the database level:
We log in with his Azure account and we indicate the identifiers of the Azure database that will be created automatically:
Then, SQL Server starts the configuration:
At this moment, we have a new database in Azure:
Then, activate the feature at the desired tables:
And that's it, the synchronization starts in the background. We can check the proper functioning of the archive by looking at the execution plan:
We can see that the query is the same as without Stretch Database. SQL Server loads data from local and remote tables without having to do anything special. Simply magical!