Posts Tagged ‘Stretch Database’

Stretch your on-premise Database/Table to Azure SQL Database with StretchDB – SQL Server 2016

September 15, 2015 4 comments

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.

SQL Server 2016 - StretchDB.JPG

–> 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]

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


Step #2. Enable Database for Stretch to Azure

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

SQL Server 2016 - StretchDB 01

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

SQL Server 2016 - StretchDB 02

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

SQL Server 2016 - StretchDB 03

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

SQL Server 2016 - StretchDB 04

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

SQL Server 2016 - StretchDB 05

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

SQL Server 2016 - StretchDB 06

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]

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

-- Batch 1
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT 'Manoj P'
SELECT 'Saurabh S'
SELECT 'Keshav K'
SELECT 'Vivek S'
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:

SQL Server 2016 - StretchDB 07

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:

SQL Server 2016 - StretchDB 08

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:

SQL Server 2016 - StretchDB 09

Step #5. Load some more data in batches

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

select * from dbo.EmployeeStretch

-- Batch 3
INSERT INTO dbo.EmployeeStretch (EmployeeName)
SELECT 'Nitya 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:
SQL Server 2016 - StretchDB 10

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

sp_spaceused @objname = 'dbo.EmployeeStretch'
--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'
--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'
--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'
--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.


Microsoft SQL Server 2016 Public Preview (CTP2) Available – download now

May 28, 2015 3 comments

Just got an email from Microsoft:

Microsoft SQL Server 2016 Public Preview Available – Try it Today!

[Register and Download CTP-2 Evaluation version (180 days)]


Direct download link:


–> Smooth Installation with new setup-option and new features in SSMS:

Microsoft SQL Server 2016, the next major release of Microsoft’s flagship database and analytics platform, provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. The first public preview, SQL Server 2016 Community Technology Preview (CTP) 2, is now available for you to download to trial via Microsoft Azure.

–> Try it today for an early look at these new capabilities:

– Always Encrypted: helps protect data at rest and in motion

– Stretch Database: dynamically stretch your warm and cold transactional data to Microsoft Azure, demo video.

– Real-time Operational Analytics: our in-memory technologies are enhanced to provide real-time analytics on top of breakthrough transactional performance

–> Additional capabilities include:

1. PolyBase: More easily manage relational and non-relational data with the simplicity of T-SQL.

2. AlwaysOn Enhancements: Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries.

3. Row Level Security: Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application, demo video.

4. Dynamic Data Masking: Supports real-time obfuscation of data so data requesters do not get access to unauthorized data. Helps protect sensitive data even when it is not encrypted, demo video.

5. Native JSON support: Allows easy parsing and storing of JSON and exporting relational data to JSON, demo video.

6. Temporal Database support: Tracks historical data changes with temporal database support.

7. Query Data Store: Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance.

8. MDS enhancements: Offer enhanced server management capabilities for Master Data Services.

9. Enhanced hybrid backup to Azure: Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines. Also, you can stage backups on-premises prior to uploading to Azure.

–> Other Benefits:

1. Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics

2. New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes

3. Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database

4. Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android

5. Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology

6. Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes, demo video.

7. Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

Learn more about SQL Server 2016: SQL Server 2016 Preview Page

Microsoft announced SQL Server 2016 – New features and enhancements

May 9, 2015 2 comments

On Monday, 4th May 2015 Microsoft at Ignite event announced the new version of SQL Server i.e. SQL Server 2016, which will be available for Public Preview this summer.

>> MSDN Blog announcement:

>> Microsoft SQL Server 2016 official page:

This version of SQL Server is going to be a major release with new features and will also overcome some limitations of SQL Server 2014.


-> New Performance Enhancements:

– In-memory OLTP enhancements: Greater T-SQL surface area, terabytes of memory supported and greater number of parallel CPUs, provide up to 30x faster Transactions, more than 100x faster Queries than disk-based relational databases and Real-time Operational Analytics, Demo video.

– Query Data Store: Monitor and optimize query plans with full history of query execution.

– Native JSON: Parsing & storing of JSON as relational data & exporting relational data to JSON, as it is becoming a popular format to store NoSQL/Unstructured data, Demo video.

–> Security Upgrades:

– Always Encrypted: Help protect data at rest and in motion with the master key residing with the application & no application changes required.

– Row Level Security: Customers can implement Row-level Security on databases to enable implementation of fine-grained access control over rows in a database table for greater control over which users can access which data, demo video.

– Dynamic Data Masking: Real-time obfuscation of data to prevent unauthorized access, demo video.

–> Even Higher Availability, with Enhanced AlwaysOn:

– Up to 3 synchronous replicas for auto failover across domains, for more robust High Availability and Disaster Recovery

– Round-robin load balancing of replicas

– DTC & SSIS support

– Automatic failover based on database health

–> Hybrid Cloud Solution:

– Stretch Database: Stretch operational tables in a secure manner into Azure for cost effective historic data availability, that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure, demo video.

– Azure Data Factory integration with SSIS

–> Deeper Insights Across Data

– PolyBase: Manage relational & non-relational data with the simplicity of T-SQL.

– Enhanced SSIS: Designer support for previous SSIS versions and support for Power Query.

– Built-in Advanced Analytics: Bringing predictive analytic algorithms directly into SQL Server.

>> Check the SQL Server 2016 Datasheet here for more information on this:


–> YouTube Videos on SQL Server 2016: