SQL Server 2016 – Playing Around Stretch database

I started testing the stretch tables quite some time and thought of blogging the experience.

DML Operations

Only INSERT are allowed.
DELETE\UPDATE operations are not allowed (as of CTP 3.0). If you try to do you will ended with the below error

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

DDL operations are not allowed (as of CTP 3.0). If try you will end up with the below error

Msg 14825, Level 16, State 1, Line 1
Cannot perform 'ALTER TABLE' on table 'Trip' because REMOTE_DATA_ARCHIVE is enabled.
Backup and Restore Operations
  • There is no special backup commands for the db when you enable stretch. You can use the same backup strategy.
  • There are few additional steps to be followed when you try to restore the database.
Restore the database
  • Create the Master database key (if it doesnt exist)

-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyMasterKeyPassword';

  • Create the database scoped credential.

-- Create a database scoped credential.
USE DATABASE
GO
CREATE DATABASE SCOPED CREDENTIAL credential_name
WITH IDENTITY = 'MyIdentity'
[ , SECRET = 'MyMasterKeyPassword' ]
GO

  • Authorize the db to connect to Azure to migrate the data

EXEC sp_rda_reauthorize_db @azure_credentialname

Useful scripts

Some of the scripts which I thought will be useful.

  • Find the tables involved in remote data archieve

SELECT [name]
, [remote_data_archive_migration_state_desc]
FROM [sys].[tables]
WHERE [is_remote_data_archive_enabled] = 1;

  • Get last sync data

SELECT DB_NAME([rda].[database_id]) [DatabaseName]
, [st].[name] [TableName]
, [rda].[migrated_rows]
, MAX([rda].[end_time_utc]) [LastDataMigrationTime]
FROM [sys].[dm_db_rda_migration_status] [rda]
INNER JOIN [sys].[tables] [st] ON [st].object_id = [rda].[table_id]
WHERE [migrated_rows] <> 0
GROUP BY [st].[name]
, [rda].[database_id]
, [rda].[migrated_rows];

  • Get details about external source and azure sql server where stretch tables stored

SELECT [rdad].[remote_database_name] [RemoteDatabase]
, [ed].[name] [ExternalSourceName]
, [ed].[location] [AzureSQLServer]
FROM [sys].[external_data_sources] [ed]
INNER JOIN [sys].[remote_data_archive_databases] [rdad] ON [rdad].[data_source_id] = [ed].[data_source_id]
AND [ed].[type] = 3;

  • Space Used

/*****Space Details*****/
EXEC [sp_spaceused]
'Trip',
@mode = 'ALL'; --Returns the total space used in inhouse and cloud servers
GO
EXEC [sp_spaceused]
'Trip',
@mode = 'LOCAL_ONLY'; --Returns the total space used in inhouse server only
GO
EXEC [sp_spaceused]
'Trip',
@mode = 'REMOTE_ONLY';--Returns the total space used in cloud server only
GO
EXEC [sys].[sp_spaceused_remote_data_archive]; --Returns the total space used in cloud for all stretched tables
GO
EXEC [sp_spaceused]
@mode = 'ALL'; --Returns the total local database size , data size includes azure data size as well
GO
EXEC [sp_spaceused]
@mode = 'LOCAL_ONLY'; --Returns the total local database size however cloud storage size is excluded
GO
EXEC [sp_spaceused]
@mode = 'REMOTE_ONLY';--Returns the total cloud database size however inhouse storage size is excluded
GO
/*****Space Details*****/

Leave a Reply

Your email address will not be published. Required fields are marked *

*