When I first started driving in North America, one of the things that I noticed along the freeway is a self-storage facility. Having not seen one before, I asked my dad what they were for – they are space rented out on a short-term basis where you can keep your personal stuff. If people don’t have enough space to keep their stuff at home, they can either move into a larger house or rent one of these self-storage spaces.

self storage units (9) by Scott Myers

Every business that stores their data in databases will eventually face this challenge. Be it for a compliance requirement or just for archival purposes, data collected needs to be stored and managed in the long term. And especially in these age of data analytics, there’s even more justification keep on keeping data. There’s a reason the prices of storage media has dropped significantly throughout the years.

Similar to us hoarding personal stuff at home, there are those that we use on a regular basis – like your favorite sweater that you wear almost every week – and those that don’t – like that vintage fretless bass guitar that you haven’t touched for years.  We like to keep the stuff that we use on a regular basis within reach for ease of access and maybe those that we rarely use stored elsewhere. And as we accumulate more stuff, the amount of storage space we need increases.

I’m sure every (SQL Server) DBA can relate to this. The database has grown huge that some data processing tasks that used to take a few minutes are now taking ages to complete. What used to fit in a 500 GB-sized disk could no longer fit in a 1 TB-sized drive. And it still continues to grow. You now worry about data archival, backups, data processing and consistency checks. And while your organization is not in the business of hoarding large amounts of data, you do it to support business operations.

Stretch Database Feature in SQL Server 2016

The upcoming version of SQL Server introduced a new feature called Stretch Database. As per Books Online, this feature allows you to “migrate your historical data transparently and securely to the Microsoft Azure cloud.” Think of it this way: your on-premise database has some historical tables on Microsoft’s Azure SQL Database.

I see a couple of different use cases for this feature but one that stands out is the ability to “outsource your data archival process.” As a high availability and disaster recovery expert, the biggest concern that most of my customers have is meeting recovery objectives and service level agreements (SLAs.) The larger the database becomes, the longer it takes to restore it from backup or bring it online. As a side effect of that, you also need a large amount of storage space to keep up with the requirement – the database files, the backups and the copy of backups that you need to maintain for your retention policies. I’ve sat down with some of my customers to define recovery objectives and SLAs on a per-database object basis. While that may be an unusual exercise for some DBAs, the reality is that we will have to deal with managing a large database in terms of storage requirements.

Similar to how we deal with our personal stuff, we can keep hot data in our on-premise SQL Server database and move archived data on Azure SQL Database via the Stretch Database feature. It could be a table exclusively for historical data or a table that has a combination of current and historical data.

What Are Your Concerns?

Looking at a new feature always raises several concerns. Microsoft Certified Master and Data Platform MVP Brent Ozar (blog | Twitter) raised some valuable concerns about the Stretch Database feature in this blog post.

While SQL Server 2016 has not been released to manufacturing (RTM) just yet, you can already test it out to see if it meets your requirements. If you have questions and concerns about the Stretch Database feature, let me know or you can file a feedback/comment on the Microsoft Connect website for SQL Server.

In a future blog post, I’ll walk you thru the process of enabling the Stretch feature an on-premise SQL Server 2016 database and looking at the data archival process happening in the background. It will feel like somebody else is doing the data archival process for you.

Additional Resources

• Stretch Database: SQL Server 2016
• Backup and restore Stretch-enabled databases
• Enable Stretch Database for a database (I feel like the title is a bit redundant)