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