SQL Server StretchDB

The estimated reading time for this post is 5 minutes

StretchDB is a new feature of SQL Server 2016 to archive/migrate cold data from on-premise to Azure SQL Database. We as consultant put the following values on the StretchDB feature:-

  • The cost of keeping cold data is low on Azure compare to On-Premise environment.
  • Using this feature does not require any changes on TSQL queries or Application architecture.
  • Reduces on-premise storage system’s maintenance and operation costs.
  • Keeps your data secure in Azure cloud environment.

SQL Server start migrating the cold data once StretchDB feature been enabled on Server, Database and Specific Table. SQL Server transparently migrate data to Azure and retrieve data from Azure, It also has a TRYAGAIN logic to make sure no data is lost during migration process. As DBA, you are able manually Pause or Resume data migration or even Abandon the cold data on Azure. The best candidate tables are those keeping historical data row such as Sales data for each year. Some DBAs to make the queries run faster, usually archive huge tables such as Sales table into other small tables differentiated by year. Highly transactional databases with huge amount of cold data, are most suitable candidate for StretchDB feature.

Prior using this feature, you need to be aware of its limitations. The following is StretchDB limitations:-

  • Data uniqueness is not enforced by Azure SQL Stretch Database.
  • Update and Delete is not possible on migrated data rows.
  • Insert is not possible on Stretch-Enabled table through Linked Server.
  • Index is not allowed to be created on a view using stretch-enabled table.
  • Filters on indexes are not migrating on remote table.
  • Candidate table should have less than 1023 columns and 998 indexes.
  • Candidate table should not be Filetable or tables contains FILESTREAM data.
  • Candidate table should not be Replicated or using either CT or CDC.
  • Candidate table should not be memory optimized table.
  • Candidate table should not have the following data types: ntext, text, image, timestamp, sql_variant, XML and CLR (System & User Defined).
  • Candidate table should not have Column_set or Computed Columns.
  • Candidate table should not have Default constraint, Check Constraint and Foreign Key.
  • Candidate table should not have XML, Spatial and Full Text indexes.
  • Candidate table should not been referenced in Indexed View.

To enable StretchDB feature, you are required to enable it on SQL Server instance level prior enabling database and tables. Enabling StretchDB requires SYSADMIN and DATABASE CONTROL permissions on instance level, DB_OWNER on database level.

To enable it on instance level, you need to turn on ‘Remote Data Archive’ option in SP_CONFIGURE.

EXEC SP_CONFIGURE 'remote data archive' , '1';  
GO
RECONFIGURE WITH OVERRIDE;  
GO

Enabling a database to use StretchDB is not so straight forward, you need to following the following steps in order:-

Create Database Master key (DMK), DMK secures the credential data that database will use to connect to remote database.

USE AdventureWorks; 
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD='Pa$$w0rd'; 
GO

Then you need to provide connectivity credentials in order on-premise database to be able to connect to remote database.

CREATE DATABASE SCOPED CREDENTIAL [AdventureWorks_Cred] 
WITH IDENTITY = 'sqladmin' , SECRET = 'Pa$$w0rd';

The final step to enable database with StretchDB feature is to alter database by the following command:-

ALTER DATABASE AdventureWorks
SET REMOTE_DATA_ARCHIVE = ON  
(  
SERVER = 'strechdbserver.database.windows.net' ,  
CREDENTIAL = AdventureWorks_Cred  
) ;

You might receive the following error if the Azure firewall is not configured.

/*Msg 40615, Level 14, State 1, Line 17
Cannot open server 'strechdbserver' requested by the login. Client with IP address '115.133.213.111' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect.
Msg 14817, Level 16, State 1, Line 17
The server 'strechdbserver.database.windows.net' is not accessible. Ensure that the remote server exists and the Azure SQL DB Firewall Rules permit access to the server. If you believe that your server should be accessible please retry the command.
Msg 5069, Level 16, State 1, Line 17
ALTER DATABASE statement failed.*/

You can enable StretchDB feature on existing table or new table. If you do not provide FILTER_PREDICATE function, then SQL Server migrate entire table into the remote table. The following script, creates a inline table value function to filter out cold data from Sales.SalesOrderDetail table from AdventureWorks database.

CREATE FUNCTION FN_GETCOLDDATA(@DATE DATETIME)
RETURNS TABLE
WITH SCHEMABINDING
AS 
	RETURN (SELECT 1 AS is_eligible WHERE @DATE < CONVERT(DATETIME,'01/03/2008',101))
 
GO
 
ALTER TABLE Production.TransactionHistory
SET ( REMOTE_DATA_ARCHIVE = ON ( FILTER_PREDICATE = dbo.FN_GETCOLDDATA(TransactionDate), MIGRATION_STATE = OUTBOUND));

once a table enabled as stretch table, then it is time to monitor the migration process by using sys.dm_db_rda_migration_status dynamic management view. the following result set shows that our TransactionHistory table is in mid of data migration to Azure SQL Stretch Database.

You may PAUSE or RESUME data migration by the following alter command:-

ALTER TABLE Production.TransactionHistory
SET ( REMOTE_DATA_ARCHIVE (MIGRATION_STATE = PAUSED));
 
GO
 
SELECT * FROM SYS.remote_data_archive_tables
 
GO
 
ALTER TABLE Production.TransactionHistory
SET ( REMOTE_DATA_ARCHIVE (MIGRATION_STATE = OUTBOUND));
 
GO
 
SELECT * FROM SYS.remote_data_archive_tables

Storage usage is one of DBAs concern that needs to be answered, SQL Server provides SP_SPACEUSED stored procedure to return storage spaced used by a table on LOCAL or REMOTE. The following script returns the TransactionHistory table storage size on Local and Remote.

EXEC SP_SPACEUSED 'Production.TransactionHistory','false','LOCAL_ONLY';
EXEC SP_SPACEUSED 'Production.TransactionHistory','false','REMOTE_ONLY';

You may need to delete migrated data from on-premise database, SQL Server provides such functionality by using sys.sp_rda_reconcil_batch stored procedure.

To disable Stretch table and copy the remote data into the local database table, you are required to run the following script:-

ALTER TABLE Production.TransactionHistory
SET (REMOTE_DATA_ARCHIVE (MIGRATION_STATE = INBOUND));

The following script disables the stretch table and abandon the remote data:-

ALTER TABLE Production.TransactionHistory
SET (REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY (MIGRATION_STATE = PAUSED));

Disabling Stretch Database for a table or for a database does not delete the remote object. Instead, you have to drop it by using the Azure management portal. The remote objects continue to incur Azure costs until you delete them manually.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn