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.
