Introduction
SQL Server is one of the most widely used RDBMS around the world, and one of the reasons for this wide adoption comes from Microsoft’s commitment to add new capabilities that cater to the need of the changing landscape year after year. Along that line, SQL Server 2016 introduces several new features, one of which is Stretch Database. Stretch Database lets you move or archive your cold data from a local SQL Server database to Azure SQL Database transparently (without writing code for data movement) so that your local SQL Server database has less overhead in maintaining them (and hence increases overall performance) while at the same time, stretched data is online allowing applications to access it like any other table. In this article series, I am going to talk in detail about this new capability in SQL Server 2016.

Understanding Stretch Database (or in Short, StretchDB)
The Stretch Database feature securely and transparently archives your cold or historical data from a local SQL Server database to Azure SQL Database (the SQL Database service in Microsoft Azure Cloud is provided as a Platform as a Service [PaaS]) with remote query processing capability. Once you enable this feature for a table, SQL Server silently and transparently moves/migrates data to Azure SQL Database. These are some of the benefits of using this feature:

Secure and transparent movement of cold or historical data without writing a data movement module; makes local queries and other database operations run faster as they have to work on hot data or local data most of the time
Storage of cold data in Azure SQL Database is cost effective; that is, there is reduced cost and complexity in keeping cold data online in Azure SQL Database Archived data remains online and they are query-able like any other table in local SQL Server database
No application change is required to access these archived tables or data; You can still have a single query accessing these two types of data or tables at a given time or in the same query
You can pause data migration to troubleshoot any issues (or to minimize load on network bandwidth) and resume it once you are done
It has a inbuilt retry mechanism, which ensures no data is lost if a failure happens during data migration and does the data reconciliation automatically Reduced time for maintenance for your local data (re-index, performance tuning etc.)
Reduced time for backup and restore of your local database as it deals with only hot data

The good part of this feature is, (even though cold data is stored externally in Azure SQL Database but they are online), it is transactionally consistent and works with other SQL features like Always Encrypted, Row Level Security, etc.

What to Stretch and how to Decide
Typically, large transaction tables with large amounts of historical data might benefit from enabling them for stretch. Think of a massive table with hundreds of millions or billions of rows with 70%-80% cold data that users need to maintain online indefinitely. However, most of the time, only 20%-30% of hot data gets accessed but cold data also needs to be online even though they are accessed infrequently.

Related Articles
10 New Features Worth Exploring in SQL Server 2016
With SQL Server 2016 Community Technology Preview (CTP) 2, you can enable this feature on a table from your local SQL Server database and all the data from that specific table gets moved or migrated to the Azure SQL database transparently. This means, you need to first move data (whatever you consider as cold data) that you want to migrate, into another history table locally and then enable this feature on that specific local history table. There are different ways to move data from the main transaction table to a history table; for example you can use partition switching, stored procedure with data movement logic or the SQL Server Integration Services (SSIS) package, etc.

As the StretchDB feature evolves, you might expect to have a single table with both hot and cold data (a single table with mixed data) and you can enable the StretchDB feature on that specific table by specifying a filter predicate to move only cold data from that table to Azure SQL database. This will also relieve you from the overhead of moving cold data from the main table to another history table locally.

Identifying Potential Tables for Enabling the StretchDB Feature with SQL Server 2016 Upgrade Advisor

SQL Server 2016 provides a standalone utility to let users from prior versions of SQL Server to run set of rules to identify potential issues and blockers when upgrading to SQL Server 2016. This utility also includes capability to identify potential tables for enabling the StretchDB feature.

To use SQL Server 2016 Upgrade Advisor, you need to first download it from here as it is not part of the SQL Server 2016 CTP 2 installation media. Once installed, you can launch it and choose the “Run Stretch Database Advisor” wizard as shown in the figure below:

Full article...