Select Page

Top SQL Server 2016 Features: The Stretch Database

Author: Ben Jongewaard | | June 18, 2019

The Stretch Database is one of the more important new features of SQL Server 2016 that blend on-premise and cloud environments into a single entity. It can offer organizations a secure and stable point of entry into hybrid cloud utilization, and significantly reduce the costs of premises-based cold data storage.

What is a Stretch Database?

The Stretch Database feature in SQL Server 2016 is a hybrid solution for cold data storage which blends the capabilities of a premises-based SQL Server and an Azure SQL Database in the cloud for companies which are actively using platform-as-a-service (PaaS) capabilities. Cold data is defined as infrequently-used data assets which are stored in the cloud to save costs. The benefits of this solution include and aren’t limited to:

  • Lowers the cost of cold data storage compared to premises-based solutions
  • Provides always-online access to cold data via queries
  • Can assist in securing data via encrypted cloud connectivity
  • Streamlines premises-based database maintenance and backups
  • Does not require application modifications to work with hybrid design

Effectively, a Stretch Database allows you to move some portion of your data to a secure cloud environment, which reduces your on-premises storage and processing costs. When you query tables which are stored in an Azure SQL Database in the cloud, the SQL Server query optimizer knows which servers and rows are on-premises and which are in Azure and allocates the workload accordingly.

Do You Need the Stretch Database?

Organizations may choose to adopt the Stretch Database feature for a number of reasons. However, many of the most common use cases are related to reducing the cost and footprint of premises-based data storage, or streamlining a cloud migration. Your organization may be a candidate for this SQL Server 2016 feature if you:

  • Need to reduce the footprint of premises-based data which is used infrequently
  • Want to test secure hybrid cloud solutions while maintaining existing infrastructure
  • Anticipate a high demand for scalability and growth in cold or archived data stores
  • Need to improve the performance of a premises-based database which contains cold data

Disadvantages of Stretch Database

The Stretch DB is not the right solution for every organization. According to Microsoft, this feature is designed to provide “a set of extremely elegant solutions to problems common to larger databases.” According to this whitepaper, the cons and trade offs of using the Stretch Database feature can include:

  • Moderate performance reductions compared to premises-based data storage
  • Updates and deletes are handled as an administrative function
  • Relatively complex to set up and implement

The Hybrid Benefits of the SQL Server 2016 Stretch Database Feature

For some organizations, the Stretch Database feature in SQL Server 2016 can be the right hybrid solution. You can leverage the Azure platform in the cloud and all of its features for stability and scalability while maintaining your current investment in premises-based databases.

Read This Next

Upgrade or Bust: SQL Server 2008

Are your databases and infrastructure still running SQL Server 2008? Your chances of risking business disruption increase when you rely on databases that are out of support.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.