Understanding Stretch Database Functionality in SQL Server 2016

Introduction

SQL Server is one of the most widely used RDBMS around the world, and one of the reasons for this wide adoption comes from Microsoft’s commitment to add new capabilities that cater to the need of the changing landscape year after year. Along that line, SQL Server 2016 introduces several new features, one of which is Stretch Database. Stretch Database lets you move or archive your cold data from a local SQL Server database to Azure SQL Database transparently (without writing code for data movement) so that your local SQL Server database has less overhead in maintaining them (and hence increases overall performance) while at the same time, stretched data is online allowing applications to access it like any other table. In this article series, I am going to talk in detail about this new capability in SQL Server 2016.

Understanding Stretch Database (or in Short, StretchDB)

The Stretch Database feature securely and transparently archives your cold or historical data from a local SQL Server database to Azure SQL Database (the SQL Database service in Microsoft Azure Cloud is provided as a Platform as a Service [PaaS]) with remote query processing capability. Once you enable this feature for a table, SQL Server silently and transparently moves/migrates data to Azure SQL Database. These are some of the benefits of using this feature:

  • Secure and transparent movement of cold or historical data without writing a data movement module; makes local queries and other database operations run faster as they have to work on hot data or local data most of the time
  • Storage of cold data in Azure SQL Database is cost effective; that is, there is reduced cost and complexity in keeping cold data online in Azure SQL Database
  • Archived data remains online and they are query-able like any other table in local SQL Server database
  • No application change is required to access these archived tables or data; You can still have a single query accessing these two types of data or tables at a given time or in the same query
  • You can pause data migration to troubleshoot any issues (or to minimize load on network bandwidth) and resume it once you are done
  • It has a inbuilt retry mechanism, which ensures no data is lost if a failure happens during data migration and does the data reconciliation automatically
  • Reduced time for maintenance for your local data (re-index, performance tuning etc.)
  • Reduced time for backup and restore of your local database as it deals with only hot data

The good part of this feature is, (even though cold data is stored externally in Azure SQL Database but they are online), it is transactionally consistent and works with other SQL features like Always Encrypted, Row Level Security, etc.

What to Stretch and how to Decide

Typically, large transaction tables with large amounts of historical data might benefit from enabling them for stretch. Think of a massive table with hundreds of millions or billions of rows with 70%-80% cold data that users need to maintain online indefinitely. However, most of the time, only 20%-30% of hot data gets accessed but cold data also needs to be online even though they are accessed infrequently.  

With SQL Server 2016 Community Technology Preview (CTP) 2, you can enable this feature on a table from your local SQL Server database and all the data from that specific table gets moved or migrated to the Azure SQL database transparently. This means, you need to first move data (whatever you consider as cold data) that you want to migrate, into another history table locally and then enable this feature on that specific local history table. There are different ways to move data from the main transaction table to a history table; for example you can use partition switching, stored procedure with data movement logic or the SQL Server Integration Services (SSIS) package, etc.

As the StretchDB feature evolves, you might expect to have a single table with both hot and cold data (a single table with mixed data) and you can enable the StretchDB feature on that specific table by specifying a filter predicate to move only cold data from that table to Azure SQL database. This will also relieve you from the overhead of moving cold data from the main table to another history table locally.

Identifying Potential Tables for Enabling the StretchDB Feature with SQL Server 2016 Upgrade Advisor

SQL Server 2016 provides a standalone utility to let users from prior versions of SQL Server to run set of rules to identify potential issues and blockers when upgrading to SQL Server 2016. This utility also includes capability to identify potential tables for enabling the StretchDB feature.

To use SQL Server 2016 Upgrade Advisor, you need to first download it from here as it is not part of the SQL Server 2016 CTP 2 installation media. Once installed, you can launch it and choose the “Run Stretch Database Advisor” wizard as shown in the figure below:

Launching SQL Server 2016 Upgrade Advisor
Figure 1 – Launching SQL Server 2016 Upgrade Advisor

On the first screen of the wizard, you need to select your source database(s), which you want to analyze for identifying potential tables for stretching it to the cloud (Azure SQL Database).

Analyzing a database to upgrade
Figure 2 – Analyzing a database to upgrade

When you run the tool against the identified database, it reports back the tables and if there are any potential issues or blockers in migration. For example, as highlighted in the figure below, you can see that for a table to be migrated it should not contain CHECK constraints, Foreign key constraints, XML data types, etc. For a complete list of surface area limitations and blocking issues, please refer to this list. Please note, right now this is based on SQL Server 2016 CTP2 and might change over time as it transitions to RTM.

Analyzing tables to upgrade
Figure 3 – Analyzing tables to upgrade

Things to Know before Starting on Utilizing StretchDB

As I discussed earlier, when you enable the StretchDB feature for a table, SQL Server transparently migrates data from an on-premise local table to a table in Microsoft Azure (more specifically Azure SQL Database). SQL Server does it by creating a Linked Server locally, which has a remote endpoint as target to move data to, as shown in figure below from product documentation.

High level architecture of Stretch Database feature
Figure 4 – High level architecture of Stretch Database feature

In the context of the StretchDB feature, data can be classified into these three categories:

  • Local Data – represents the data in local on-premise table(s), which are not supposed to move to Azure SQL Database or they are supposed to be in local on-premise table(s) only. 
  • Eligible Data – represents the data in local on-premise table(s), which are still to be migrated to Azure SQL Database based on configurations defined.
  • Remote Data – represents migrated data in table(s) in Azure SQL Database in Microsoft Azure Cloud Platform.

Apart from that, you also need to understand that Local Database represents the local on-premise database, which can be enabled for the StretchDB feature whereas Remote Endpoint represents the location of Azure SQL Database in Azure where remote data is stored.

Note – The feature mentioned and demonstrated in this article is based on SQL Server 2016 CTP 2.4 and might change when RTM becomes available or in future releases.

Conclusion

Stretch Database is a new feature in SQL Server 2016, which lets you move or archive your cold data from a local SQL Server database to the Azure SQL Database transparently (without writing code for data movement) so that your local SQL Server database has less overhead in maintaining them (and hence overall increasing the performance) and at the same time, stretched data is online allowing applications to access it like any other table.

In this article we were introduced to this new feature, learned ways to identify potential tables for stretching and understood some of the key concepts to get started with this feature. In the next article of this series, I am going to demonstrate how this feature works and how to quickly get started with it.

Resources

Stretch Database Documentation

What’s New in SQL Server 2016

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles