I have a rather large table of approximately 3 billion records. The table is partitioned into 24 monthly file groups, each with its own data file. I use a sliding window archiving solution to switch out data to our staging table, where it is then loaded onto an archiving database. The switch out is done using the SWITCH PARTITION statement.
However, I now have to replicate this table to a secondary server. I thought of using merge replication because data will need to be edited on both servers and replicated to the other server.
1. Is using the SWITCH PARTITION statement the best way to get old data out of a partitioned table?
2. How can I use merge replication with a partitioned table?
Some documentation I have read states, "Transactional and merge replication do not support the following commands: ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or the REBUILD WITH PARTITION statement of ALTER INDEX. Merge replication does not support the SWITCH PARTITION command of ALTER TABLE, but transactional replication does."
In conclusion, if you are replicating a large partitioned table to a secondary server and both need to be update-able, how would you go about it?