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

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 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 overhead in maintaining them (and hence overall increasing the performance) and the same time, stretched data is online allowing applications to access it like any other table.

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

Pausing and Resuming Remote Data Archival for a Table

If necessary, you can pause the data migration to troubleshoot any issues (or to minimize the load on network bandwidth) and resume it once you are done. To pause or resume data migration you can either use the wizard in SSMS (to pause it, right-click on the table in Object Explorer of SSMS and select Tasks, and then select Stretch, and then select Pause; likewise, to resume it, right-click on the table in Object Explorer of SSMS and select Tasks, and then select Stretch, and then select Resume.) or use the T-SQL command as given below:

--Pause remote data archival
ALTER TABLE [dbo].[SalesOrderDetailHistory]
       SET(REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OFF))
GO
--Resume remote data archival
ALTER TABLE [dbo].[SalesOrderDetailHistory]
       SET(REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = ON))
GO

SQL Server 2016 CTP 2 does not allow updating and deleting rows from a table enabled for remote data archival although you can insert data. This also applies when you have paused remote data archival. If you try doing DELETE or UPDATE you will get an error similar to that shown below:

delete from SalesOrderDetailHistory where SalesOrderID = 68536

Msg 14826, Level 16, State 1, Line 80
Update and delete of rows eligible for migration in table 'SalesOrderDetailHistory'
  is not allowed because of the use of REMOTE_DATA_ARCHIVE.

Disable StretchDB Feature for a Database

If you want to temporarily stop data migration and re-start later, you can follow the approach as discussed for pausing and resuming but if you want to disable this feature completely then you need to perform these two activities:

  • First, you need to pause data migration for all the tables that have been enabled for remote data archival (referred as stretch-enabled table) in the database. Next, for each of these tables you need to create a local table with the same schema as the stretch-enabled table, then copy the data from the stretch-enabled table to the newly created local table (you can use the INSERT INTO … SELECT FROM command). Finally drop the stretch-enabled table and rename the newly created table with the name of the stretch-enabled table you dropped.
  • Second, now you can disable the StretchDB feature for the database when it no longer contains any stretch-enabled table with the command given below.

As you notice with the below script, when you try disabling the StretchDB feature on a database that contains even a single table with remote data archival enabled, it fails and hence you need to first turn them off before disabling the StretchDB feature on the database.

--Disable StretchDB feature for a database
ALTER DATABASE [StretchDBDemo]
      SET REMOTE_DATA_ARCHIVE = OFF;

Msg   14802, Level 16, State 1, Line 70
Cannot   disable REMOTE_DATA_ARCHIVE because the database contains at least one table   having REMOTE_DATA_ARCHIVE enabled.
Msg   5069, Level 16, State 1, Line 70
ALTER   DATABASE statement failed.

Backup and Restore for Stretch-enabled Database

Backup and restore for a database, enabled for the StretchDB feature, works slightly differently. When you take a backup of a stretch enabled database, it includes only local data and eligible data (data not yet migrated and still available in the local stretch-enabled table) including information about the remote endpoint where the database’s remote data resides, at the point in time when you run your backup command. The backup copy of stretch-enabled database is called “shallow backup”. Currently, SQL Server 2016 Community Technology Preview 2 (CTP2) does not support taking a “deep backup” that contains all data in the database, from both local as well as from remote.

Though the process to take a backup of a database remains the same, restoring it back is slightly different for a stretch-enabled database. After the database is restored, your database contains only local data and eligible data (data not yet migrated and still available in the local stretch enabled table) from the backup but migration will not start automatically as it might not have the required credentials to connect to the remote endpoint. Hence you need to first create credentials on your restored database (script provided below to create credential if it does not exists) and re-establish the connection between the local database and its remote endpoint by calling sys.sp_reauthorize_remote_data_archive as demonstrated in the script.

----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
--Switch to restored database
USE <Restored Database>
--Restore the authenticated connection between a StretchDB   enabled locally 
--restored database and Azure SQL database
EXEC   sp_reauthorize_remote_data_archive @azure_credentialname   = N'<Azure SQL   Server Name>.database.windows.net'
GO

Copying   remote database 'RDAStretchDBDemoAD8E496D-8CFE-4A8F-A90C-9DA4C5682ADE' to   
remote database 'RDAStretchDBDemo156C5CB16-0488-4865-B840-CF3720E68950'.
Waiting   for remote database copy to complete.
Remote   database 'RDAStretchDBDemo156C5CB16-0488-4865-B840-CF3720E68950' has   
completed copying, and is now online.

As you can see in the output message for the above script, once the connection is re-established, stretch-enabled database creates a copy of the remote database on the same server and links it with the local database. It then attempts to reconcile eligible data in the local database with remote data without any manual intervention. Once after the reconciliation completes, your data will be in a consistent state and at that time you can safely delete the previous copy of the remote database.

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 that 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 overhead in maintaining them (and hence overall increases the performance) and at the same time, stretched data is online allowing applications to access it like any other table.

In this article of this series, I explained and demonstrated the impact of enabling the StretchDB feature on backup and restore, ways to pause and resume, and disable this feature altogether.

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