SQL Server 2016 – Playing Around Stretch database

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

Leave a Reply

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

*


x

Related Posts

Provisioning Azure Cosmos DB using Powershell
Azure Cosmos database is one of the NoSQL database that is available in Microsoft Azure cloud platform. Azure Cosmos db is getting more popular c...
Query Editor - Azure Portal
An interesting new tool (in preview) added to Azure SQL database. Now Azure portal provides flexibility to query azure sql database from a browse...
Clear Procedure Cache in SQL Azure database
When we were working on performance baseline or performance improvement we might be clearing down the procedure cache to get the exact statistics...
powered by RelatedPosts