Skip to content

Get the most out of MSSQL !

Consultant & Trainer for all things MSSQL

  • Home
  • Company
  • Business Intelligence
  • Simply Better Business

SQL 2016 – Stretch database in detail –CTP 2.3 screenshots with BOL corrected now

Posted on September 10, 2015March 20, 2021 by jayanth.kurup

If you have been following the buzz around MS SQL 2016 you would surely have heard of Stretch database. The concept is interesting and new and probably something we could have used even in SQL 2014. I know a few clients already using Azure data sync to move historical data to the cloud I can’t comment on the economic viability of offloading historical data to the cloud since I would prefer not being billed at all for data I don’t use but that’s just me. In this post I try and cover this feature in as much detail as I can.

So what is stretch database?

This feature allows the DBA to move historical data to the cloud, therefore leaving the current OLTP data on premise and the historical data in a secondary cloud location where it is still seamlessly queryable. Potential benefit include cheap storage of billions on rows of archive data without the data management headaches that come with it. Potential disadvantages include limited functionality of the tables where this functionality is being used and slower query response time due to network IO.

How do you enabled it?

Run the below query to enable Remote Data Archive at the server level

sp_configure 'remote data archive',1
GO

RECONFIGURE

Enable the feature at the database level by right clicking the database as shown below

Follow the steps in the below wizard

Select the tables you want to push to the cloud

Login to the Azure account

Note it’s always better to make sure you use a domain authenticated account when connecting to Azure, so it’s going to require both DBA and Network admin to setup and configure the VPN to allow users and machines to connect seamlessly in a hybrid environment.

Enter the azure credentials choose a region close to the local DB to ensure the least Network latency. Note Azure databases only use SQL authentication. The username and password doesn’t already have to exists in your azure subscription they will be created for you automatically.

Press Next to reach the summary screen.

Press Finish to start setting everything up.

Once completed you should see the below screen.

If this step fails with below error message

Sep 15 2015 02:06:03 [Informational] TaskUpdates: Message:Task : ‘Stretch the Database stretchdb’ — Status : ‘Failed’ — Details : ‘Task failed due to following error: Alter failed for Database ‘stretchdb’. ‘.

It is most likely an issue with the firewall and not being able to connect to Azure Server.

If everything goes well and you open the log files you will a new server has been created in the Region

This can be validated by logging into Azure too. You will see that by default the standard Edition is selected with a Storage Size of 250 GB, ( FYI this can be expensive especially when you can use the same server configuration to support a Cloud Availability Group Replica.

You will also notice that a linked server connection is created on the local database which points to the Azure Cloud Database.

Once configured you can enabled Stretch database on a particulat table by running the below command

ALTER TABLE [dbo].[clientlist] ENABLE REMOTE_DATA_ARCHIVE
    WITH (migration_state = ON)

Note the syntax is ENABLE REMOTE_DATA_ARCHIVE and NOT ADD as shown in BOL

You will also notice there is no way to limit the rows so essentially you should have an archive table into which the data can be moved from the transactional table before it being moved to the cloud.

remotedataarchive-win-pss96iqg44m-madworks-20150910-050738728

Here is the table as seen when connecting to the cloud database via SSMS

Notice there are no Columns folder. Where executing a query you can tell that there is a remote connection being established by viewing the execution plan.

The performance for querying is highly dependent on the network. The tables do not accept default values for columns so most OLTP table are out of the questions here. You can’t create any clustered indexes or constraints either and some datatypes are not supported. FYI I was also not able to create any nonclustered indexes either.

More limitations are mentioned here.

You can query the table directly within the cloud, this also displays the hidden column batchId and could be useful for reporting purposes when used with PowerBI. Note Using PowerBI gateway doesn’t make sense in this case since you still end up fetching the data from the cloud even if the query is initiated locally.

I also tested this feature with the row level security feature introduced in SQL 2016 and row level security is not understood by this feature as a result you will get the below error.

Here is what happens when you try to run insert , update and delete operations , as you can see inserts go thru but update and deletes fail.

Considering the above a serious question you need to ask yourself is how to move transaction data into an archive table that only allows inserts? Since your only allowed to perform inserts you need to keep in mind that you need an identity column can be used to specify a range or you need an ETL procedure that only uses type 2 SCD else you end up with duplicate records which can mess up the totals. I will most more on this feature soon but initial thoughts makes it feel like a half-baked feature that is not yet production/real world ready.

Please Consider Subscribing

Subscribe

CategoriesAzure, Databases, Performance TuningTagsability, account, Action, administration, advantage, age, AI, Analysis Services, app, archive table, ASP, aspx, availability, AWS, Azure, Azure account, Azure database, Azure databases, Bangalore, benefit, Bengaluru, Better, BigData, bill, billion, BLR, Business Intelligence, buzz, CaL, call, case, CHOOSE, client, Cloud, Clustered, CLUSTERED INDEX, column, command, comment, concept, configuration, connection, constraint, Consultant, Consulting, Corporate, count, CREATE, cred, credential, CTE, Data, database, datatypes, date, Dates, dba, dbo, default value, detail, development, disadvantage, domain, edition, en-US, Enabled Business Solutions, enabledbusiness, enabledbusinesssolutions, end, environment, error, error message, etl, exe, exec, execution plan, EXISTS, expert, feature, file, Finish, firewall, folder, form, format, fun, function, functionality, FYI, gate, group, head, Highly, historical data, Hotmail, identity, identity column, INCLUDE, index, indexes, India, info, information, insert, int, interest, issue, Jayanth, Kurup, Latency, level, lib, library, lie, limit, limitation, load, location, Log file, login, machine, madworks, mail, management, maroon, Mary, message, microsoft, Migration, min, mind, ML, move, MS SQL, ms sql server, MSBI, msdn, MSSQL, MYSQL, name, need, network, network latency, new server, NONCLUSTERED, nonclustered index, note, notice, ol style, OLTP, operation, Oracle, part, password, performance, plan, png, point, post, power, power pivot, Power Query, PowerApps, PowerBI, Powershell, pre, premise, Press, Press Finish, procedure, product, production, purpose, Python, Query, question, RDBMS, Record, red, region, Remote, remote connection, report, Reporting, response, result, row, row level security, ROWS, run, script, sec, second, security, SELECT, sense, server, Server level, set, setup, show, side, SKU, solution, sql, SQL 2000, SQL 2005, SQL 2008, sql 2008 r2, sql 2012, SQL 2014, SQL 2016, SQL 2017, SQl 2019, SQL Authentication, SSAS, SSIS, SSMS, SSRS, standard, standard edition, star, start, state, status, step, storage, Stretch database, subscription, SUM, summary, summary screen, support, sync, syntax, T-SQL, tab, Tables, task, test, thing, thought, tools, total, trainer, Transact, Transaction, tuning, type, update, Updates, Upgrade, uploads, Uri, user, username, validate, value, VALUES, Very large database, view, Virtual, virtual machine, visual studio, VM, VPN, window, windows, WindowsAzure, wizard, word, work, world

jayanth.kurup

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.

Post navigation

PreviousPrevious post: The History of War Analyzed
NextNext post: SQL 2016 – Dynamic Data Mask –& Hacking the feature

The Latest

  • Monty Hall Simulation using T-SQL April 5, 2022
  • Query to quickly profile a column February 7, 2022
  • Outlook 2019 keeps asking for password multiple times December 9, 2021
  • Part 2:- Learning T SQL for beginners – Datatypes September 28, 2021
  • Part 1:- Learning T SQL for beginners- SQL, Tables and Nulls September 27, 2021
  • Query to find execution time of Jobs July 28, 2021
  • A simple script to decapitalize Column names July 19, 2021
  • My personal side effects with Covishield June 27, 2021
  • Setting up and Configuring CUDA, CUDNN and PYTorch for Python Machine Learning. June 3, 2021
  • keras.utils.generic_utils’ has no attribute ‘populate_dict_with_module_objects May 30, 2021

Find By Category

  • Azure
  • Databases
  • Events
  • Performance Tuning
  • PowerBI
  • Uncategorized
  • Website Design

Archive

  • April 2022 (1)
  • February 2022 (1)
  • December 2021 (1)
  • September 2021 (2)
  • July 2021 (2)
  • June 2021 (2)
  • May 2021 (2)
  • April 2021 (5)
  • March 2021 (10)
  • January 2021 (2)
  • November 2020 (2)
  • October 2020 (3)
  • September 2020 (4)
  • August 2020 (6)
  • July 2020 (1)
  • June 2020 (32)
  • May 2020 (18)
  • April 2020 (2)
  • March 2020 (4)
  • February 2020 (5)
  • January 2020 (1)
  • December 2019 (1)
  • November 2019 (14)
  • October 2019 (3)
  • September 2019 (1)
  • July 2019 (3)
  • June 2019 (2)
  • May 2019 (1)
  • April 2019 (2)
  • March 2019 (1)
  • January 2019 (4)
  • December 2018 (2)
  • November 2018 (4)
  • September 2018 (6)
  • August 2018 (2)
  • July 2018 (3)
  • June 2018 (4)
  • May 2018 (1)
  • April 2018 (4)
  • March 2018 (3)
  • February 2018 (3)
  • January 2018 (1)
  • December 2017 (2)
  • November 2017 (4)
  • August 2017 (2)
  • July 2017 (5)
  • May 2017 (1)
  • March 2017 (3)
  • January 2017 (3)
  • December 2016 (2)
  • November 2016 (2)
  • October 2016 (4)
  • September 2016 (1)
  • August 2016 (1)
  • July 2016 (1)
  • June 2016 (1)
  • May 2016 (2)
  • April 2016 (1)
  • March 2016 (14)
  • February 2016 (10)
  • January 2016 (19)
  • December 2015 (3)
  • November 2015 (5)
  • October 2015 (10)
  • September 2015 (9)
  • August 2015 (16)
  • July 2015 (13)
  • June 2015 (4)
  • May 2015 (2)
  • April 2015 (2)
  • March 2015 (7)
  • February 2015 (3)
  • January 2015 (22)
  • December 2014 (1)
  • November 2014 (5)
  • October 2014 (12)
  • September 2014 (5)
  • August 2014 (7)
  • July 2014 (41)
  • June 2014 (9)
  • May 2014 (12)
  • April 2014 (32)

Members Only

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
Proudly powered by WordPress