SQL Server 2016: Concepts and architecture for Stretch Database

The new feature set within SQL Server 2016 (in CTP2.2 as of today), not just simple enhancements or easy features there will be a big changes to come as compared to previous releases.

As there is a flood of information about What’s new or top features in SQL Server 2016  I’m keen on newest feature: Stretch Database concept.

What is a stretch database?

A different name to what you call as Archive process, the stretch database management lets us to archive any historical data in a transparent manner. There is a facility to archive/store data in Azure cloud for migration. The main advantage is, no need to change existing queries or client applications.

Few set of questions reference:

Is Stretch Database for you?

If you can make the following statements, Stretch Database may help to meet your requirements and solve your problems.
If you’re a decision maker

  •  I have to keep transactional data for a long time.
  • Sometimes I have to query the historical data.
  • I have apps, including older apps, that I don’t want to update.
  • I want to find a way to save money on storage.
  • The size of my tables is getting out of control.
  • My users say that they want access to historical data, but they only rarely use it.
  • I have to keep buying and adding more storage.
  • I can’t backup or restore such large tables within the SLA.

Where to begin?

All you need is Azure account, if you don’t have one then get an Azure account.

Using the following method within your local instance as follows:

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


Then we have an opportunity to select a database or number of tables to enable Stretch option.

Enable Stretch for a database by using SQL Server Management Studio

  1. In SQL Server Management Studio, in Object Explorer, select the database for which you want to enable Stretch.

  2. Right-click and select Tasks, and then select Enable Database for Stretch.

  3. On the Microsoft Azure Sign-in page of the wizard, sign in to your Azure account and select the subscription to use for billing.

  4. On the Stretch Settings page of the wizard, do the following things to configure the Azure SQL Database server.

    1. Select an Azure location for the server.
    2. Create the credential for the server.
    3. Configure a firewall rule to allow connections to the server.
  5. On the Summary page of the wizard, review your choices and then click Finish.

Enable Stretch for a table to start migrating historical data to Azure SQL Database.

Make sure that the account used to connect your local instance and Azure instance has db_owner permissions on the both sides, along with ALTER permissions on the involved tables.

Enable Stretch for a table by using SQL Server Management Studio

  1. In SQL Server Management Studio, in Object Explorer, select the table for which you want to enable Stretch.

  2. Right-click and select Enable Stretch.

The best option to enable stretch database is to identify transactional databases with large dataset of historical data, that is stored and contained nearly millions/billion rows.


How to identify relevant databases and tables for Stretch Database?

All you need is SQL Server 2016 Upgrade Advisor tool (sounds familiar), which has a Stretch Database Advisor to identify relevant databases and tables for Stretch Database. Download SQL Server 2016 Upgrade Advisor tool. Mind you, this is an advisor not a definitive identification to enable relevant feature. As a database management owner you must have specific information to accompany the list of tables and databases within your data platform.

stretch DB adv

As always any new feature that is associated with new release of SQL Server, make sure you understand about requirements and limitations for Stretch Database.

Finally, here is the list of features (from BOL) on tasks associated to databases or data management:

Databases Cursors Database Checkpoints (SQL Server)
Tables Sequence Numbers Back Up and Restore of SQL Server Databases
In-Memory OLTP (In-Memory Optimization) DDL Triggers Bulk Import and Export of Data (SQL Server)
Indexes DML Triggers Data Compression
Partitioned Tables and Indexes Synonyms (Database Engine) OLE Automation Objects in Transact-SQL
Views JSON Data (SQL Server) Event Notifications
Stored Procedures (Database Engine) XML Data (SQL Server) Monitor and Tune for Performance
Search (SQL Server) Spatial Data (SQL Server) Temporal Tables
User-Defined Functions Binary Large Object (Blob) Data (SQL Server)
Statistics Data-tier Applications
Plan Guides The Transaction Log (SQL Server)