A customer wants to implement table partitioning on a replicated table.
They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.
So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).
What are his architectural or practical solutions to using table partitioning and replication?
thx
I'm sorry but i did not understand your question exactly.
Did you mean something like : "What are the benefits of using Table Partitioning with Replication?" ?
Ekrem ?nsoy
MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE
|||BOL says that SWITCH PARTITION cannot be used on a replicated table.
So if my customer wants to implement a partition function/scheme and switch a partition out on a monthly basis, it appears that this cannot be done.
What other options would they have to roll data on and off their table on a monthly basis (other than SELECT INTO the archive table followed by a DELETE on the original table)?
thx
|||That is the only option. SWITCH is not supported on replicated tables. The reason for this is very straightforward. A SWITCH operation is a metadata only operation. These do not get picked up by replication and so can not be applied to the subscriber. Since it can not be applied to a subscriber, allowing this operation would create a subscriber which is out of synch with the publisher with no way of putting it back in synch. So, if it is replicated, you have to use either an insert or a select into in order to move the data to the other table and then come back and delete it off the source table thereby allowing it to flow through replication.
No comments:
Post a Comment