The following article introduces the concept of Stretch Database (Stretch DB), a new feature of SQL Server 2016 and demonstrates examples of its usage.


Microsoft SQL Server 2016 is a hybrid cloud environment designed to allow data and services to be enable users perform advanced analytics within their databases and create business insight visualizations.

One of the highly anticipated features new to Microsoft SQL Server 2016 is Stretch DB, a feature that migrates your historical data transparently and securely to the Microsoft Azure cloud.

Moreover, it can be configured to migrate a complete table to the cloud, or to migrate only part of a table based on certain condition. For example, one won’t migrate its complete transaction table, but migrating the data which is older than 5 years makes sense!


Benefits of Stretch DB

  1. Ability to determine which rows to migrate to the cloud and which rows to keep On-Premise by using predicates.
  2. Stretch DB ensures that no data is lost if a failure occurs during migration. It also has retry logic to handle connection issues that may occur during migration.
  3. Data Migration can be paused to troubleshoot problems on the local server or to maximize the available network bandwidth.
  4. Existing Applications does not have to be changed. They can query the tables seamlessly.


Is Stretch DB for you?

Stretch Database targets transaction databases with large amounts of historical data, typically stored in a small number of tables. These tables may contain more than a billion rows. Some cases where Stretch DB can be used are:

  1. Transaction data is stored for a long time
  2. Historical Data is queried occasionally
  3. The size of tables are exponentially increasing and needs more storage

To identify databases and tables that can be “stretched”, Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor.

Stretch DB Advisor

Download and install Upgrade Advisor from here. This tool is not included on the SQL Server 2016 Release Candidate (RC0) installation media.

To verify if a database and table is eligible for Stretch DB, open upgrade advisor and click on “Run Stretch Database Advisor”.

a. Select the server where the Database resides

b. Select the Database to be stretched

 c. Select the tables to stretch and view the results

Configure and Use Stretch DB

Enable Stretch DB at server level

Before a Database can be stretched, Stretch DB needs to be configured at the server level.

EXEC sp_configure 'remote data archive' , '1';

This operation requires sysadmin or serveradmin permissions.

Enable Stretch Database on a database

The easiest way to get started is to use the wizard.

a. Go to Tasks > Stretch > Enable

b. This will lead to a Stretch DB Intro Page

c Then, select the tables that needs to be stretched

d. Sign in to your Microsoft Azure Account

e. Once signed-In, you may create a new server or connect to an existing SQL Server

f. Create a Master Key

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.

g. The wizard will then create the server and perform the configurations to stretch the Database and the required tables

Monitor the Data Migration

To monitor the Data Migration progress, at the database level, go to Tasks > Stretch > Monitor

The previous operation would migrate all the data to the cloud.

Query & Verify the Execution Plan

After a few minutes, when querying the table transactions, all the data will be fetched from the cloud and no predicate was specified when enabling stretch on the table.



Migrate only Specific rows

Bring the data from the cloud back to on premise

To migrate only specific rows, start by first bringing all the data from the cloud to the local database.

To do so, click on stretch > Disable > Bring Data back from Azure

All the records would be migrated back to on-premise gradually.

Define the function to do filtering

The inline table-valued function required for a Stretch Database filter function looks like the following example.

CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datetime)
RETURN SELECT 1 AS is_eligible
              WHERE @column1 < CONVERT(datetime, '4/1/2016', 101)

The parameters for the function have to be identifiers for columns from the table. Schema binding is required to prevent columns that are used by the filter predicate from being dropped or altered.

If the function returns a non-empty result, the row is eligible to be migrated; otherwise - that is, if the function doesn't return any rows - the row is not eligible to be migrated.

Alter the table

The next step is to alter the table and set remote_archive on while specifying the predicate.

        FILTER_PREDICATE = dbo.fn_stretchpredicate(DATE),

Monitor Migration

There are 67,620 records which are dated before 4/1/2016 which should be migrated to the cloud.

Query & Verify Execution Plan

a. Select data where Date < 4/1/2016, as expected, data will be fetched form the cloud

b. Select data where product if = 4139. This is a more interesting scenario, where both the On-Premise and Cloud data is queried and the result is merged and returned to the user.


Limitations of Stretch DB

The development of Stretch DB is still in progress and as at the time of writing, there are lots of properties that is not yet supported by Stretch DB. For example, tables with more than More than 1,023 columns, and More than 998 indexes. The complete list of Stretch DB Limitations can be consulted here.


Stretch Database is an easy way to migrate archive data to Microsoft Azure, if your database supports it. Currently in SQL Server 2016 there are several limitations as described above. It would be great if those can be supported gradually for a higher adoption of this feature. But this is a great initiative from Microsoft, powering the local databases with cloud capabilities is awesome!


  1. Stretch Database
  2. Enable Stretch Database for a database
  3. Enable Stretch Database for a table
  4. Use a filter predicate to select rows to migrate (Stretch Database)