SQL Server 2016 Stretch Database - - Concept and Architecture
I have attended one interview a long back and an Interviewer asked me "What is Stretch Database?" and I was like (o_o)-???? This is something I never heard. So I said I'm not aware. so he said this is new feature in SQL Server, try to get the details about it. Lucky they have not selected me.. :). I decided to get the Notes about Stretch Database.
Stretch Database allows you to archive your historical data transparently and securely. In SQL Server 2016 Community Technology Preview 2 (CTP2), Stretch Database stores your historical data in the Microsoft Azure cloud. After you enable Stretch Database, it silently migrates your historical data to an Azure SQL Database. Few silent features as below:
- You don't have to change existing queries and client Apps. You continue to have seamless access to both local and remote data.
- Your local queries and database operations against current data typically run faster.
- You typically enjoy reduced cost and complexity.
This DB offers local server performance for hot data and cloud storage for old data without any change to the application. The basic use case is a table that contain a small amount of hot data that users normally care about and a large amount of old data that should have been moved to off-line archive but the users still able to query\access it.
When you enable Stretch Database on a database, it creates a secure linked server definition in the on-premises SQL Server. This linked server definition has the remote endpoint as the target. When you enable Stretch Database on a table in the database, it provisions remote resources and begins to migrate eligible data, if migration is enabled.
Queries against tables with Stretch Database enabled automatically run against both the local database and the remote endpoint. Stretch Database leverage's processing power in Azure to run queries against remote data by rewriting the query. You can see this rewriting as a "remote query" operator in the new query plan.
In SQL Server 2016 Community Technology Preview 2 (CTP2), Stretch Database migrates entire tables. This assumes that you already move historical data into a table that's separate from current data.
Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor, to identify databases and tables for Stretch Database. For more info, For more details see Identify databases and tables for Stretch Database.
Migration: Stretch Database ensures that no data is lost if a failure occurs during migration. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view provides the status of migration.
Querying: You don't have to change existing queries and client apps. You 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 that's archived remotely.
Refer MS article for more details Stretch Database.
I have attended one interview a long back and an Interviewer asked me "What is Stretch Database?" and I was like (o_o)-???? This is something I never heard. So I said I'm not aware. so he said this is new feature in SQL Server, try to get the details about it. Lucky they have not selected me.. :). I decided to get the Notes about Stretch Database.
Stretch Database
This is the new feature in SQL Server 2016.Stretch Database allows you to archive your historical data transparently and securely. In SQL Server 2016 Community Technology Preview 2 (CTP2), Stretch Database stores your historical data in the Microsoft Azure cloud. After you enable Stretch Database, it silently migrates your historical data to an Azure SQL Database. Few silent features as below:
- You don't have to change existing queries and client Apps. You continue to have seamless access to both local and remote data.
- Your local queries and database operations against current data typically run faster.
- You typically enjoy reduced cost and complexity.
This DB offers local server performance for hot data and cloud storage for old data without any change to the application. The basic use case is a table that contain a small amount of hot data that users normally care about and a large amount of old data that should have been moved to off-line archive but the users still able to query\access it.
Stretch Database Architecture
Stretch Database leverage's the resources in Microsoft Azure to offload archival data storage and query processing.When you enable Stretch Database on a database, it creates a secure linked server definition in the on-premises SQL Server. This linked server definition has the remote endpoint as the target. When you enable Stretch Database on a table in the database, it provisions remote resources and begins to migrate eligible data, if migration is enabled.
Queries against tables with Stretch Database enabled automatically run against both the local database and the remote endpoint. Stretch Database leverage's processing power in Azure to run queries against remote data by rewriting the query. You can see this rewriting as a "remote query" operator in the new query plan.
What kind of databases and tables are candidates for Stretch Database?
Stretch Database targets transactional databases with large amounts of historical data, typically stored in a small number of tables. These tables may contain more than a billion rows.In SQL Server 2016 Community Technology Preview 2 (CTP2), Stretch Database migrates entire tables. This assumes that you already move historical data into a table that's separate from current data.
Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor, to identify databases and tables for Stretch Database. For more info, For more details see Identify databases and tables for Stretch Database.
What happens when you enable Stretch Database?
After you enable Stretch Database for a local server instance, a database, and at least one table, it silently begins to migrate your historical data to an Azure SQL Database. You can pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth.Migration: Stretch Database ensures that no data is lost if a failure occurs during migration. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view provides the status of migration.
Querying: You don't have to change existing queries and client apps. You 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 that's archived remotely.
Refer MS article for more details Stretch Database.
No comments:
Post a Comment