The Quick Hot and Cold of Stretch DB

I was very privileged to attend Joe Yong’s in person presentation on Stretch DB a couple of weeks ago at the Pacific North West SQL Server User Group (PNWSQL). He gave introduction on Stretch DB, the scenarios that Stretch DB would be ideal, how to set it up, what it means when you stretch your database and the road map. The presentation was based on his Ignite session, with an update on the CTP2.2 version.

Stretch Database – The Important Bits

A quick summary of the benefits in stretching your database to the cloud is listed below.

Stretch Tables To The Cloud

Source: Joe Yong’s PNWSQL Slide Deck


Below is a quick list of the highlights that I have learned in the session:

  1. Users can nominate an entire table to be stretched (e.g. Archive data that contains data from 3 years and older).
  2. Users can nominate the definition of “cold” part (which is what rows will be stretched) of the table to be stretched.
  3. When the database is stretched, it will trickle the data migration to Azure
  4. Is the stretched database on Azure just an Azure SQL Database (SQL DB)? Yes, the Azure SQL Database V12 version. But not all functionality of Azure SQL DB is enabled. As an example, you can take advantage of the Point In Time Restore of Azure SQL DB on the cold (Stretched) side of the database. However you won’t be able to connect to the stretched database directly and query it in the final release though that isn’t locked down today.
  5. The backup on the database that is stretched is shallow.
  6. What’s the big deal? Aside from the great use case scenario outlined here, I think if you are slowly introducing the cloud adoption into your organisation’s culture, this would definitely help. First, the cold data is well secured, i.e you can use Always Encrypted feature. I would imagine as more Security features are made available on the Azure SQL DB side, Stretch DB soon can take advantage of it.
  7. There is going to be the ability to snapshot a version of the cold data. This means you can restore another version of the cold data as long as you have the corresponding hot database. i.e. Snapshot can be as at 1 July 2015 of the cold data and linked to a hot database that is current, i.e. today’s date – 24 July 2015.
  8. Data in the cold side cannot be modified directly through Azure SQL Database. You can do that through the local database.

Adopt, Advise

If your companies have good use cases for Stretch DB (or Microsoft Cloud Platform products), please nominate them today to join Cloud Platform Advisors – a.k.a Technology Adoption Program (TAP). It’s a private engineering feedback community for exchanging ideas and best practices between engineers.


SQL Server 2016 CTP 2.2 and Beyond

Stretch DB Post CTP 2

Source: Joe Yong’s PNWSQL Slide Deck


Want More Info?

Andrew Pruski (b | t) has detailed a step-by-step guideline on how to setup Stretch DB here.

Check out other features in SQL Server 2016 public preview announcement.

Watch Joe Yong’s Ignite session on Stretch DB from start to end. The last 8 minutes with Q&A had interesting discussions.

Stretch DB FAQ also is useful if you’re checking out CTP 2 version.


Edit: Special thanks to Joe Yong for clarifying points 2 and 4 of the Stretch Database – The Important Bits section