Select Page

Top SQL Server 2016 Features: The Stretch Database

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

SQL Server 2008 Extended Support Ends 2019 – Plan Now

Failing to switch can result in increased security vulnerabilities, a lack of business agility and no official support. Upgrading is the only way forward.

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
Oracle DBA Skills

8 Things Every Beginner Oracle DBA Should Know

A checklist of eight critical skills and areas an entry-level Oracle DBA should be familiar with to succeed on the job.

Patrick Gates | November 30, 2016

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine over one hundred logins in the source server, you need to migrate them to the destination server. Wouldn’t it be awesome if we could automate the process?

JP Chen | October 1, 2015

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.