SQL with Manoj

A Stretch Database lets you archive your historical data transparently and securely in the Azure SQL Database (i.e. Microsoft PaaS Cloud).

The idea is to have an On-Prem database configured to allow extension of one or more of its tables to a SQL Azure Database.

This extension to Azure can archive transparently “cold” data without change access to information requirements when the table is queried, all this while enjoying the very attractive storage cost proposed by Microsoft Azure.

–> We will see here how we can configure a Database for Stretch and enable it in a table in simple steps.

Step #1. Configure SQL Server instance to enable “Remote Data Archive”

USE [master]
GO

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

Step #2. Enable Database for Stretch to Azure

2.a. Enable a Database for Stretch from SSMS:

2.b. Sign-in to Microsoft Azure with your subscription ID:

2.c. Provide your preference for Azure Datacenter and login credentials:

2.d. Final page showing successful completion of enabling StretchDB:

2.e. Now go to the Azure portal and check the new SQL DB created by the Enable process:

2.f. Check the SQL Instance settings and DB Server name to connect it from SSMS:

Now we have a new Azure SQL Database created that is paired up with our On-prem SQL Server instance Database. We can not enable individual tables that we want to stretch to Azure.
 

Step #3. Now Create a table and insert some sample data in batches

USE [StretchDB]
GO

CREATE TABLE dbo.EmployeeStretch (
	EmployeeID	INT IDENTITY(1,1),
	EmployeeName VARCHAR(1000)
)

-- Batch 1
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT 'Manoj P'
UNION ALL
SELECT 'Saurabh S'
UNION ALL
SELECT 'Keshav K'
UNION ALL
SELECT 'Vivek S'
UNION ALL
SELECT 'Ganesh S'

select * from dbo.EmployeeStretch

Step #4. Enable Table for Stretch

With SSMS only you can enable a table for Stretch with 1-2 simple clicks, check the image below:

So, as soon as you enable this table for stretch the process creates an mutually equivalent table in Azure SQL DB with similar name which can be seen immediately via SSMS, check below:

On Querying this table the Stretch process adds an extra column with name “batchid–[object_id]” suffixed with the table ID belonging to On-prem SQL DB, check below:

Step #5. Load some more data in batches

-- Batch 2
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT 'Kaushik S'
UNION ALL
SELECT 'Deepak B'

select * from dbo.EmployeeStretch

-- Batch 3
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT 'Nitya S'
UNION ALL
SELECT 'Hema S'

select * from dbo.EmployeeStretch

-- Batch 4
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT TOP 100 CONCAT([FirstName], ' ', [LastName]) FROM [AdventureWorks2014].[Person].[Person]

select * from dbo.EmployeeStretch

As we’ve done INSERT in separate 4 Batches, thus you can see Batch IDs assigned to records from 1-4 under the batchid column of the Stretch table on Azure SQL DB, below:

 

–> Check the stats, i.e. space used by the table in on-prem and Azure DB:

sp_spaceused @objname = 'dbo.EmployeeStretch'
--Output: 
--name	               rows  reserved  data   index_size  unused
--dbo.EmployeeStretch  109   48 KB     16 KB  32 KB	  0 KB

sp_spaceused @objname = 'dbo.EmployeeStretch', @mode = N'LOCAL_ONLY'
--Output: 
--name	               rows  reserved  data   index_size  unused
--dbo.EmployeeStretch  0     16 KB     8 KB   8 KB        0 KB

sp_spaceused @objname = 'dbo.EmployeeStretch', @mode = N'REMOTE_ONLY'
--Output: 
--name	               rows  reserved  data   index_size  unused
--dbo.EmployeeStretch  109   32 KB     8 KB   24 KB       0 KB

The above stats clearly shows that the space used by Stretch enabled table in On-premise and On Azure SQL DB. As the data is moved behind the scenes from On-Prem to Azure SQL DB the space used by the table in On-prem is negligible i.e. some minimal default space.

–> Let’s load some more data as a 5th batch

-- Batch 5
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT TOP 1000 CONCAT([FirstName], ' ', [LastName]) FROM [AdventureWorks2014].[Person].[Person]

select * from dbo.EmployeeStretch

–> Now again check the space used:

sp_spaceused @objname = 'dbo.EmployeeStretch', @mode = N'REMOTE_ONLY'
--Output: 
--name	               rows  reserved  data   index_size  unused
--dbo.EmployeeStretch  1509  288 KB    80 KB  80 KB       128 KB

This again shows that whatever data you load on an On-Prem Stretch-enabled table it moves all data to Azure SQL DB without taking any space in On-prem DB, but on Azure SQL DB.
 

–> Some DMVs that you can use to check the stats of the data migration and Database’s & Table’s Stretch status:

select * from sys.dm_db_rda_migration_status

select * from sys.remote_data_archive_databases

select object_name (885578193), * from sys.remote_data_archive_tables

–> Let’s try to DELETE the Stretch Enabled table:

DELETE FROM dbo.EmployeeStretch WHERE EmployeeID >= 10

It throws following error:

Msg 14826, Level 14, State 1, Procedure trigger_RemoteDataArchive_885578193, Line 94
Update and delete of rows eligible for migration in table ‘EmployeeStretch’ is not allowed because of the use of REMOTE_DATA_ARCHIVE.
Msg 3609, Level 16, State 1, Line 79
The transaction ended in the trigger. The batch has been aborted.
 

Please Note: As of CTE 2.3 release you can’t run UPDATE or DELETE operations on a Stretch-enabled table.
 

You can check the same demo to configure StretchDB here:

Check & Like my FB Page.