Stretch Database in SQL Server 2016
Contributions, Microsoft Azure, SQL Server 2016

Overview of New Feature- Stretch Database in SQL Server 2016

SQL Server 2016 has the new feature to store cold data transparently and securely to the Microsoft Azure cloud. This new feature is known as Stretch Database and the feature will be beneficial to those needing to keep transactional data for long periods of time and those looking to save money on storage. Being able to seamlessly migrate data to the Microsoft Azure Cloud will give you the ability to archive data without having to change the way your applications query the data. Stretch Database migrates entire tables.

If your database is already set up to store archive data in separate tables from current data, you will be able to easily migrate the archive data to Azure. Once you enable Stretch Database, it will silently migrate your data to an Azure SQL Database. Stretch Database leverages the processing power in Azure to run queries against remote data by rewriting the query. You will see this as a “remote query” operator in the query plan.

Pictorial Layout to know working mythology:-

Stretch Database in SQL Server 2016
Image Source: Microsoft Developer Network

 

Stretch Database may help to meet your requirements and solve your problems. Please look at the below tables to make a decision.

If you’re a decision maker

If you’re a DBA

Keep transactional data for a long time. The size of my tables is increasing day by day and getting out of control.
Sometimes I have to run query onto the cold data. My users say that they want access to cold data, but they only rarely use it.
I have some applications, including older apps that I don’t want to update. I have to keep buying and adding more storage.
I have to find a way to save money on storage. I can’t backup or restore such large tables within the SLA.

Prerequisite: Enable Stretch Database on the server:-

Before you can enable Stretch Database on a database or a table, you have to enable it on the local server. This operation requires sysadmin or serveradm in permissions.

  • If you have the required administrative permissions, the Enable Database for Stretch wizard configures the server for Stretch.
  • If you don’t have the required permissions, an administrator has to enable the option manually by running sp_configure before you run the wizard, or an administrator has to run the wizard.

To enable Stretch Database on the server manually, run sp_configure and turn on the remote data archive option. The following example enables the remote data archive option by setting its value to 1

 

Limitations for Stretch Database:-

Not all tables will be eligible for being Stretch-enabled. Certain table properties, data and column types, constraints, and indexes are not supported, such as:

  • The Tables which are Memory-optimized and replicated.
  • Tables that contain FILESTREAM, Change Tracking and Change Data.
  • Data types such as timestamp, sql_variant, XML, geography or columns that are Always Encrypted
  • Check and default constraints or foreign key constraints that reference the table
  • XML, full-text, spatial, clustered columnstore and indexed views that reference the Stretch-enabled table
  • You cannot run UPDATE, DELETE, CREATE INDEX and ALTER INDEX operations on a Stretch-enabled table

 

Conclusion:

Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database. Unlike typical cold data storage, your data is always online and available to query. You can provide longer data retention timelines without breaking the bank for large tables like Customer Order History. Benefit from the low cost of Azure rather than scaling expensive, on-premises storage.

Useful Links:

About The Author