Support Partition-Switching for Microsoft SQL Server Endpoints.
The ability to use table partitioning provides faster speeds loading data. Partition Switching improves the performance in archiving very fast. Old data can be switched out to separate tables or purge if necessary. For very large databases, especially OLTP, having the ability to partition-switch provides a fast method of moving table data or purging it with minimal resources required. Most definitely, much more efficient than performing delete ranges or truncating.
Currently, Qlik Replicate User guide has documented the following limitation:
"Partition switching is not supported."
However, by modifying the publication (with the changes listed at the bottom) Attunity replicate will still perform the INSERT/UPDATE/DELETES whether the Attunity task is turned off or in change replicate (using tlog replication). Attunity does perform the replication tasks but provides the following error during partition-switching:
Stream component 'st_0_TaskName' terminated
Stream component failed at subtask 0, component st_0_TaskName
Error executing source loop
Endpoint is disconnected
Encountered an unexpeceted error
... and here we can get an idea why the error was triggered:
Failed in applying table mapper catch up.
mssql_table_mapper_catch_up(...) failed in performing CATCH_UP statement 'select s.partition_id,s.object_id from sys.system_internals_partitions s , sysobjects o where s.object_id= o.id and o.xtype=N'U' and s.is_data_row_format=1 and s.object_id in (1841493689 )'
-- modifying the publication to allow partition switching in transactional replication
EXEC sys.sp_changepublication
@publication = @publication,
@property = 'allow_partition_switch',
@value = 'true';
And
EXEC sys.sp_changepublication
@publication = @publication,
@property = 'replicate_partition_switch',
@value = 'false';
Additional benefits:
1) We archive our replicated databases (at publisher/distributor) having different retentions - improving performance and recovery.
2) Ability to reload subscribers from specific partitions
3) Ability to purge at the source (OLTP) - let's say 90 days, and still retain entire history at the landing/replicated database.
4) Ability to replicate any INSERT/UPDATE/DELETES until records have been partition-switched (90 day retention for example).
5) High performance, minimal-locking, and minimal resources utilized in archiving/deleting large number of records.