Top SQL Server 2016 Features: The Stretch Database

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.