How Does Stretch Database Works in SQL Server 2016

SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides many benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Before deciding to this new feature in production environment, it is important to understand how does Stretch Database works and its behavior. First, to enable stretch database, we need to enable the instance level configuration option “remote data archive”, after which we will be able to Enable a Stretch Database for a database or table. Once we enable stretch database for atleast one database and one table, it will internally start to migrate our historical data to Azure SQL database. If the historical data is stored in a separate archive table already, then we can migrate the entire table, if table contains both historical and current data then we need to specify a filter predicate to select the rows which are to be migrated to the Azure SQL database. Some of the cool features with the Stretch Database is that it ensures that no data is lost in case a failure occurs during the data migration to Azure SQL database. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view also provides us with the status of migration.

Also there us option to pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth or to migrate data only during non-business hours or during low activity period. Another cool thing about using stretch database is that we don’t have to change existing queries or client applications. We can continue to have seamless access to both local and remote data, even during data migration. There is a small amount of latency for remote queries, but you only encounter this latency when you query the historical data.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Benefits at Glance of Using New SQL Server 2016

SQLServerF1

Microsoft has released most awaited RTM version of SQL Server 2016 on June 1 2016. There was lot of buzz around with the release of new SQL Server 2016 and has been already downloaded by many community DBAs and developers to start testing or learning using the new features. Just like any other SQL Server version, even in new SQL Server 2016, there has been lot of new features introduced in areas like security, performance, scalability, stability, etc. Also there is support for SQL Server on linux in pipeline, which makes it much more interesting and to enter new companies which did not use windows operating system. As a DBA or developer it is very important to gets handon new features and suggest to the management how it will benefit the clients or customers or their own business with the new features. Always, test in a test environment thoroughly before deciding to move to production. Also, as like any new version, it is expected to have lot of bugs too, so it is up to you to decide whether to wait till a service pack is released or just go ahead and install it right away now. My preference is to start testing on test environment and see how application reacts to it, and if the new features are beneficial and performance has improved with the applicaion, then I prefer to go ahead with the new SQL Server 2016 on production environment.

Following are some of the important benefits of new SQL Server 2016 which we might want to keep on top of our head.
– Microosft claims Enhanced in-memory performance which provides up to 30x faster transactions, and more than 100x faster queries than disk based relational databases and real-time operational analytics.
– New security feature Always Encrypted which helps in protecting our data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without requiring any changes to the application.
– Built-in advanced analytics– provide the scalability and performance benefits of building and running advanced analytics algorithms directly in the core SQL Server transactional database.
– Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology

– New features like Stretch Database technology keeps more of our historical/cold data at our fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes.
– Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore our on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure.
– Closer integration with Azure SQL Database which helps more customers comfortable to migrated to cloud with the new security features.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings