Stretch Databases – SQL Server 2016

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:

• Useful for ‘cold’ or historical data.
• Your data is still online and available for query.
• No changes to applications or queries required.
• Stretching your data to Azure is encrypted and safe – and Always Encrypted, Row Level Security, and other SQL security features are still available
• Useful for reducing storage space needed on local disk
• Useful for reducing size of backups and the time required to perform backups.
• Stretching applies to individual tables – you can stretch either the entire table or selected rows from the table.
• Select and Insert is supported for stretched data; while Update and Delete is not supported.

Limitations:

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

• Tables with FILESTREAM data
• Tables replicated or using Change Tracking or Change Data Capture
• Memory optimized tables
• Text, ntext, image, timestamp datatypes
• Sql_variant, geometry or geography, CLR, hierarchyid datatypes
• COLUMN_SET or computed column types
• Default or check constraints, or foreign key constraints that reference the table
• Full text, XML, spatial indexes, or index views that reference the table

Certain limitations also exist for tables that have been stretched:

• Uniqueness is not enforced with UNIQUE or PRIMARY KEY constraints in the stretched table in Azure
• You can’t Update or Delete rows in a stretch enabled table or view
• You can’t Insert rows into a stretched table on a linked server
• You can’t create an index for a view that includes stretch enabled tables
• Filters on indexes do not propagate to the remote table.

Steps to set up Stretch Database

1. Get an Azure account with Microsoft – https://azure.microsoft.com/en-us/free/
   1. Create Azure server
   2. Establish user credentials
   3. Configure firewall rules
2. Run DMA (Data Migration Assistant) to see if your database is eligible
   1. Download Stretch Database Advisor (DMA) – https://www.microsoft.com/en-us/download/details.aspx?id=53595
3. Enable Instance
4. Enable Database
5. Monitor state of stretch progress

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.

Enable Instance

To enable Stretch Database on your instance run the following statement:

–enable instance for stretch

EXEC sp_configure ‘remote data archive’, ‘1’

GO

RECONFIGURE

GO

Enable Database

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.

Now What?

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.

Conclusion

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!