Identify Candidate Tables for SQL Server 2016 Stretch Databases
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:
Click Select Databases to Analyze > SQL Instance, enter your server name (and credentials if necessary), then click Connect:
Now you will be presented with a list of databases to choose from. For simplicity I'm going to choose AdventureWorks2014:
I click Select > Run and then this summary is displayed:
You can save the results (here are the 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:
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:
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.
- See these tips and other resources:
- Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor
- Stretch Database (MSDN)
- Requirements and limitations for Stretch Database
- SQL Server 2016 Features in CTP2
- SQL Server 2016 Tips
About the authorAaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.
View all my tips