SQL Server Stretch is a technology introduced in SQL Server 2016 that allows to select tables and decide to keep part of the data in Azure Cloud and part of the data on premises.

The idea behind this technology is to keep the "cold data" in the cloud and the "hot data" on the premises

When thinking of Stretching your DB consider the following:

• The size of my tables is getting out of control
• My users say that they want access to cold 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.

In order to identify databases and tables that are candidates for Stretch Database, download SQL Server 2016 Upgrade Advisor and run the Stretch Database Advisor

After you enable Stretch Database for a SQL Server instance:

•  a database, and at least one table, Stretch Database silently begins to migrate your cold data to Azure.
•  You don't have to change existing queries and client apps. You continue to have seamless access to both local and remote data, even during data migration. There is a small amount of latency for remote queries, but you only encounter this latency when you query the cold data.
• Stretch Database 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. A dynamic management view provides the status of migration.
• You can pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth.

 Benefits using Stretch DB:

• Provides cost-effective availability for cold data
  Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database.
• Doesn’t require changes to queries or applications
  Access your SQL Server data seamlessly regardless of whether it’s on-premises or stretched to the cloud.
• Streamlines on-premises data maintenance
  Reduce on-premises maintenance and storage for your data. Backups for your on-premises data run faster and finish within the maintenance window. Backups for the cloud portion of your data run automatically.
• Keeps your data secure even during migration
  Enjoy peace of mind as you stretch your most important applications securely to the cloud. SQL Server’s Always Encrypted provides encryption for your data in motion.
  Though, There are some limitations: 

  Data Type Not Supported with Stretch DB	Features Not Supported with Stretch DB
  filestream  timestamp sql_variant XML geometry geography hierarchyid CLR user-defined types (UDTs)	Computed Columns  Check constraints Foreign key constraints that reference the table Default constraints XML indexes Full text indexes Spatial indexes Clustered columnstore indexes Indexed views that reference the table

Once Stretch database is configured, it can be monitored by the following options:

1. Using T-SQL

— monitor synchronization status

select * from sys.dm_db_rda_migration_status

2. By the Wizard, select the monitor item in the stretch menu

For Summery:

•   The stretch database feature is a very nice and good feature to get with the release of SQL Server 2016.
•   Stretch Database is an easy way to migrate archive data to Microsoft Azure, if your database supports it
•   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.
•   It enables the DBA to handle historical data and storage capacity without having the consult the developers and/or architects of new solutions.

Below you will find a script that helps delivering Stretch DB. The script uses Adventure works Microsoft Sample adding some of my own (you will need to download AdventureWorks2016CTP3 DB)