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