Extending Your On-Premise databases to the Hybrid Cloud with SQL Server Stretch Databases

There’s no doubt that cloud technology is evolving very rapidly; using the cloud today is no longer a choice between on-premise and the cloud. Instead, many parts of the Windows Server operating system and Microsoft’s server applications have built-in Azure hybrid cloud integration capabilities.

One great example of Microsoft’s hybrid cloud integration is SQL Server’s Stretch Databases feature. First introduced with SQL Server 2016 and carried forward to SQL Server 2017, Stretch Databases enable you to seamlessly extend a database from an on-premise SQL Server deployment to Azure. SQL Server Stretch Databases can be a good hybrid cloud option when you have large amounts of historical data that you need to keep online for a long time and you need to query that data. Stretch Databases can enable you to provide longer data retention times for archiveing data using low-cost Azure storage. It can eliminate the need to expand your local storage which is typically much more expensive than cloud storage. It can also be an attractive option if you can’t get the historical data in your large tables backed up and restored within your allotted backup window.

Requirements

The Stretch Database feature requires that you have SQL Server 2016 or 2017. It is supported by all editions of SQL Server. Not surprisingly, the Stretch Databases feature also requires that you have an Azure account and makes use of Azure SQL Database. To learn more about the costs involved in using Stretch Databases you can look at SQL Server Stretch Database pricing.

Capabilities

SQL Server can seamlessly retrieve both the local data as well as the remote data in Azure in response to a user query. There is additional latency for queries that need to access the remote data. Microsoft provides an Enable Database for Stretch wizard that allows you to select the database tables that you want to extend to Azure. When you enable Stretch Databases, you can select multiple tables from a given on-premise database. You can then select the data that will be stretched to Azure and finally, select to migrate an entire table or you can specify a row filter.

• Entire table – Selecting the entire table will result in all of the rows being migrated from the on-premise instance of SQL Server to Azure SQL DB storage.
• Filtered rows – The row filter optionally enables you to select a subset of rows to move to Azure. You can use a WHERE clause that selects a column from the table then you can use an operator like less then ( < ) or greater then ( > ) and provide a value that’s used to select the rows. You can test your filter before applying it.

The Stretch Database feature will take care of the data movement in the background. Under the covers, the Stretch Database feature implements a linked server from your on-premises SQL Server instance to Azure SQL Database. Stretch Databases are compatible with most data types as well as database security features like Transparent Data Encryption (TDE), Always Encrypted and Row-level Security.

Limitations

However, like you would expect there are some limitations. Some of the most importation Stretch Database limitations include:

• You can’t UPDATE or DELETE rows that have been migrated to Azure
• Tables can’t have more than 1,023 columns or 998 indexes
• Tables can’t be memory-optimized (In-Memory OLTP)
• Tables can’t use FileTables or FILESTREAM data
• Tables can’t use text, ntext, images, timestamp, sql_variant, XML and CLR data types
• Tables can’t use replication
• Tables can’t use Change Data Capture (CDC)
• Uniqueness is not enforced for UNIQUE and PRIMARY KEY constraints
• Tables can’t have Full-text indexes
• You can’t create an index for a view that includes Stretch-enabled tables

Stretch Databases are a great example of how the hybrid cloud can benefit IT operations by seamlessly leveraging cloud storage. You can learn more about hybrid cloud Stretch Databases at Microsoft Docs Stretch Database.