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

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.

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.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your 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

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

Imagine over 100 logins on 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.