Introducing Stretch Databases for SQL Server 2016

Microsoft SQL Server 2016 is a hybrid cloud environment designed to allow data and services to be accessed from any device, enabling users to perform advanced analytics within their databases and create business insight visualizations.

One of the highly anticipated features new to Microsoft SQL Server 2016 is Stretch Databases or Stretch DB

A combination of on-premise computing resources and both private and public cloud computing are commonly used by IT departments. To meet organizations’ needs and to allow them to choose how and where applications are run, Microsoft has provided both SQL Server 2014 and upcoming 2016 with the means to work in hybrid environments.

Stretch Database provides users with the ability to “dynamically stretch” both warm data—business data that is online and active—and cold data—any old data archived in a closed file—to Microsoft Azure, according to Microsoft’s preview guidance on SQL Server 2016. This can include online transaction processing (OLTP) data.

This stretching process is secure and doesn’t require users to make any application changes. If, for example, you need to access data in a 5 TB database, restoring it would take valuable time. Furthermore, you may not need all of the data, which would make total database restoration both overly complicated and expensive. Stretch DB allows a user to selectively migrate exactly the data needed, saving users time and providing a cost-effective approach for working with archived data.

How Does Stretch Database Work?

Stretch DB works by moving a portion of your data to the secure cloud. As described in Redmond Magazine, Stretch DB cuts costs by reducing data storage and processing expenses:

The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly. The query processing on the Azure rows takes place in Azure so the only latency is for the return of the rows over the network.

If, for example, old-order history data is stored by the organization in a cloud-based Microsoft Azure repository, SQL Server can access the Azure SQL Database, retrieve the information, and export it to an application when needed. The user is able to specify exactly the data she wishes to stretch—a single table, for example.

Once the selection is made, trickle data migration is used to move that data to Azure. The data can then be returned to on-premise storage. The data exchange is both transparent and bi-directional.

During the stretching process, all the characteristics of the database stay intact. The code or stored procedures do not change. The user access control does not change either. This maintains the integrity of the data, while still enabling staff to work with it.

Benefits of Stretch Database Technology

The most attractive benefit associated with Stretch Database technology is acquiring hyperscale cloud capability and new hybrid scenarios facilitated with SQL Server 2016. If your core transactional tables grow, for example, some historical data may need to be archived to lower the cost of operating the database and to maintain its performance. With Stretch Database, you can still access both warm and cold data, which has numerous business advantages. Stretch Database can be configured, for example, to adhere to any policies or rules for archiving data. And being able to accomplish these tasks using Azure lowers the operational cost additionally.

In addition, the Stretch Database feature can be used in tandem with the new Always Encrypted feature for data security. This new SQL Server 2016 technology applies to resting data as well as any data being transmitted. The Transparent Data Encryption secures the data. Keys used are never provided to the database system or cloud service provider. Stretch Database also works with Row Level Security.

The scenarios in which your organization may wish to use Stretch Database include a variety of data retention-related tasks, such as ensuring regulatory compliance, auditing, or business planning.

Before Getting Started with Stretch DB

Organizations and users should consider a variety of issues before blindly using Stretch DB. As Microsoft, in its white paper devoted to explaining how SQL Server 2016 functions in a hypercloud environment, states:

With Stretch Databases in SQL Server 2016, you get a set of extremely elegant solutions to problems common to larger databases. That being said, there are some moderate performance reductions when accessing the cold data as compared to keeping all data on-premises, and any updates or deletes are handled as an administrative function. However, based on the nature of archived/old data, those tradeoffs will generally be worth the lower [total cost of operation], reduced complexity, and other benefits that Stretch Databases provides [sic].

One of the smartest things you could do before upgrading, a migrating, or installing SQL Server 2016 is get advice from the database experts at Datavail. We can help you plan your move, make your move, and make any adjustments needed after your move. Contact us today!

Image courtesy Microsoft Corporation, from Hyperscale Cloud Technical White Paper, used under Fair Use: Commentary.