Tricky implementation of Transnational Replication and Table Partitioning in SQL Server 2012
Recently I came across a requirement where in data was supposed to be retained on Production server for one year but for reporting purpose two years of data was needed.
After evaluating various options, transnational replication was set up. The challenge was how to keep the data on publisher and subscriber with different retention period. If delete commands are not replicated, it might cause data consistency issues and would defeat the purpose of setting up replication. If data older then 1 year on subscriber is moved to another table - it will require all reports to be changed to get data from two tables and thus was not acceptable.
Since SQL Server edition was Enterprise so started exploring if table partitioning could be of help. Initially it seems like panacea but hit another roadblock on two counts:
- How to switch the partition on publisher without affecting the data on subscriber?
- Define a strategy to reinitialize the replication, if needed.
After doing few tests, I felt like yes, I can make it to work.
How to switch the partition on publisher without affecting the data on subscriber?
I created a table named table_12 on publisher and partitioned the same. Also created one more table named table_13 to switch the data from table_12 and set up transnational replication for table_12. Attempt to switch the partition was failed with following error.
As documented on MSDN, SWITCH PARTITION operations are blocked on tables which are enabled for replication but there are ways to overcome said restriction.
Enabled partition switch for concerned publication using below T-SQL and then tried switching data from table_12 partition 1 to table_13
sp_changepublication @publication='Test_pub10', @property='allow_partition_switch',@value='TRUE'
ALTER TABLE table_12 SWITCH PARTITION 1 to Table_13 - I had created table_13 on publisher and subscriber and after executing this statement data was switched from both publisher and subscriber, this is not what I was looking for.
Not to replicate partition switch on subscriber, executed below script on publisher and repeated the test to move the data from partition 2 after truncating table_13 table on both servers.
sp_changepublication @publication='Test_pub12', @property='replicate_partition_switch',@value='FALSE'
ALTER TABLE table_12 SWITCH PARTITION 2 to Table_13 - It worked like charm !! Now I do see what exactly was needed and we are good to go.
Define a strategy to reinitialize the replication, if needed.
It was the time for looking at another roadblock - I wanted to define and document the process for reinitialize the replication as you know replication breaks some time.
Can we initialize subscriber from backup? Answer is No. Can we just reinitialize the replication by generating the snapshot and applying same on subscriber ? It's no again. Why ? Because either option might cause data older than one year to be lost on subscriber.
Is there a way to get it done somehow ? Yes, it can be done.
Let's say there are 12 partitions on publisher and 16 partitions on subscriber and each partition stores the data for one month. You need to switch four partitions to some new partitioned table, then reinitialize the subscriber with new snapshot. Once done move those 4 partitions back to original table and announce the status to concerned stakeholders if post checks don't find any issues.
Hope you enjoyed the post !