Limitations for Stretch Database in SQL

We know that Stretch Database is a Microsoft solution for securely migrating cold data to Azure with remote query processing abilities. The biggest fact is that it does not require any change in your business application code and SQL data components. We have options to choose the pricing tier and configure settings in the Azure Portal to maintain control over price and costs as per our business needs and have the facility to scale up or down whenever it required.

This is the great feature of SQL Server 2016 but it has some limitations which cannot work in Stretch databases such as-

A. Limitations that currently prevent us from enabling Stretch for a table – There are some table properties, data types, column types, Constraints and Indexes that stop us from enabling Stretch for a table which are given below:

1. Table Properties: If a table contains more than 1,023 columns or more than 998 indexes then we can enable that table for Stretch. Memory-optimized tables do not fall for Stretching also. FILESTREAM data is not workable for Stretch.
2. Data types - Stretch is not workable for text, ntext and image, timestamp, sql_variant, XML and CLR data types including geometry, geography, hierarchyid, and CLR user-defined types.
3. Column types - Stretch is not appropriate for COLUMN_SET and Computed columns also.
4. Constraints - If a table contains default constraints and check constraints and having foreign key constraints that reference the table then we cannot enable it for stretching. In a parent-child relationship case, we can enable Stretch for the child table but not for the parent table.
5. Indexes - If a table contains full text indexes, XML indexes, Spatial indexes and Indexed views for reference the table then we cannot enable it for stretching.

B. Limitations for Stretch-enabled tables – If our table is enabled for stretching then it should have the following limitations-

1. Constraints - Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.
2. DML operations - We cannot UPDATE or DELETE rows from the stretch-enabled table that have been migrated, or rows that are eligible for migration. The same condition is applicable for a view that includes Stretch-enabled tables. If you are working on linked server then you can't INSERT rows into a Stretch-enabled table.
3. Indexes - We cannot create an index for a view that includes Stretch-enabled tables. Another thing is that filters on SQL Server indexes are not propagated to the remote table.

Limitations for Stretch Database in SQL Server
Enable Database for Stretch to Azure

References: https://msdn.microsoft.com/en-us/library/dn935011.aspx

Stretch Database in SQL Server 2016

Stretch Database is a Microsoft solution for securely migrating cold data to Azure with remote query processing abilities which does not require any change in your business application code and SQL data components. It provides the most significant benefit from the low cost of Azure rather than scaling expensive, on-premises storage. We have options to choose the pricing tier and configure settings in the Azure Portal to maintain control over price and costs as per our business needs and have the facility to scale up or down whenever it required.
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.

Seamlessly access SQL Server data regardless of whether it is on-premises or stretched to the cloud. We have  advanced security options those also work with Stretch Database and move it into Azure SQL Database in the cloud in a secure fashion.

With Stretch Database, backups for your on-premises data run faster and complete more easily than they did on the un-stretched data and easy to turn on Stretch Database from SQL Server Management studio. Use the Azure portal to configure additional settings and choose a performance level, scaling up or down as needed while maintaining control over cost.
Benefits of Stretch Database
It is easy to turn on Stretch Database from SQL Server Management studio. Use the Azure portal to configure additional settings and choose a performance level, scaling up or down as needed while maintaining control over cost. Stretch Database provides the following benefits:

• Scale SQL Server 2016 using bottomless cloud storage
• Make warm and cold data available to users at low cost
• Access and query stretched data online
• Move data easily—no query or application changes required
• Use with advanced security features like Always Encrypted
• Reduce maintenance and storage costs for on-premises data

What does Stretch Database work?

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. There are some conditions which are based on the data availability such as cold and hot data transaction.  

For an example, if we are storing our cold data and hot data in a separate tables then we can migrate the entire table.
In the other hand, if we are storing both hot and cold data in a single table then we can specify a filter function to select the rows to migrate.
In this way, we don't have to change existing data components such as procedures, function, SQL queries and client apps. We can 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.

Suitable data components for Stretch Database?

Stretch Database targets transactional databases with large amounts of cold data, typically stored in a small number of tables. These tables may contain more than a billion rows. Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor, to identify databases and tables for Stretch Database.

Limitations for Stretch Database in SQL Server
Enable Database for Stretch to Azure

References: https://msdn.microsoft.com/en-us/library/dn935011.aspx