Disaster recovery in multiple publishers environment

Hi,
I configured multiple publishers and one subscriber architecture on sql 2005
by having one publisher control the schema and indexes on the subscriber, and
the other publishers only append their data to the subscriber. I'm trying to
mimic a disaster recovery issue where one of the publishers fails (one
scenario is for the main publisher to fail, and one scenario is for one of
the other publishers to fail), but I'm having issue with restoring any of the
publishers (either type) and re-initializing the replication with the one
subscriber in a way that doesn't generate data conflicts and allow the
replication to continue.
Every time I try to do this, the subscriber reports of data conflicts
against the data that the same publisher has replicated already (this is
because the records have a primary uniqueidentifier key), and now tries to
replicate again, and therefore replication doesn't seem to keep pushing new
data to the subscriber.
How can approach either scenario without having to delete the failed
publisher data from the subscriber, and without allowing for duplicate data
(if I remove the primary key from the subscriber) before I re-initialize the
replication?
Thanks,
Gil
Can you outline exactly how the disaster recovery test is being done - do you
restore the publisher's publication database to the same or another server?
Are you synchronising the distribution database backups? Are you using the
same distribution database or does each publisher have its own one.
Thanks,
Paul Ibison
|||Have a look at the continue on data consistency error profile. This will
allow you to skip the pk violations.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gil Shabat" <GilShabat@.discussions.microsoft.com> wrote in message
news:96ABD1A2-F355-4A77-B44C-2B60EA410CDD@.microsoft.com...
> Hi,
> I configured multiple publishers and one subscriber architecture on sql
> 2005
> by having one publisher control the schema and indexes on the subscriber,
> and
> the other publishers only append their data to the subscriber. I'm trying
> to
> mimic a disaster recovery issue where one of the publishers fails (one
> scenario is for the main publisher to fail, and one scenario is for one of
> the other publishers to fail), but I'm having issue with restoring any of
> the
> publishers (either type) and re-initializing the replication with the one
> subscriber in a way that doesn't generate data conflicts and allow the
> replication to continue.
> Every time I try to do this, the subscriber reports of data conflicts
> against the data that the same publisher has replicated already (this is
> because the records have a primary uniqueidentifier key), and now tries to
> replicate again, and therefore replication doesn't seem to keep pushing
> new
> data to the subscriber.
> How can approach either scenario without having to delete the failed
> publisher data from the subscriber, and without allowing for duplicate
> data
> (if I remove the primary key from the subscriber) before I re-initialize
> the
> replication?
> Thanks,
> Gil
|||Thanks for your replies.
For now I'm using one server to backup, and then restore the database. I
haven't got to restore the distribution database, and this also exists on the
same server. After some experimenting, I found that if I delete the
subscription + publisher, then restore the database, run the following
command exec sp_removedbreplication @.dbname=’dbname’,@.type='both' to remove
records of the publication,
and re-create the publication + subscription, but configure the subscriber
not to initialize, I get to a point that the new data is replicated again
without too many issues on both scenarios. however, I'm not sure whether this
is the right approach to take. Can you please let me know your thoughts?
Also, on a related issue, I tested using the SQL 2005 Developer edition
changing the schema of the subscriber, by changing the main publisher, and
this seems to work just fine. Problem is that when I run same scripts against
a publisher that has the setting to 'Keep existing object unchanged', it
still tries to replicate the DDL (tried to add a column to an existing
replicated table), and therefore returns with error (something about the same
column name can't exist twice on the same table). In addition, if I update
one of the other publishers first I can clearly see that despite the 'Keep
existing object unchanged' setting it still replicates the changes.
I tried to follow the process above and this allowed me to get around this
issue (i.e. delete all publications other than main publication, make schema
changes to all including the main publication, so subscriber schema would
also be modified, then re-create the publications, and disable the
initialize), but this seems completely wrong to me. Any thoughts on that
Thanks,
Gil
"Gil Shabat" wrote:

> Hi,
> I configured multiple publishers and one subscriber architecture on sql 2005
> by having one publisher control the schema and indexes on the subscriber, and
> the other publishers only append their data to the subscriber. I'm trying to
> mimic a disaster recovery issue where one of the publishers fails (one
> scenario is for the main publisher to fail, and one scenario is for one of
> the other publishers to fail), but I'm having issue with restoring any of the
> publishers (either type) and re-initializing the replication with the one
> subscriber in a way that doesn't generate data conflicts and allow the
> replication to continue.
> Every time I try to do this, the subscriber reports of data conflicts
> against the data that the same publisher has replicated already (this is
> because the records have a primary uniqueidentifier key), and now tries to
> replicate again, and therefore replication doesn't seem to keep pushing new
> data to the subscriber.
> How can approach either scenario without having to delete the failed
> publisher data from the subscriber, and without allowing for duplicate data
> (if I remove the primary key from the subscriber) before I re-initialize the
> replication?
> Thanks,
> Gil
|||What you initially describe is known as a nosync initialization and this is
quite standard.
Am not too sure why the replication of ddl changes would cause a duplicate
column - can you elaborate. EG is the schema of the table on the subscriber
already different to the publisher before the change?
As a workaround which avoids a complete reinitialization, you could drop the
article, drop the subscription to the article, make the change then readd,
(see http://www.replicationanswers.com/AddColumn.asp) but this might not be
necessary depending on more info from the question above.
HTH,
Paul Ibison
|||Thanks for the info.
I basically tested by having two schema identical databases (publishers),
and an identical schema database subscriber. Any change to the schema (ALTER
TABLE ADD...) on either publisher caused an immediate replication of the DDL
to the subscriber regardless whether or not the 'Keep existing object
unchanged' setting was used or not (the main publisher used the 'Drop
existing object and create a new one' and the secondary publisher used the
'Keep existing object unchanged' setting). In our production environment I
envision running the same DDL code against all publishers, and having only
the main publisher replicate those schema changes to the subscriber.
Unfortunately, when I tried to run a simple add column statement, the first
one to update its ddl statements have also replicated its changes to the
subscriber, while the 2nd one tried to do the same, but return an error that
the same column name cannot exist multiple times. The following is the exact
message: "Column name 'additional_Sync' in table 'dbo.Primary1' is specified
more than once"
To create the environment, I created two separate databases, and I ran
against them the same DDL to create one table. Then I created the main
publisher and subscription, which created the environment on the 3rd database
(subscriber), and finally, I created a new publication for the secondary
publisher, but used the 'Keep existing object unchanged' setting, so schema
wouldn't be re-created. This resulted in having data from the 2 publishers on
the subscriber. At this point I tried to test with adding a new column to the
table I created on the publishers.
I would appreciate any thought you might have.
Thanks,
Gil
|||Please can you test something for me with your setup. I believe that in SQL
2000 I set this up differently. One publisher/subscriber was set up as per
usual. The other one was set up with the "keep the existing table unchanged"
option. Data was sent from the second publisher but there was no DROP TABLE
statement there. If the data is partitioned this is fine. The thing is that
after setting it up this way, using sp_repladdcolumn, the column was only
propagated from the first publisher and not the second. This is where I
believe the difference might lie - you are using ALTER TABLE instead, and you
have the "keep existing..." setting on each publisher's articles. Please try
setting it up my way (not yet tried in SQL 2005) and see if this works for
you...
HTH,
Paul Ibison
|||I believe that in SQL 2005 MS changed the option from "keep the existing
table unchanged" to 'Keep existing object unchanged', but both do exactly the
same thing
I tried already yesterday to disable the replication schema updates by
executing: exec sp_changepublication @.publication ='Publication
Name',@.property ='replicate_ddl', @.value=0
and then executed the alter table command, which wasn't included in the
replication. then I added the column to replication by executing the
sp_repladdcolumn on both publishers, and the column has been added, and again
like before, both DDL commands were sent to subscriber, and I ended up with
the same error
Gil