On November 30th, I presented the Stretch Database feature in “Les Journées SQL Server 2015” in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0
I decided to share you my demonstration into a blog.

 

I – Enabling the feature at the instance level

First, you need to enable the “Remote Data Archive” option at the instance level.
To check if the options is enabled:

sp_configure 'REMOTE DATA ARCHIVE';
GO

 

As it returns a “run-value” set to “0”, I need to enable the option as follows:

sp_configure 'remote data archive', '1';
RECONFIGURE
GO

 

II – Enabling the feature at the database level

I create a database named “Stretch_DB” for this demonstration:

By default, the feature is obviously disabled for my database:

SELECT is_remote_data_archive_enabled FROM sys.databases WHERE name = 'Stretch_DB';

During the activation process of the feature, I need to link my local database to Azure. So I have to create a SQL Database server. I will not detail this creation in this blog because you can find several sources on the web describing how to do (cf. Getting Started with Azure SQL Database v12).

Do not forget to configure the remote firewall to accept the connection of the local SQL Server instance.

To access to the remote SQL Database server, I also need to use the Server Admin Login (provided during the SQL Database server creation step).

 

So I create the credential on my local instance:

USE Stretch_DB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pa$$w0rd';
CREATE DATABASE SCOPED CREDENTIAL StretchCred WITH IDENTITY = 'myLogin', SECRET = 'myPa$$w0rd';
GO

And I link my local database to my remote server:

ALTER DATABASE STRETCH_DB
    SET REMOTE_DATA_ARCHIVE = ON ( SERVER = 'JSS.DATABASE.WINDOWS.NET' , CREDENTIAL = STRETCHCRED ) ;
GO

 

I can check that my feature is enabled for my database:

SELECT IS_REMOTE_DATA_ARCHIVE_ENABLED FROM SYS.DATABASES
    WHERE NAME = 'STRETCH_DB';
GO

 

Also, I can see my remote database created in Azure:

SELECT * FROM SYS.REMOTE_DATA_ARCHIVE_DATABASES

 

III – Enabling the feature at the table level

I create a table named “ERRLOG” in “dbo” schema.

Then I enable the migration for this table:

ALTER TABLE DBO.ERRORLOG
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = OUTBOUND )  ) ;
GO

 

I can check that my feature is enabled for my table:

SELECT IS_REMOTE_DATA_ARCHIVE_ENABLED  FROM SYS.TABLES
WHERE NAME = 'ERRORLOG'

 

Also, I can see the remote table created in Azure:

SELECT * FROM SYS.REMOTE_DATA_ARCHIVE_TABLES

 

I can pause the migration process for my table. Useful if I need to troubleshoot or monitor my feature.

ALTER TABLE dbo.ErrorLog
	SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = PAUSED )  ) ;
GO

 

IV – Monitoring the Stretch Database feature

To check the migration state of the table:

SELECT REMOTE_DATA_ARCHIVE_MIGRATION_STATE_DESC FROM SYS.TABLES
WHERE NAME = 'ERRORLOG'

 

To list the migration batch of the table:

SELECT * FROM SYS.DM_DB_RDA_MIGRATION_STATUS ORDER BY START_TIME_UTC DESC;
GO

 

To obtain a data consumption of the table (locally and remotely):

SP_SPACEUSED 'ERRORLOG', 'TRUE', 'REMOTE_ONLY'
GO
SP_SPACEUSED 'ERRORLOG', 'TRUE', 'LOCAL_ONLY'
GO

 

As soon as the Stretch Database feature is enabled, an Extended Events session is created to monitor the Stretch Database ecosystem.
To see these events, I made a script:

SELECT CAST(EVENT_DATA AS XML) AS EVENTDATA
INTO #CAPTURE_EVENT_DATA
FROM SYS.FN_XE_FILE_TARGET_READ_FILE(‘C:MSSQL13.MSSQLSERVERMSSQLLOGSTRETCHDATABASE_HEALTH*.XEL’, ‘C:MSSQL13.MSSQLSERVERMSSQLLOGMETAFILE.XEM’, NULL, NULL);

SELECT CAST(EVENT_DATA AS XML) AS EVENTDATA
INTO #CAPTURE_EVENT_DATA
FROM SYS.FN_XE_FILE_TARGET_READ_FILE('C:MSSQL13.MSSQLSERVERMSSQLLOGSTRETCHDATABASE_HEALTH*.XEL', 'C:MSSQL13.MSSQLSERVERMSSQLLOGMETAFILE.XEM', NULL, NULL);

SELECT 
	EVENT_DATA.VALUE('(./@NAME)', 'NVARCHAR(50)') AS EVENT_NAME,
	EVENT_DATA.VALUE('(./@TIMESTAMP)', 'DATETIME') AS EVENT_DATE,
	EVENT_DATA.VALUE('(DATA[@NAME="DATABASE_ID"]/VALUE)[1]', 'INT') AS EVENT_DB_ID,
	EVENT_DATA.VALUE('(DATA[@NAME="TABLE_ID"]/VALUE)[1]', 'BIGINT') AS EVENT_TABLE_ID,
	EVENT_DATA.VALUE('(DATA[@NAME="IS_SUCCESS"]/VALUE)[1]', 'NVARCHAR(5)') AS EVENT_SUCCESS,
	EVENT_DATA.VALUE('(DATA[@NAME="DURATION_MS"]/VALUE)[1]', 'INT') AS EVENT_DURATION,
	EVENT_DATA.VALUE('(DATA[@NAME="ROWS"]/VALUE)[1]', 'BIGINT') AS EVENT_ROWS,
	EVENT_DATA.VALUE('(DATA[@NAME="ERROR_NUMBER"]/VALUE)[1]', 'BIGINT') AS EVENT_ERROR_NUMBER,DIRE
	EVENT_DATA.VALUE('(DATA[@NAME="SEVERITY"]/VALUE)[1]', 'INT') AS EVENT_SEVERITY,
	EVENT_DATA.VALUE('(DATA[@NAME="MESSAGE"]/VALUE)[1]', 'NVARCHAR(1000)') AS EVENT_MESSAGE
FROM #CAPTURE_EVENT_DATA
	CROSS APPLY EVENTDATA.NODES('//EVENT') XED (EVENT_DATA)

 

The Stretch Database feature is an interesting feature from SQL Server 2016. If you want more information, you should take a look to our other blogs:

SQL Server 2016 CTP2: Stretch database feature – Part 1
SQL Server 2016 CTP2: Stretch database feature – Part 2
SQL Server 2016 CTP3.0: Stretch Database enhancements