SQL Server 2016 Cloud Integration Enhancements

Abstract

In earlier version of SQL Server (2014), the Microsoft Azure platform was meant for keeping the database backups as this platform is cost-effective, secure, and inherently offsite for keeping the backups. Later, it has been enhanced to provide disaster recovery by making cloud backup as asynchronous replica which will reduce CAPEX and OPEX cost of physically maintaining additional hardware in additional data centers.

In the latest version of SQL Server (2016), the Microsoft brought an ability to extend your on premise SQL database to Azure and perform queries seamlessly. In this article, an attempt has been made to explain this new feature of SQL Server.

Business Context

We know that the history data is critical for any organization to enable reporting and comply with the regulatory requirements. However, the retention of huge amount of history data in the primary database storage area will impact the performance of on-line transaction processing and increase the cost of storage. In order to overcome these limitations, Microsoft has introduced an option to expand your SQL Server database to the cloud. This option is called as “Stretch Database” that allows extension of SQL database from local SQL Server instance to Azure SQL database and the users will be able to access the data on Azure SQL database seamlessly.

Introduction

This “Stretch Database” feature lets you dynamically move portion of warm and cold transactional table data to Microsoft Azure. This feature is more beneficial to keep the on-line transactional data for longer periods of time on the On-premise storage and move the history data to cloud storage to save money on storage and improve on-line transaction processing performance. The history data is migrated to Azure seamlessly and the applications will be able to query the data without requiring any changes to application code.

This is required to enable “Stretch database option” at database level and identify the tables that are required to move to Azure so that the migration happens silently. In order to identify the candidate tables for migration, run a tool called “Stretch Database Advisor. The query optimizer is fully aware of the rows are on the server vs. the rows on the Azure, and divides the workload accordingly. Since the Azure takes care of the query processing for the data on Azure, the latency is attributed only to the network to return the data. Also, the user is charged whenever the data on Azure is queried, along with the Azure Blob storage. No additional software licensing costs are levied.

Prerequisites

The following are the prerequisites to leverage the Stretch Database feature.

·         Azure SQL database uses firewall rules to allow connections to the local SQL Server and databases. Hence, server-level and database-level firewall settings are to be defined for the master and the user databases in the Azure SQL database server to selectively allow access to the database. Microsoft recommends using database-level firewall rules whenever possible to make the user database more portable. The server-level firewall rules may be used when there are many databases exists with same access requirements.

·         Setting up of Azure account and subscription for billing

·         Enabling Stretch Database for a SQL Server instance - Change the "remote data archive" instance-level configuration option using sp_configure, as mentioned below. With this option enabled, one can configure databases for Stretch Database, migrate data, and query data on the remote endpoint.

--To verify the current setting for the instance

EXEC sp_configure 'remote data archive';

GO

--To set the option to ON at instance level

EXEC sp_configure 'remote data archive' , '1';

GO

RECONFIGURE;

GO

Note: This option is neither a switch that turns the Stretch Database feature on or off nor a permission setting for configuring Stretch Database.

·         Availability of Stretch Database Advisor tool which is part of SQL Server 2016 Upgrade Advisor. SQL Server 2016 Upgrade Advisor is a standalone tool that enables users of prior versions to run a set of upgrade rules against their SQL Server database to pinpoint breaking and behavior changes and deprecated features as well as providing help with the adoption of new features such as Stretch Database. This tool does not come along with SQL Server 2016 CTP2. It is to be downloaded from here.

Implementation

SSMS Wizard:

T-SQL:

CREATE CREDENTIAL WITH IDENTITY = , SECRET =

ALTER DATABASE

SET REMOTE_DATA_ARCHIVE = ON (SERVER = );

GO;

ALTER TABLE dbo.

ENABLE REMOTE_DATA_ARCHIVE WITH (MIGRATION_STATE = ON);

GO

Verification & Validation of Stretch Database setup

·         To know all the databases and tables that are stretch enabled on a particular instance, check the value of column is_remote_data_archive_enabled in sys.databases and sys.tables system tables (0 – not enabled, 1 – enabled, same column in both the tables)

·         To verify the status of data migration to Azure, use the sys.dm_db_rda_migration_status DMV.

·         To know all the remote databases and tables that are used by Stretch Database – use the catalog views sys.remote_data_archive_databases and sys.remote_data_archive_tables

Data Access

Stretch Database does not change the permission model for an existing database. The user logins will be able to access the Azure SQL database through the local instance database only. User logins cannot issue queries directly to the remote endpoint (Azure) through the linked server definition.

Data Backup

As of CTP2 release, SQL Server 2016 supports only shallow backup for a Stretch-enabled database. It means it does not include the data residing on the remote server. The shallow backup contains only the local data, along with the remote endpoint location where the data resides on Azure SQL database. The ability to take deep backup that includes local and remote data, is expected to be available with SQL Server 2016 RTM version.

Data Restore

EXEC sys.sp_reauthorize_remote_data_archive @azure_username, @azure_password;

GO;

Disable Stretch Database option for a Table

·         In order to disable stretch database option for a table, pause data migration on the table as a first step. This can be done either by using SSMS (select the table, right-click Go to Tasks à Stretch à Pause) or by using T-SQL, as shown below.

ALTER TABLE