Stretch Databases: The Basics

What Is It?

A stretch database is a database where the rarely used portions of the data are stored in the cloud. Offered by SQL Server 2016 alongside Azure, it will allow seamless migration of data between the cloud and your local server databases.

Consider data as being warm or cold; warm data is accessed often, cold is not. So for all intents and purposes, cold data is wasting space on the local server most of the time it’s there. The feature was created with historical data and other similar types of data in mind. Data that would not be accessed regularly, but still needs to be available on occasion.

A stretch enabled database can have tables that query as if they are local, yet have some of their rows stored in Azure blob storage. The only noticeable difference by the user is that some queries take longer to process.

There is a fair amount of control over data migration and application integration. Functions can be created to stretch specific rows, based custom filter functions. Migration can be monitored and troubleshooted, or simple paused and resumed at will.

Once the data is in the cloud it is backed up automatically, with Azure holding storage snapshots from the last 7 days for easy recovery by the user. Any backups of the stretch enabled database actually only takes the local data – migrated data can only be restored using the Azure storage snapshots.

Requirements And Prerequisites

The main requirement is having an Azure account with a billable subscription already set up. Blob storage is set up during the stretch database setup, so nothing further is needed on the Azure side.

The Stretch Database Advisor, included in the separately downloaded Data Migration Assistant, can indicate which tables are eligible for stretching to Azure (I’m planning on going through this in a future blog post). They do not mention what incompatibilities the other tables have, however.

Any table that is not shown as compatible must be manually examined, to see what incompatibilities there are. I have included a full list of incompatible features, column types, and data types below.

Limitations And Incompatibilities

As of writing this, there are still a large number of index, constraint, and data type limitations on tables that use stretch databases according to the MSDN website. Data management operations are also limited – you cannot update or delete rows marked for migration, or that have already been migrated. These rows must be migrated back to the local database in order to be altered.

Table Properties

· Memory optimised tables cannot become stretch enabled

· Replicated tables cannot become stretch enabled.

· Tables using change tracking and change data capture cannot become stretch enabled.

Column And Data Types

· A table cannot be stretch enabled if it has more than 1,023 columns.

· FileTables and Filestream data are incompatible.

· Column set and computed columns are incompatible.

· Text, ntext, image, timestamp, sql_variant, XML and CLR data types are all incompatible.

Constraints

· Uniqueness is not enforced on migrated Unique and Primary Key constraints.

· Default and Check constraints are incompatible.

· Foreign Key constraints cannot reference a stretch enabled table

Indexes

· Indexed views are incompatible.

· Fulltext, XML, and Spatial indexes are incompatible.

· A table cannot be stretch enabled if it has more than 998 indexes.

· Filtered indexes do not include migrated data on stretch enabled tabled.

Further Reading

The MSDN website has the full description of stretch databases

SQL Data Partners have also done an interview with Anthony van Gemert, one of the product managers for the stretch feature