Skip to main content

Suggest an Idea

Vote for your favorite Qlik product ideas and add your own suggestions.

Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Qlik Replicate Support Partition-Switching for Microsoft SQL Server Endpoints

HaroldV
Contributor
Contributor

Qlik Replicate Support Partition-Switching for Microsoft SQL Server Endpoints

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.

Tags (2)
4 Comments
HaroldV
Contributor
Contributor

Can these features be added for the interim: Option(s) for "Internal Parameters" to "allow" partition-Switching by suppressing the error messages associated with a Microsoft SQL Endpoint when executing a Partition-Switch DDL statement on a table being replicated by Attunity.

Shelley_Brennan
Former Employee
Former Employee

Thank you for the detailed post - we would like to collect feedback from others and will consider your suggestion for a future release.

Status changed to: Open - Collecting Feedback
Meghann_MacDonald

From now on, please track this idea from the Ideation portal. 

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Ideation
Explorer II
Explorer II
 
Status changed to: Closed - Archived