Finding Tables Suitable for Stretch Databases in SQL Server 2016


Most of the DBAs must be familiar with SQL Server upgrade advisor, which we run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

We can identity the tables which can be part of Stretch Database using SQL Server 2016 upgrade advisor, SQL Server 2016 upgrade advisor has different options to analyse a regular database and to analyze for Stretch Database. To get started, first download and install SQL Server 2016 upgrade advisor on a test server. Launch the SQL Server 2016 upgrade advisor and select “Scenarios” option -> choose ‘Run Stretch Database Advisor” -> Choose “Select Databases to Analyze” -> Provide SQL Instance name and make the successful connection to the SQL instance against which we want to run the analysis -> Select one or more databases from the list of databases list provided -> Run the analysis. It will take a while for the analysis to complete, after which we will be presented with a summary page of the analysis. We can save the results to HTML file or CSV file or other options as and when available.

But there are many limitations to the tables to be part of Stretch Database at this point of time. Below are some of the limitations.

– Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure SQL table that contains the migrated data.
– Insert/Delete operations are not supported and also insert is not allowed through linked servers as well.
– Views cannot be created on the stretch enabled tables. Filters on SQL Server indexes are not propagated to the remote table.
– There are many other limitations on which data types cannot be used for the tables columns, some database properties are not supported like filetables, Memory-optimized tables, etc.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Leave a Reply

Your email address will not be published. Required fields are marked *