Introduction to SQL Server Stretch Database

In many cases Azure SQL Database offers an economically and functionally viable alternative to SQL Server deployments. However, there are also scenarios where we might discover that rather than serving as a replacement, it provides synergy, working side by side with your on-premises databases. One of technologies that illustrate this paradigm is Stretch Database, introduced in SQL Server 2016. We will describe its basic characteristics and review its implementation steps in this article.

As the name indicates, the primary purpose of the Stretch Database is to implement a cross-premises database, with some of its content hosted in an on-premises instance of SQL Server 2016 and the reminder residing in an Azure SQL Database. Despite splitting data across two locations, you do not have to modify the way it is accessed, effectively eliminating the need to update existing database-dependent applications. At the same time, you can take advantage of easily scalable cloud compute and storage resources. As a side benefit, you can minimize duration of an on-premises maintenance window by reducing the amount of data to be backed up. Data migrated to Azure remains protected by automatic backups available as part of the standard Azure SQL Database offering. From the security standpoint, Stretch Database supports the range of features incorporated into SQL Server 2016 and Azure SQL Database V12, including Always Encrypted, Row Level Security, and Transparent Data Encryption.

Before you start planning implementation of Stretch Database you should first determine whether your data and its usage patterns comply with its requirements. There are a number of potentially blocking issues and limitations applicable to Stretch Database that you need to consider. Some of these limitations result from restrictions imposed by the capabilities of Azure SQL Database, such as lack of support for tables that contain FILESTREAM data; however there are number of others that are Stretch Database-specific. For example, there is currently no support for text, ntext, XML, or CLR user-defined data types, default and check constraints, or full-text and XML indexes. You should also note that You should also note that updating or deleting rows that have been either migrated to Azure SQL Database or designated as eligible for migration is not supported at this time. In addition, keep in mind that unique and primary keys are not enforced for data transferred to Azure. For a comprehensive list of the limitations of Stretch Database, refer to the Azure Documentation.

Once you have confirmed that your database and its tables do not violate any of the Stretch Database prerequisites, you need to apply the following configuration changes in order to migrate cold data to Azure:

• Enable the Stretch Database functionality on the SQL Server 2016 instance hosting the database you intend to stretch. This is accomplished by configuring the remote data archive server configuration option, which you can apply by running the following:

  USE master
  EXEC sp_configure 'remote data archive', '1';
  GO
  RECONFIGURE;
  GO
  

• Enable stretch for the database. This is accomplished by performing the following steps:
  • Create a database master key, which will be used to secure credentials necessary to connect to the Azure SQL Database:

    USE <database>; 
    GO  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='<password>'; 
    GO
    

  • Create the credentials to connect to the Azure SQL Database:

    CREATE DATABASE SCOPED CREDENTIAL <credential_name>  
        WITH IDENTITY = '<identity>' , SECRET = '<secret>' ;
    GO   
    

  • Configure the database by enabling the remote data archive option. This requires providing the credentials you created in the previous step along with the name of the server hosting the Azure SQL Database to which cold data will be migrated.

    CREATE DATABASE SCOPED CREDENTIAL <credential_name>  
        WITH IDENTITY = '<identity>' , SECRET = '<secret>' ;
    GO
    ALTER DATABASE <database name>  
        SET REMOTE_DATA_ARCHIVE = ON  
            (  
                SERVER = '<server_name>' ,  
                CREDENTIAL = <credential_name> 
            ) ;  
    GO
    

• Select at least one table within the database to be included in the stretch. You can either migrate all of data contained with a table to Azure or create a filter function that limits the scope of migration to a subset of table rows. Likely candidates for stretch are large tables with a significant amount of historical data, such as history tables that accompany temporal tables introduced in SQL Server 2016. While you might have a good idea which tables to choose based on the knowledge of data usage patterns in your environment, you have the option of leveraging Stretch Database Advisor included in the SQL Server 2016 Upgrade Advisor (available from Microsoft Downloads), which will automatically identify the most optimal options for you. You can also use it to determine if any of them is a subject to the blocking issues we mentioned earlier. To enable stretch for an individual table, you can run the following:

  ALTER TABLE <table_name> 
  	SET ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE=OUTBOUND ));
  

The simplest way to implement these tasks relies on the Enable Database for Stretch Wizard. You can invoke it directly from the Object Explorer window within the SQL Server Management Studio by right-clicking on the database you intend to stretch and selecting Tasks->Stretch->Enable from the context sensitive menu. The wizard will automatically identify tables eligible for stretch and allow you to create a filter function to limit the scope of the data to be migrated. Next, it will prompt you to sign in to your Azure subscription and select the target Azure region. At that point, you will need to either provision a new server to host the Azure SQL database or designate an existing one. You will also be asked to provide a strong password to encrypt the database master key (DMK), which in turn is used to encrypt your SQL Server database credentials. In addition, the wizard will allow you to specify a range of IP addresses to be included in the server firewall rules, in order to permit connectivity from the on-premises SQL Server instance. Finally, on the summary page, you will find an estimated monthly cost resulting from the stretch.

Pricing of Stretch Database is determined by three factors – compute, storage, and egress data transfers (out of Azure). The first of them is expressed in so called Database Stretch Units (DSUs) and ranges from 100 DSUs to 6000 DSUs. DSUs offer relative measurement of computing resources assigned to the Azure SQL Database, which translates into the speed of query processing and migration of cold data. The storage cost is calculated based on the price per GB per month, with database size and its backups contributing towards the total storage amount. The amount of storage is also affected by the resiliency setting you choose for the Azure SQL Database. In particular, selecting geo-redundant replica will incur extra cost when compared with local redundancy.

See all articles by Marcin Policht