SQL Server 2016 introduces a new feature called Stretch Database, which allows you to archive your historical data transparently. This means that you can store older data in a table in Azure SQL Database without requiring any changes to existing queries and applications. But how do you identify which tables are good candidates for this feature?

The new SQL Server 2016 Upgrade Advisor includes a Stretch Database Advisor that can automatically analyze your database(s) and provide a list of eligible tables based on their overall size and row count. It can also identify compatibility issues, as not all data types are supported for stretching.

To perform a Stretch Database analysis, launch the Upgrade Advisor and select the “Run Stretch Database Advisor” option under the Scenarios menu. Connect to your SQL Instance and choose the database you want to analyze. By default, the analysis may not identify any tables, but you can adjust the criteria by dragging the sliders for size and row count.

Once the analysis is complete, the advisor will display a summary of the results. You can save the results or export them for further analysis. If any tables are identified, you can drill down into the compatibility issues to understand why certain table properties, data types, and indexes may be ineligible for stretching.

It is important to note that there are certain requirements and limitations for Stretch Database, which you should consider before deciding to stretch a table. Firstly, the table should have a datetime-based column or some other way to determine which data is old or “cold.” Additionally, a significant percentage of queries should only access new data to avoid frequent access to remote data. Lastly, the old data should not require updates, as the stretch process is a one-time archival action and the data becomes read-only.

Stretching a table can be a cost-effective way to archive older data while still keeping it available for queries. The Upgrade Advisor’s Stretch Database Advisor can assist in identifying suitable tables and potential blockers, but ultimately, you will need to make qualitative judgments based on your specific requirements.

Stay tuned for more articles on SQL Server 2016 features and best practices!