Skip to main content

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. 

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 condition. For example, one won’t migrate its complete transaction table, but migrating the 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 does not have to be changed. The change will be done seamlessly.

    sql
                                  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 are 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

Download and install Upgrade Advisor from here. This tool is not included on the SQL Server 2016 Release Candidate (RC0) installation media.
To verify if a database and table is eligible for Stretch DB, open upgrade advisor and click on “Run Stretch Database Advisor.”

advisor
  1. Select the server where the Database resides.

    configure
  2. Select the Database to be stretched.

    database
  3. Select the tables to stretch and view the results.

    result

    result
Configure and Use Stretch DB

Enable Stretch DB at server level

Before a Database can be stretched, Stretch DB needs to be configured at the server level.
  1. EXECsp_configure'remote data archive','1';  
  2. GO  
  3. RECONFIGURE;  
  4. 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 TasksStretch, then click Enable.

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

    page
  3. Then, select the tables that needs to be stretched.

    stretch
  4. Sign in to your Microsoft Azure Account.

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

    configure
  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.

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

    progress
Monitor the Data Migration

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

monitor

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

server

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.

table

 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".

azure

recover

message

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

stretch
server

Define the filtering function

The inline table-valued function required for a Stretch Database filter function looks like the following example. 
  1. CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datetime)  
  2. RETURNS TABLE  
  3. WITH SCHEMA BINDING  
  4. AS  
  5. RETURN SELECT 1 ASis_eligible  
  6. WHERE @column1 <CONVERT(datetime,'4/1/2016', 101)  
  7. 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.
  1. ALTER TABLE [dbo].[TRANSACTIONS]  
  2. SET ( REMOTE_DATA_ARCHIVE =ON (  
  3. FILTER_PREDICATE =dbo.fn_stretchpredicate(DATE),  
  4. MIGRATION_STATE =OUTBOUND ));    
Monitor Migration

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

code


configure

Query & Verify Execution Plan

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

code

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

live query
Limitations of Stretch DB

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 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 a higher adoption of this feature. But this is a great initiative from Microsoft, powering the local databases with cloud capabilities is awesome!

References 

Comments

Popular posts from this blog

Creating and Querying Microsoft Azure DocumentDB

DocumentDB is the latest storage option added to Microsoft Azure.
It is a no-sql storage service that stores JSON documents natively and provides indexing capabilities along with other interesting features.

This article is available available on theMicrosoft Technet Wiki. This article was highlighted in theTop Contributor awardson the 12th of October 2014. This article was highlighted in the TNWiki Article Spotlight. This article was highlighted in the The Microsoft TechNet Guru Awards! (October 2014).


DocumentDB is the latest storage option added to Microsoft Azure.
It is a no-sql storage service that stores JSON documents natively and provides indexing capabilities along with other interesting features.
This wiki shall introduce you to this new service.

Setting up a Microsoft Azure DocumentDBGo to the new Microsoft Azure Portal. https://portal.azure.com/ 


 Click on New > DocumentDB


Enter A Database ID and hit Create!



Query Unstructured Data From SQL Server Using PolyBase

Scope The following article demonstrates how unstructured data and relational data can be queried, joined and processed in a single query using PolyBase, a new feature in SQL Server 2016. Pre-RequisitesIntroduction to Big Data Analytics Using Microsoft Azure Big Data Analytics Using Hive on Microsoft Azure Analyze Twitter Data With Hive in Azure HDInsight Running Hadoop on Linux using Azure HDInsight  Introduction Traditionally, Big Data is processed using Apache Hadoop which is totally fine. But what if the result of this needs to be linked to the traditional Relation Database? For example, assume that from the analysis of tons of application logs, marketing needs to contact some customs that faced problems in an application following a failure in the application.
This problem is solved with PolyBase. PolyBase allows you to use Transact-SQL (T-SQL) statements to access data stored in Hadoop or Azure Blob Storage and query it in an ad-hoc fashion. It also lets you query semi-structure…

Creating and Deploying Microsoft Azure WebJobs

Azure WebJobs enables you to run programs or scripts in your website as background processes. It runs and scales as part of Azure Web Sites.
This article focuses on the basics of WebJobs before demonstrating an example where it can be used.

This article is also available on the Mirosoft TechNet Wiki.
This article was highlighted in the The Microsoft TechNet Guru Awards! (October 2014).


Introduction
What is Microsoft Azure WebJobs?
Azure WebJobs enables you to run programs or scripts in your website as background processes. It runs and scales as part of Azure Web Sites.

What Scheduling Options is supported by Microsoft Azure WebJobs? Azure WebJobs can run Continuously, On Demand or on a Schedule.
In what language/scripts are WebJobs written?
Azure WebJobs can be created using the following scripts:  .cmd, .bat, .exe (using windows cmd).ps1 (using powershell).sh (using bash).php (using php).py (using python).js (using node)In this article, the use of c# command line app shall be demonstrated.
Cr…