SQL Server 2016 – Introduction to Stretch Database

No, that is not the stretch you're looking for

Beginning with SQL Server 2016 you will have the ability to store portions of a database in the cloud. This new ability is known as Stretch Database and the feature will be beneficial to those needing to keep transactional data for long periods of time and those looking to save money on storage. Being able to seamlessly migrate data to the Microsoft Azure Cloud will give you the ability to archive data without having to change the way your applications query the data.

In SQL Server 2016 Community Technology Preview 2 (CTP2), Stretch Database migrates entire tables. If your database is already set up to store archive data in separate tables from current data, you will be able to easily migrate the archive data to Azure. Once you enable Stretch Database, it will silently migrate your data to an Azure SQL Database. Stretch Database leverages the processing power in Azure to run queries against remote data by rewriting the query. You will see this as a "remote query" operator in the query plan.

An easy way to identify databases and tables that are eligible for being Stretch-enabled is to download and run the SQL Server 2016 Upgrade Advisor and run the Stretch Database Advisor. Aaron Bertrand (@AaronBertrand) wrote about this recently:

• Identify Candidate Tables for SQL Server 2016 Stretch Databases

Limitations for Stretch Database

Not all tables will be eligible for being Stretch-enabled. Certain table properties, data and column types, constraints, and indexes are not supported, such as:

• Memory-optimized and replicated tables
• Tables that contain FILESTREAM data, use Change Tracking or Change Data
• Data types such as timestamp, sql_variant, XML, geography or columns that are Always Encrypted
• Check and default constraints or foreign key constraints that reference the table
• XML, full-text, spatial, clustered columnstore and indexed views that reference the Stretch-enabled table
• You cannot run UPDATE or DELETE statements, or run CREATE INDEX or ALTER INDEX operations on a Stretch-enabled table

For a full listing of limitations, you can visit: Requirements and limitations for Stretch Database.

Setting up Stretch Database

Getting started isn't a complicated task. You'll need an Azure account and then enable Stretch Database on the instance.

To enable Stretch Database on an instance run:

EXEC sys.sp_configure N'remote data archive', '1';
RECONFIGURE;
GO

For this demo I'm going to use the AdventureWorks2014 database on a SQL Server 2016 CPT2 instance. I'll start by creating a new table:

USE [AdventureWorks2014];
GO

CREATE TABLE dbo.StretchTest
(
  FirstName VARCHAR(50),
  LastName  VARCHAR(50)
);
GO

And then I'll populate the test table StretchTest with some data:

USE [AdventureWorks2014];
GO

INSERT INTO dbo.StretchTest(FirstName, LastName)
VALUES('Paul', 'Randal'),  ('Kimberly', 'Tripp'),('Jonathan', 'Kehayias'),
      ('Erin', 'Stellato'),('Glenn', 'Berry'),   ('Tim', 'Radney');
GO

I now have a table that I can stretch to the Microsoft Azure Cloud. To do this I'll use the GUI by right-clicking on AdventureWorks2014, choosing Tasks, and selecting Enable Database for Stretch.

The Enable Database for Stretch wizard will open, as below:

I'll click next:

And sign in to my Microsoft Azure account:

I'm then prompted to verify which account I want to use:

Then I select which Azure location I want to use and specify an admin login and password. When you do this, make sure to make note of the admin username and password because you will need this in the future in order to reconnect to the Azure SQL Database if you have to restore the database.

I then click next:

And click Finish and the database starts provisioning to Azure SQL Database Server.

I've just created a secure linked server definition on my local server that has the remote Azure SQL database as the endpoint. I can view this in Server Objects, Linked Servers as well as in my Azure account under SQL Databases. Note that only system processes can use this linked server; user logins cannot issue queries through the linked server to the remote endpoint.

Now that Stretch Database is enabled for the instance and for the AdventureWorks2014 database I can now stretch my new table. To stretch the table to Azure I need to alter the table and enable remote data archive.

USE [AdventureWorks2014];
GO

ALTER TABLE [StretchTest]
ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE = ON );
GO

In addition to new features with SQL Server 2016, there are some new DMVs as well. To monitor the migration of data to Azure you can query sys.dm_db_rda_migration_status. When I queried the DMV after enabling remote data archive I was able to see that the 6 rows were migrated:

Backup and Restore of a Stretch Database

Currently in SQL Server 2016 CTP2 when a database that is Stretch-enabled is backed up, a shallow backup is created which does not include the data that has been migrated to the Azure SQL database. It is expected that with the RTM release of SQL Server 2016 backing up a Stretch-enabled database will create a deep backup that will contain both local and stretched data.

When restoring a database that is Stretch-enabled, you'll have to reconnect the local database to the remote Azure SQL Database. You do this by running the stored procedure sys.sp_reauthorize_remote_data_archive as a db_owner.

If I now back up the Stretch-enabled AdventureWorks2014 database and restore it, I will no longer be able to query the StretchTest table until I reconnect to Azure SQL Database by running:

USE [AdventureWorks2014];
GO

EXEC sys.sp_reauthorize_remote_data_archive @azure_username, @azure_password;
GO

Once reconnected I get a message similar to the one below and then I'm able to query the Stretched data once again:

When restoring a Stretch-enabled database to another instance, that instance must have "remote data archive enabled". Once you have restored the database and enabled "remote data archive", all that is required is reconnecting to the Azure SQL Database by running the sys.sp_reauthorize_remote_data_archive stored procedure.

The backups for Azure SQL Databases for Basic, Standard, and Premium service tiers are taken every hour. The backup retention period varies depending on the service tier level. At time of writing, for basic it is 7 days, standard 14 days, and premium is 35 days. You can restore Azure SQL Databases by using the Microsoft Azure web portal.

Un-migrate Data

To migrate data back to local storage from an Azure SQL Database you will need to create a new local table with the same schema as the Stretch-enabled table. You then have to copy the data from the Stretch-enabled table into the new local table. Once the data is copied you drop the Stretch-enabled table and rename the new local table to the name of the Stretch-enabled table that was just dropped.

You can only disable Stretch for a database once all Stretch-enabled tables have been dropped. If you drop a database enabled for Stretch, the local database is removed but the remote data is not; you will have to drop the remote database from the Azure management portal.

Summary

Stretch Database is an easy way to migrate archive data to Microsoft Azure, if your database supports it. Currently in SQL Server 2016 CTP2 there are many limitations with table, data, and column properties, data and column types, constraints, and indexes. If you are not restricted by those limitations, then Stretch Database is a simple way to migrate historical data to Azure SQL Database and free up valuable local storage. Managing backups will become a bit more complex since your data will be split between on premise and in the cloud.

I'm looking forward to these restrictions being lifted in the RTM release, and I'm sure many of you will be able to make use of this cool feature.