Stretching SQL Server 2016 Tables To The Azure Cloud

Introduction

Microsoft SQL Server 2016 is a hybrid cloud environment designed to allow data and services to enable users to perform advanced analytics within their databases and create business insight visualizations.

Stretch DB in SQL Server

One of the highly anticipated features new to Microsoft SQL Server 2016 is Stretch DB, a feature that migrates your historical data transparently and securely to the Microsoft Azure cloud.

Moreover, it can be configured to migrate a complete table to the cloud or to migrate only part of a table based on certain conditions. For example, one won’t migrate its complete transaction table, but migrating data which is older than five years makes sense!

Benefits of Stretch DB 

1. Ability to determine which rows to migrate to the cloud and which rows to keep On-Premise by using predicates.
2. Stretch DB ensures that no data is lost if a failure occurs during migration. - It also has retry logic to handle connection issues that may occur during migration.
3. Data Migration can be paused to troubleshoot problems on the local server or to maximize the available network bandwidth.
4. Existing Applications do not have to be changed. The change will be done seamlessly.

   Source: http://wikidba.net/

Is Stretch DB for you?

Stretch Database targets transactional databases with large amounts of historical data, typically stored in a small number of tables. These tables may contain more than a billion rows. Some cases where Stretch DB can be used are: 

1. Transaction data is stored for a long time.
2. Historical Data is queried occasionally.
3. The size of tables is exponentially increasing and needs more storage.

To identify databases and tables that can be “stretched,” Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor.

Stretch DB Advisor in SQL Server

Download and install Upgrade Advisor from here. This tool is not included in the SQL Server 2016 Release Candidate (RC0) installation media.

To verify if a database and table are eligible for Stretch DB, open upgrade advisor and click on “Run Stretch Database Advisor.”

Select the server where the Database resides.

Select the Database to be stretched.

Select the tables to stretch and view the results.

Configure and Use Stretch DB

How to enable Stretch DB at the server level?

Before a Database can be stretched, Stretch DB needs to be configured at the server level.

EXECsp_configure'remote data archive','1';  
GO  
RECONFIGURE;  
GO  

This operation requires sysadmin or serveradmin permissions.

Enable Stretch Database on a database?

The easiest way to get started is to use the wizard.

1. Go to Tasks, Stretch, then click Enable.

2. This will lead to a Stretch DB Intro Page. 

3. Then, select the tables that need to be stretched.

4. Sign in to your Microsoft Azure Account.

5. Once signed in, you may create a new server or connect to an existing SQL Server.

6. Create a Master Key.

7. The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.

8. The wizard will then create the server and perform the configurations to stretch the Database and the required tables.

Monitor the Data Migration

To monitor the Data Migration progress, at the database level, go to Tasks, Stretch, then click Monitor.

The previous operation would migrate all the data to the cloud.

Query & Verify the Execution Plan

After a few mins, when querying the table transactions, all the data will be fetched from the cloud and no predicate was specified when enabling stretch on the table.

 

Migrate only Specific rows

Bring the data from the cloud back to on-premise

To migrate only specific rows, start by first bringing all the data from the cloud to the local database.

To do so, click on Stretch, Disable, then click "Bring data back from Azure".

All the records would be migrated back to on-premise gradually.

Define the filtering function in SQL Server 

The inline table-valued function required for a Stretch Database filter function looks like the following example.  

CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datetime)  
RETURNS TABLE  
WITH SCHEMA BINDING  
AS  
RETURN SELECT 1 ASis_eligible  
WHERE @column1 <CONVERT(datetime,'4/1/2016', 101)  
GO 

The parameters for the function have to be identifiers for columns from the table. Schema binding is required to prevent columns that are used by the filter predicate from being dropped or altered.

If the function returns a non-empty result, the row is eligible to be migrated; otherwise - that is, if the function doesn't return any rows - the row is not eligible to be migrated.

Alter the table

The next step is to alter the table and set remote_archive on while specifying the predicate.

ALTER TABLE [dbo].[TRANSACTIONS]  
SET ( REMOTE_DATA_ARCHIVE =ON (  
FILTER_PREDICATE =dbo.fn_stretchpredicate(DATE),  
MIGRATION_STATE =OUTBOUND ));    

Monitor Migration

There are 67620 records that are dated before 4/1/2016 which should be migrated to the cloud. 

Query & Verify Execution Plan

i. Select data where Date < 4/1/2016, as expected, data will be fetched from the cloud.

ii. Select data where product if = 4139. This is a more interesting scenario, where both the On-Premise and Cloud data are queried and the result is merged and returned to the user.

Limitations of Stretch DB in SQL Server

The development of Stretch DB is still in progress and at the time of writing, there are lots of properties that are not yet supported by Stretch DB, for example, tables with more than 1,023 columns, and More than 998 indexes. The complete list of Stretch DB Limitations can be consulted here.

Conclusion

Stretch Database is an easy way to migrate archive data to Microsoft Azure if your database supports it. Currently, in SQL Server 2016, there are several limitations as described above. It would be great if those can be supported gradually for higher adoption of this feature. But this is a great initiative from Microsoft, powering the local databases with cloud capabilities is awesome!

Read more articles on SQL Server:

• Create User-Defined Data Types In SQL Server
• Using The Template Explorer Of SQL Server Management Studio