In recent years we have seen huge data explosion, as users usually want to capture most of their data for various reasons, such as some mentioned below:
- Regulatory compliance (for example, taxes)
- Auditing (for example, fraud investigation)
- Analysis and Forecasting (for example, comparing past results)
- Nature of business (for example, retailer transaction details history)
- Inability to decide what can be safely scrapped (for example, what might a government agency or major institutional investor ask for?)
Stretch database is a new feature introduced in SQL 2016 which does data archival of historical data from on-premises to Azure SQL DB (Microsoft PaaS Cloud) in a transparent and secure way with remote querying capability.
Most of the enterprises want their archived data to be stored at the same place and which can be accessed as and when needed without waiting for data to be restored or brought online. For this kind of scenarios only option is to keep all data on production DB. In earlier versions of SQL Server, this results in many critical issues such as:
- Maintenance operations such as re-indexing and others becomes difficult
- Restore becomes expensive
- Storage cost is more if storing on-premise due to infrastructure cost
- Full table can be stored - If historical data is in the separate table.
- Selective records can be migrated using filter predicate - If a table contains both historical and current data.
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.
You can pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth.
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.
Data loaded on an On-Premise Stretch-enabled table is moved to Azure SQL DB without taking any space in On-premise DB but on Azure SQL DB.
You can pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth.
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.
Data loaded on an On-Premise Stretch-enabled table is moved to Azure SQL DB without taking any space in On-premise DB but on Azure SQL DB.
Benefits of Stretch database:
- Cost-effective
Stretch database provide a cost effective way of storing data. Stretch warm and cold transaction data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database. Unlike typical cold data storage, your data is always online and available to query. You can provide longer data retention timelines without breaking the bank for large tables like Customer Order History. Benefit from the low cost of Azure rather than scaling expensive, on-premises storage. You choose the pricing tier and configure settings in the Azure Portal to maintain control over price and costs. Scale up or down as needed. - Doesn’t require changes to queries or applications
Access your SQL Server data seamlessly regardless of whether it’s on-premises or stretched to the cloud. You set the policy that determines where data is stored, and SQL Server handles the data movement in the background. The entire table is always online and queryable. And, Stretch Database doesn’t require any changes to existing queries or applications – the location of the data is completely transparent to the application. - Streamlines on-premises data maintenance
Reduce on-premises maintenance and storage for your data. Backups for the cloud portion of your data run automatically. Backups for your on-premises data run faster and finish within the maintenance window. Your on-premises storage needs are greatly reduced. Azure storage can be 80% less expensive than adding to on-premises SSD. - Keeps your data secure even during migration
Enjoy peace of mind as you stretch your most important applications securely to the cloud. SQL Server’s Always Encrypted provides encryption for your data in motion. Row Level Security (RLS) and other advanced SQL Server security features also work with Stretch Database to protect your data.
Limitations for Stretch-enabled tables
- Constraints: Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.
- DML operations: We can't UPDATE or DELETE rows that have been migrated, or rows that are eligible for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables. We can't INSERT rows into a Stretch-enabled table on a linked server.
- Indexes: We can't create an index for a view that includes Stretch-enabled tables. Filters on SQL Server indexes are not propagated to the remote table.
Limitations that currently prevent us from enabling Stretch for a table
Table properties
- Tables that have more than 1,023 columns or more than 998 indexes
- File-tables or tables that contain FILE-STREAM data
- Tables that are replicated, or that are actively using Change Tracking or Change Data Capture
- Memory-optimized tables
Data types
- text, ntext and image
- timestamp
- sql_variant
- XML
- CLR data types including geometry, geography, hierarchyID, and CLR user-defined types
Column types
- COLUMN_SET
- Computed columns
Constraints
- Default constraints and check constraints
- Foreign key constraints that reference the table. In a parent-child relationship (for example, Order and Order_Detail), you can enable Stretch for the child table (Order_Detail) but not for the parent table (Order).
Indexes
- Full-text indexes
- XML indexes
- Spatial indexes
- Indexed views that reference the table
Others
- Every time you run the wizard to enable Stretch for a database, the wizard creates a new Azure SQL Database server. By default, the number of servers that you can create per subscription is limited.
- By default, the wizard creates a SQL Database server with the Standard service tier and the S3 performance level and according to the Azure SQL Database Service Tiers and Performance Levels documentation, the S3 tier has a maximum size for the database of 250 GB. (The highest level, Premium/P3, gives 500 GB.) Oh, and the maximum size for a blob in storage is also 500GB.
The Stretch Database feature in SQL Server 2016 illustrates the scalability that we can achieve using cloud solutions in an easy and cost effective way. Even though we have many shortcomings but this feature is still growing and will provide companies with an alternative and effective way of storing huge amount of data
Comments
Post a Comment