Replicating a partitioned table to a secondary server

Tags:
Merge replication
SQL Server replication
SQL Server tables
Table partitioning
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

if you only want merge it,I do suggest you using Partition Wizard to do it,because two days ago,I used it to merge my windows7 partitions,and found it is easy-to-use

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SQL Server Ask the Experts
    [...] Replicating a partitioned table to a secondary server asked by a SQL Server reader and answered by [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following