Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 85 of 212

73 C H A P T E R 5 | Broader data access Stretch Database Table partitioning Custom cleanup script Stretch Database If your primary requirement for historical data is to enable queries for recent data changes, but you occasionally need to provide access to older data, then you should consider transparently migrating historical data to SQL Database by using the Stretch Database feature that we introduce in Chapter 4, "Improved database engine." If you are using temporal tables strictly for auditing, you can stretch the entire history table and use the Stretch Wizard to set up Stretch Database. Otherwise, you can use an inline predicate function based on the value of the end-period column when you run a CREATE TABLE or ALTER TABLE statement to enable Stretch Database on your history table and migrate a subset of rows to SQL Database, as shown in Example 5-8. Note The use of Stretch Database has no effect on how the database engine captures data changes or how it returns results for temporal queries from the history table. Although this is a good option for the history table, you cannot enable Stretch Database on a current table. Example 5-8: Enabling Stretch Database for a history table -- Create inline predicate function to filter historical data CREATE FUNCTION dbo.fn_StretchBySystemEndTime(@systemEndTime datetime2) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS is_eligible WHERE @systemEndTime < CONVERT(datetime2, '2016-01-01T00:00:00', 101) ; -- Create temporal table ALTER TABLE [Production].[ProductInventory_TemporalHistory] SET ( REMOTE_DATA_ARCHIVE = ON (FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime (ValidTo) , MIGRATION_STATE = OUTBOUND) ); Note If you prefer to use a sliding window and dynamically adjust the filter condition, create a scheduled script that uses the ALTER TABLE statement to disable Stretch Database temporarily, the ALTER FUNCTION statement to change the date used for filtering for the inline predicate function, and the ALTER TABLE statement to enable Stretch Database again. You can see an example of this script at "Manage Retention of Historical Data in System-Versioned Temporal Tables," Table partitioning If you are using Enterprise Edition, another option for managing the history table is to use table partitioning with a sliding window to easily move data out of the table when the data-retention period expires for a subset of data. You can keep system versioning on as you switch out the historical data and thereby avoid interruptions to normal workloads. Before you can use partition switching, the history table must have a clustered index on the end-period column.

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016