Stretch database feature in SQL Server 2016

Stretch Database:

It allows you to store a portion of a table in the Azure SQL Database.

Stretch Database feature offer local server performance for latest data and cloud storage for old data without any change to the application.

Users are mostly interested in latest data, with this feature latest data will be available in your local server where as the historical information will be moved to Azure.

When you enable stretch database, it creates a second database that is hosted in Azure. Then when you mark a table as “stretch”, SQL Server will automatically start moving its data into the cloud.

Currently only “archive table” mode is enabled, which assumes that it is working on a history table and moves all of the rows.

The “archive row” mode, which hasn’t been released yet, will use a WHERE clause to determine which rows to archive.

Common scenarios include rows that are more than a year old or have a flag indicating that the row is no longer live (e.g. completed orders).

No need to change anything in application : The SQL to query a stretch table is exactly the same as the SQL needed to query for a normal table. The query execution engine will automatically take care of distributing the query between the local and Azure-based server. This means you can enable stretch on a database without any changes to the applications using it.

Needs changes in backup and restore mechanism: Normal backups only include the locally hosted data. A full backup, including data located in the stretch database, will require a different procedure.

Limitations:

Below column types are not supported:

1. filestream
2. timestamp
3. sql_variant
4. XML
5. geometry
6. geography
7. hierarchyid
8. CLR user-defined types (UDTs)

Below features also not supported:

1. Column Set
2. Computed Columns
3. Check constraints
4. Foreign key constraints that reference the table
5. Default constraints
6. XML indexes
7. Full text indexes
8. Spatial indexes
9. Clustered columnstore indexes
10. Indexed views that reference the table
    For more information on how to enable the Stretch database feature refer SQL Server 2016 stretch database feature