Getting Started with Stretch Database Functionality in SQL Server 2016 – Part 1

Introduction

SQL Server is one of the most widely used RDBMS around the world, and one of the facts of this wide adoption comes from Microsoft’s commitment to add new capabilities to cater to the need of the changing landscape year over year. Along the same line, SQL Server 2016 introduces several new features and one of them is Stretch Database, which lets you move or archive your cold data from the 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 the last article of this series, we were introduced to this new feature, learned ways to identifying potential tables for stretching and understood some of the key concepts to get started with this feature. In this article, I am going to demonstrate how this feature works and how to quickly get started with it.

Getting Started with StretchDB

SQL Server 2016 provides different ways to enable and get started with the StretchDB feature as mentioned below.

  • Using the wizard provided in SQL Server Management Studio (SSMS)
  • Using a T-SQL script

Using the Wizard Provided in SQL Server Management Studio (SSMS)

Before you start stretching your table(s) from your local on-premise database, you need to first enable this feature at the local SQL Server instance level. You can use the script below to enable or verify if this feature has been enabled or not.

  --Check if the StretchDB feature has been configure on the
  server already
  --Value 0 indicates it’s not, 1 indicates it has been 
  SELECT * FROM sys.configurations where
  name = 'remote data
  archive'
   
  --Enabling StretchDB feature on SQL Server instance
  EXEC sp_configure 'remote data archive';
  GO
  EXEC sp_configure 'remote data archive' ,
  '1';
  GO
  RECONFIGURE;
  GO

Now for demonstration, let me create a database with a table and load some data. With the script below, I create a database StretchDBDemo and a table SalesOrderDetailHistory and load data into it from the [Sales].[SalesOrderDetail] table of the [AdventureWorks2012] database. You can download [AdventureWorks2012] sample database from here.

Please note, I am creating a new database and a table here for the sake of simplicity and you don’t necessarily need to do it to start with. You can enable the StretchDB feature on an existing database and stretch an existing table to Azure SQL Database if it meets criteria to be stretched.

  CREATE DATABASE [StretchDBDemo]
  GO
  USE [StretchDBDemo]
  GO
  CREATE TABLE [SalesOrderDetailHistory](
         [SalesOrderID] [int] NOT NULL,
         [SalesOrderDetailHistoryID][int] NOT NULL,
         [CarrierTrackingNumber][nvarchar](25) NULL,
         [OrderQty] [smallint] NOT NULL,
         [ProductID] [int] NOT NULL,
         [SpecialOfferID] [int] NOT NULL,
         [UnitPrice] [money] NOT NULL,
         [UnitPriceDiscount] [money] NOT NULL,
         [LineTotal] [money],
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
         [ModifiedDate] [datetime] NOT NULL,
   CONSTRAINT [PK_SalesOrderDetailHistory_SalesOrderID_SalesOrderDetailHistoryID] 
PRIMARY KEY CLUSTERED 
(
         [SalesOrderID] ASC,
         [SalesOrderDetailHistoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [StretchDBDemo].[dbo].[SalesOrderDetailHistory]
SELECT * FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]
  GO

Right click on the database in the Object Explorer in SSMS, click on Tasks -> Stretch -> Enable as shown in the figure below:

Enabling Stretch Database feature for the database
Figure 1 – Enabling Stretch Database feature for the database

The first screen of the wizard introduces you to the wizard and next screen asks you to enable the StretchDB feature at the SQL Server instance level if it is not already enabled; if it is already enabled, then that screen is skipped. Likewise, the next screen asks you to specify details to create the database master key (if you have not created it already; if you have, then this screen will also be skipped). Database master key is needed to encrypt and secure credential and connection information needed to connect to Azure SQL Database.

Specifying master key for credential encryption
Figure 2 – Specifying master key for credential encryption

On the next screen of the wizard, you can select all of the tables (which meet criteria to be stretched and are not yet stretched) that you want to be stretched, as shown in the figure below. As I have just one table in the database, I can see only the table name listed here:

Selecting tables to stretch
Figure 3 – Selecting tables to stretch

On the next screen of the wizard, you need to connect to your Microsoft Azure Account and then select the appropriate Azure Subscription where you want Azure SQL Database to exist; click on the Sign-on button to connect to your Microsoft Azure Account.

Microsoft offers one month or $200 USD word credit (whichever comes early) free to try out Azure services when you sign off, you can find more details here.

Signing into Azure Account
Figure 4 – Signing into Azure Account

Once you are connected to your Microsoft Azure Account and have selected the appropriate subscription, select the datacenter region (closer to your on-premise location for faster data transfer) and then you have the choice to either create a new logical SQL Server in Azure or use an existing one, if it exists already.

If you check the Select Existing Server radio button, it will list all of the V12 logical SQL Servers available in the subscription in that selected region. As we are doing it for the first time, lets specify to create a new logical SQL Server (it creates version 12 of Azure SQL Database).

Selecting Azure Subscription, Region and Server
Figure 5 – Selecting Azure Subscription, Region and Server

On the next screen of the wizard, you need to specify details to create credentials by specifying server admin account. Also, you need to specify an IP or range of IPs that will be allowed to access the logical Azure SQL Server and its database.

This is one of the best security features of Azure SQL Database; by default it restricts everyone to get access to it and you, as an administrator, have control to choose who can access the Azure SQL Server and its databases.

Create Credential to Connect to Azure SQL Database
Figure 6 – Create Credential to Connect to Azure SQL Database

Once you are done, you will be presented a summary of all the configurations you have specified; click on Finish to start enabling the StretchDB feature and progress should look like what is shown in the figure below:

Execution result of enabling stretch for the database and tables
Figure 7 – Execution result of enabling stretch for the database and tables

Once the StretchDB feature is enabled for table(s), SQL Server transparently starts migrating data from the local table to the remote table and you can monitor the progress with the script below. As you can see in the figure below, SQL Server sends data in batches to reduce the load on the network:

SELECT object_name(table_id), * FROM sys.dm_db_rda_migration_status

Data migration monitoring
Figure 8 – Data migration monitoring

You can execute the queries below to check the row count in a local table as well as in a remote table during or after migration. As you can see in the figure, migration has completed and hence row count is 0 in the local table whereas the remote table has all the rows of the table.

  EXEC sp_spaceused 'SalesOrderDetailHistory', @mode = 'LOCAL_ONLY'
  EXEC sp_spaceused 'SalesOrderDetailHistory', @mode = 'REMOTE_ONLY'

Analyzing row count in local and remote tables
Figure 9 – Analyzing row count in local and remote tables

Even though the table’s data has been migrated to a remote Azure SQL Database, the end users or applications can continue using the table the way they had been (of course with no change in the way to access it). SQL Server internally creates the execution plan with remote query processing (Remote Query Operator) to pull data from the remote table transparently, as shown in the figure below.

Of course querying a remote table will have latency compared to querying a local table but a remote table is supposed to contain historical or cold data, which are rarely queried.

select top 100 * from SalesOrderDetailHistory

Analyzing Remote Query operation in use
Figure 10 – Analyzing Remote Query operation in use

Using T-SQL Script

So far, we looked at using the wizard provided in SQL Server Management Studio (SSMS) to enable the StretchDB feature but you can also use T-SQL scripting to do it as well, as shown in the script below:

--Enabling StretchDB feature on SQL Server instance
EXEC sp_configure 'remote data archive';
GO
EXEC sp_configure 'remote data archive', '1';
GO
RECONFIGURE;
GO
--Create master to encrypt and secure credential and connection information
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password to encrypt master key>'
 
--Create credential to connect to remote Azure SQL Database
CREATE DATABASE SCOPED CREDENTIAL [<Azure SQL Server Name>.database.windows.net]
WITH 
       IDENTITY = '<Server Admin Name>', -- sysadmin user name for the SQL Database server
       SECRET = '<Server Admin Password>' -- sysadmin password for the SQL Database server
GO
ALTER DATABASE [StretchDBDemo] 
SET
  REMOTE_DATA_ARCHIVE = ON 
(
       SERVER = N'<Azure SQL Server Name>.database.windows.net', 
       CREDENTIAL = [<Azure SQL Server Name>.database.windows.net]
)
GO
--Enable remote data archival for a table
ALTER TABLE [dbo].[SalesOrderDetailHistory]
       SET(REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = ON))
GO
  

Note – The feature mentioned and demonstrated in this article is based on SQL Server 2016 CTP 2.4 and might change when RTM is 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 maintenance overhead (and hence increases the overall performance) and the same time, stretched data is online allowing applications to access it like any other table.

In this article of this series, we learned how to get started with this new feature either by using SSMS or using T-SQL scripting. In the next article of this series, I am going to explain and demonstrate its impact on backup and restore, plus ways to pause and resume and disable this feature.

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