This post is part of the series 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the series!
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 can move data automatically and load data from the local and remote databases 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:
SQL
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!
Do you have a question or a suggestion about this post? Contact me!
