SQL Server 2016’s new stretch database feature promises to offer local server performance for hot data and cloud storage for old data without any change to the application. The basic use case is a table that contain a small amount of hot data that users normally care about and a large amount of old data that should have been moved to off-line archive but the users still want to be able to query it.
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 enable, 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).
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.
Backup and restore will need to change when using this model. Normal backups only include the locally hosted data. A full backup, including data located in the stretch database, will require a different procedure.
There are limitations for using stretch databases. The following column types are not supported:
- filestream
- timestamp
- sql_variant
- XML
- geometry
- geography
- hierarchyid
- CLR user-defined types (UDTs)
Furthermore, stretch tables don’t support the following features
- Column Set
- 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