Identify Candidate Tables for SQL Server 2016 Stretch Databases

By:   |   Comments   |   Related: > SQL Server 2016


A new feature in SQL Server 2016, Stretch Database, promises to allow you to archive your historical data transparently. What this means is that you can store the older data in a table in Azure SQL Database, but existing queries and applications won't need to change (they won't have to understand that there are actually two separate objects underneath). But how do you identify which tables are good candidates for this feature?


As part of the new SQL Server 2016 Upgrade Advisor (see previous tip about this preview), there is a new option for analysis: Stretch Database Advisor. This will automatically run against your database(s) and, based on overall size and row count, present a list of tables that are eligible. It will also show when tables have compatibility issues (since not all data types, for example, are supported). Note that the database doesn't currently have to be on SQL Server 2016 to perform the analysis (but of course you won't be able to stretch a table on earlier versions).

You can download the new Upgrade Advisor (Preview 1) here. I'm going to walk through a Stretch Database analysis so you can see how it works, and list out some of the limitations and other considerations you'll want to make before stretching a table.

Launch the Upgrade Advisor, and select Scenarios > Run Stretch Database Advisor:

Run Strech Database Advisor

Click Select Databases to Analyze > SQL Instance, enter your server name (and credentials if necessary), then click Connect:

Connect (click to enlarge)

Now you will be presented with a list of databases to choose from. For simplicity I'm going to choose AdventureWorks2014:

Select Databases

I click Select > Run and then this summary is displayed:

Initial Analysis

You can save the results (here are the export options):

Export Options

But as is, this analysis is not all that useful, as no tables were identified using the default criteria. Thankfully, we can drag those sliders around; here is what happens when I drop the lower bars to 15 MB in size and 10,000 rows:

Revised Analysis

The table Person.Person, which is almost 30 MB, has been identified as a candidate table. However, the advisor warns us about 14 compatibility issues, which we can drill into by clicking on that area:

Compatibility Issues

As you can see, the problem here is that certain table properties, data types, and indexes are ineligible for stretch. And these are only a few that you may come across; the full list is a bit more expansive, which you can see in the documentation:

Note that this list may change before SQL Server 2016 is released. In any case, I am sure that, like In-Memory OLTP and Columnstore indexes, many of these restrictions will be lifted as the feature matures.

In addition to these limitations, you should also consider that a table should be well-suited for archival; namely:

  • That there is a datetime-based column or some other way to determine which data is old or "cold;"
  • A decent percentage of queries should only look at new data (so that the remote data is not always accessed); and,
  • The nature of the old data should be that it does not need to be updated (since the stretch process is a one-time archival action, and the data becomes read-only at that point).

Stretching a table can be a very cost-effective way to archive older data while still making it available for queries. The new Upgrade Advisor can help you decide which tables are good candidates to take advantage of this feature, and will identify potential blockers, but you will need to make some qualitative judgments for yourself as well.

Next Steps

Comments For This Article

get free sql tips
agree to terms