Prev: Peer2Peer. Dist to Sub 'Query timeout expired'
Next: Credentials for Mirroring
From: Ken on 16 Feb 2010 23:59
I have 2 servers (server A and Server B), both with one database that
contains two tables (we'll call them MainTable and StageTable). These tables
are partitoned using the same partiton schema (1 partition per month for the
last 2 years using range left = 25 file groups and 25 data files). I can
set up the peer to peer replication successfully, but cannot replicate a
partition switch, nor partition schema changes.
I am using a sliding window archiving solution to age old data out of
MainTable. I switch out the earliest partiton into the stage table, then I
archive it off to another database. When I do my switch I am using the
statement...
ALTER TABLE [dbo].[MainTable] SWITCH PARTITION 1 TO [dbo].StageTable
PARTITION 1
I then run...
ALTER PARTITION FUNCTION [PFMyPartFunction]() MERGE RANGE(@MINDATE)
@MINDATE is the boundary value for the earliest (first) partition.
I then run th below statement to split the last(empty) partition to get
ready for the next month's data.
ALTER PARTITION FUNCTION [PFMyPartFunction]() SPLIT RANGE @MAXDATE_NEXT)
@MAXDATE_NEXT is the boundary value for the last partition plus one month.
I have ensured that the @allow_partition_switch and
@replicate_partition_switch proprties for the publication are set to true
when I create the publication.
I have read every article I have been able to find, and I don't understand
why this is not working.
I want to be able to do the sliding window archive process on either server,
but 90% of the time it will be done on server A, and have the data and
partiton function changes replicate to server B.
What am I missing?
 | 
Pages: 1
Prev: Peer2Peer. Dist to Sub 'Query timeout expired'
Next: Credentials for Mirroring