SQL Server 2016 Stretch Database: What Can It Do?

Ann All

Updated · Jul 05, 2016

By John Hughes, ManageForce

When Microsoft released SQL Server 2016, the release was accompanied by a slew of advanced features heavily tailored to make data more malleable and useful for business.

In that light, one of the most exciting features of SQL Server 2016 is the Stretch Database feature.

What is SQL Server Stretch Database?

SQL Server 2016 Stretch Database connects a database between on-premise hosting and Microsoft's Azure cloud. As such, it gives users a cohesive, transparent view of a single query that pulls data from two different locations. Without making any application changes, Stretch Database pulls both hot and cold data from wherever they're stored and makes the data seamlessly accessible to the end-user in one transparent view.

After it's properly configured, Stretch Database automatically moves cold/historical data to the Azure cloud based on custom-defined policies. Put another way, with the help of SQL Server experts, you set parameters to decide what data is hosted on-premise and what data is stored in the cloud.

As such, hot data that is regularly used will stay on-premise. But Stretch Database doesn't just move cold data to the cloud and leave it there; it also makes all of that data seamlessly accessible to users. It's as if that cold data was stored in a database on-premise, only you're not paying for all that storage and maintenance.

So, is this feature as good as it sounds? What benefits does SQL Server 2016 Stretch Database bring to the table?

Benefits of SQL Server Stretch Database

A lot of organizations don't take the time to separate hot data from cold data; they just keep all of their data together without differentiating it.

In general, about 10 percent of a company's data is active (hot) and the other 90 percent is cold. But when businesses separate storage between hot and cold data and host it on-premise, hot and cold data cannot be seamlessly integrated into one application for the end user. So businesses end up paying to store all of their undifferentiated data on-premise because they want to have the ability to access and use it at any time, regardless of how often they actually tap into the data.

But by storing all of that data together on-premise without differentiating it, businesses are simply burning cash on IT infrastructure and maintenance without maximizing the value they could be getting out of data.

Moreover, to the extent that businesses are simply paying for something they may need, this is not the most efficient way to stretch one's dollar.

The biggest benefits of Stretch Database lie in how it solves both your cost and accessibility problems.

Save Money on Data Storage

When stored on-premise and separated, cold data is not available on-demand to end users. But by stretching your data seamlessly from SQL Server to Microsoft Azure cloud, Stretch Database will seamlessly connect hot data (stored on-premise) and cold data (stored in the cloud), rendering hot and cold data equally accessible.

By delegating the storage of less active data to the cloud, your business optimizes its storage capabilities and data maintenance. At the same time, you will free up valuable resources needed to manage your on-premise data more efficiently.

For one, backups for any data you're hosting on the cloud will run automatically. Not only will the backup process run faster, but you will also no longer have to worry about manually running backups. This translates into significant time savings for your internal IT team.

Furthermore, your on-premise storage needs will be greatly reduced. As it requires less resources to maintain, Azure storage can be up to 80 percent less expensive than on-premise storage. As such, Stretch Database promises to benefit your company both operationally and economically.

Access Cold and Hot Data Seamlessly

Stretch Database also makes the cold data available to end-users on-demand. In other words, businesses can gain immediate access to all of their SQL Server data regardless of where it's stored or what type of data it is.

By analyzing your on-premise infrastructure and data, experienced managed service providers can help you properly configure your system by setting up the necessary policies in the database to determine where data is stored.

Following this process, SQL Server will be able to automatically move data between the cloud, on-premise storage and the final view of the enterprise applications you're using. This means that your entire data set and tables will be accessible online and available to query at all times.

Moreover, on the front-end, the location of the data is completely transparent to your users. You don't have to make any changes to applications when you enable Stretch Database, as the feature does all the work and movement of data in the background.

Implementing and Using Stretch for Your Advantage

The biggest challenge with this feature is that a lot of organizations don't differentiate their hot and cold data. As such, before your organization can implement Stretch Database, you first need to analyze all of your data, IT infrastructure and business needs in order to determine what data should remain hosted on-premise and what data should be migrated to the Azure cloud.

You won't have to change the applications your company uses in order to adapt to the hybrid storage model Stretch Database uses. But to properly exploit the full potential of SQL Server 2016 Stretch Database, you will want to tap into the talent of managed service providers like functional and technical analysts, as well as experienced SQL Server administrators. They will be able to help you configure the requisite permissions and policies, set up your cloud orchestration to optimize database performance, and ultimately cut costs. To work with the best possible team to meet your needs, be sure to ask potential partners those tough questions.

John Hughes is a veteran technology executive who has spent his career focused on ERP, database, cloud, and enterprise applications. He is co-founder and managing principal of ManageForce, a provider of expert managed services and professional services dedicated to cloud and on-prem enterprise applications like JD Edwards, Oracle, NetSuite, Microsoft; databases including SQL, MySQL, Oracle, DB2; as well as business intelligence, Big Data and IoT. He has more than 25 years of technology sales and executive leadership experience working for and closely with such industry leaders as Oracle, Microsoft, IBM, NetSuite, Salesforce and MicroStrategy.

  • Data Management
  • Research
  • Ann All
    Ann All

    Public relations, digital marketing, journalism, copywriting. I have done it all so I am able to communicate any information in a professional manner. Recent work includes creating compelling digital content, and applying SEO strategies to increase website performance. I am a skilled copy editor who can manage budgets and people.

    Read next