The Company I work for has a set of databases (largest just below 1Tb) on different servers – 2 in the US, 2 in Europe.
We run full peer-to-peer replication per database between the 4 nodes – so they can all take transactions (Insert/Update/Delete) and all have the data that the other nodes have gathered (within a variable latency – worst connection is on average about 30-40 secs).
The largest database carries data from early 2008 to today. All of this data is further replicated to Reporting nodes which hold ALL data.
I need to remove data at the Transactional nodes, up to 2013, to remove the Drive space deficit at the Transactional nodes, and therefore historic data will only be available at the Reporting nodes.
What is the best way to do this? Data is relatively manageable in that it is well partitioned (monthly – by partition, and then Yearly into separate Files/Filegroups). However, there is the problem of not being able to drop the Partitions while they are involved in replication and reading up on Partition switching – this is not allowed with that either. (Switching Partitions Prerequisites – Point 18)
As a full Production Environment I am trying to avoid anything that will affect replication – including resynchronisation (lot of data to resync, over large distances).
Does anyone have any good suggestions for how to perform this task?