Microsoft has added another cool feature in SQL Server 2016 – Stretch databases – that provides a cost effective way to store historical or ‘cold’ data. This feature is ideal for transactional databases with large amounts of cold data, typically stored in a small number of tables. Stretching is better than the traditional method of archiving your old data to a remote location because stretched data can still be queried and accessed through applications. The data is always online and there is no need to change anything in your application in order to query it. The end user doesn’t see any difference other than possibly a small amount of latency when querying the stretched data
Features and Advantages:
Some features and benefits of stretching your data to Microsoft Azure:
There are some limitation to stretch databases, and not all databases or tables will be eligible for stretching. Some features and datatypes in a table will prohibit stretching a table in your database. These include
Certain limitations also exist for tables that have been stretched:
Steps to set up Stretch Database
I’m going to assume you have already completed the first two steps and will not go over these. So beginning with step 3, to implement a stretch database you must enable the feature on both the instance and database.
To enable Stretch Database on your instance run the following statement:
–enable instance for stretch
EXEC sp_configure ‘remote data archive’, ‘1’
From SQL Server Management Studio (SSMS) right click on the database which holds the table(s) you want to stretch to Azure and select Tasks > Stretch > Enable.
This will open the Enable Database for Stretch Wizard. First step will be to select the table(s) you want to stretch to Azure. You can select the entire table contents, or…
…if you click on the ‘Entire Table’ link, you can select specific rows to stretch as shown below.
The next step is to Configure Azure. You will be prompted to sign in to your Azure account:
After you have signed in to Azure, select the subscription to use (if you have more than one), select the correct region, select the server you have created (or create a new one), and provide the login credentials. Currently Azure only supports SQL Server Authentication for stretch databases.
In order to stretch a database table to Azure, the database must have a database master key (DMK). Specify (and save) the password for the DMK by creating the credential in the Wizard as follows on the Secure Credentials page.
If you already have a DMK for your database you will enter the password on this same screen as shown.
On the Select IP Address page, you can select the ‘Use source SQL Server Public IP’ button or specify the range of subnet IP addresses to use.
After specifying the IP addresses, click next through the Summary and Results pages.
Thank you Microsoft, we will enjoy it!
Monitor your Stretch Progress
In order to see the migration status of your data to the Azure cloud server, run this TSQL statement to show the number of rows migrated:
select * from sys.dm_db_rda_migration_status
order by start_time_utc desc
You can also monitor your stretched database by right clicking on your database > Tasks > Stretch > Monitor as shown
This will display a report with your Source server info, the Azure server info and information about the tables that were stretched:
You will also notice in Object Explorer, the stretched database has a different icon beside it
You will also notice under the External Resources of your database, the Stretched server will be listed in the External Data Sources folder.
When connecting to your Azure server, you can now see the stretched database which get assigned a name and table name automatically.
Let’s see what happens now when we query data from the Orders table we just stretched.
From your local server instance, select rows from your table as shown. You will notice that the execution plan shows a ‘Remote Query’ operation because it is pulling data from the remote Azure server.
I only stretched certain rows of data to the Azure cloud, not the entire table. When I run a select statement that only pulls data from my local database and not the stretched Azure data, you will notice the execution plan changes. No Remote Query or concatenation operation is involved because it is only pulling data from the local table.
Disable Stretch Database
Now if for some reason you need to un-stretch your database you can disable Stretch Database for your entire database or just for an individual table. Disabling stretch database does not delete the table or data from Azure. You have to manually drop it using the Azure management portal, and the remote table in Azure will continue to incur costs until you delete it.
To disable Stretch Database for your database right click on your database in Object Explorer on your local SSMS, select Tasks > Stretch > Disable as shown
To disable Stretch Database for a table, right click on the table name and select Stretch > Disable > and either Bring data back from Azure, or Leave data in Azure. Bringing data back from Azure will incur data transfer costs.
Migrating cold or historical data to Microsoft’s Azure can be a cost effective way to retain inactive data, while still having access to it when needed. For information on Azure Stretch Database pricing go here https://azure.microsoft.com/en-us/pricing/details/sql-server-stretch-database/ . This article covered very basic information on how to set up a Stretch Database, but there are several more aspects such as performance implications and backup/restore processes that we did not cover. Hopefully this gives a starting point on which you can begin to test and explore this new feature.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!