Fail over scenario under Transaction replication

I get 2 questions which need quick answer:
Setup replication between Srv1 DB A (Distributor and publisher) with push
publication to the subscriber Srv2 DB AB
First qestion: many table get identity value set to YES on publisher, these
values are lost in subscriber, but i need to get identical Design of table in
both DB, as if Srv 1 fail, we will use DB AB from Srv 2 to replace the one in
publisher side.
How get automatically the identity setup to YES on the subscriber site?
i see many post but no one with concrete explanation, also about sp4?
Second question: as Publisher fail, we will restore the Database from
Subscriber to Publisher.
But if i do so, the replication between the Publisher and subscriber fails
(with some counter msg). Only way is to close publication and create it again.
We want to avoid any manual intervention possible as the customer will just
restore the DB and replication should continue as normal with subscriber.
Any recommandation on the second questions, i see some stored procedure? to
reset the counter. Please more precise solutions.
Mostly if the DB is restore and if you make any modification on the
Publisher side, they are not forwarded to the subscriber.
Waiting your feedback with a major welcome as our customer will used Dual
Sql server 2k. A reply direct to my email will be appreciate.
1) Your best bet is to script out your schema and then push this to the
subscriber using the pre snapshot command. Then for your articles
defaults/table articles select the snapshot tab, and select delete all the
data in the existing table.
2) when you restore your database, recreate your publication and do a
no-sync subscription.
Hilary Cotter
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
"Martial" <Martial@.discussions.microsoft.com> wrote in message
news:62916144-CA72-49F1-AC09-4003E0D70ABC@.microsoft.com...
> I get 2 questions which need quick answer:
> Setup replication between Srv1 DB A (Distributor and publisher) with push
> publication to the subscriber Srv2 DB AB
> First qestion: many table get identity value set to YES on publisher,
these
> values are lost in subscriber, but i need to get identical Design of table
in
> both DB, as if Srv 1 fail, we will use DB AB from Srv 2 to replace the one
in
> publisher side.
> How get automatically the identity setup to YES on the subscriber site?
> i see many post but no one with concrete explanation, also about sp4?
> Second question: as Publisher fail, we will restore the Database from
> Subscriber to Publisher.
> But if i do so, the replication between the Publisher and subscriber fails
> (with some counter msg). Only way is to close publication and create it
again.
> We want to avoid any manual intervention possible as the customer will
just
> restore the DB and replication should continue as normal with subscriber.
> Any recommandation on the second questions, i see some stored procedure?
to
> reset the counter. Please more precise solutions.
> Mostly if the DB is restore and if you make any modification on the
> Publisher side, they are not forwarded to the subscriber.
> Waiting your feedback with a major welcome as our customer will used Dual
> Sql server 2k. A reply direct to my email will be appreciate.
|||Hi Paul,
I try this method, now i get my structure table kept in place, but i can not
edit on the table of the Subscriber.
Example.
DB1
"Paul Ibison" wrote:

> Have a look at queued updating subscribers. This will
> solve the identity problem and will allow failback
> without having to set up everything again.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi Paul.
I try that, the structure table is identical to original one.
But it's a little worse, cause in case of fail of primary server, can not
modify or
delete or add new item to table.
I used our application and directly to SQL server, i get the msg login
failed for sa, check my Sa password on Subscriber it's fine.
I believe maybe related to MSDTC startup.
Please Help!!!
If i can modifiy on subscriber and add, i solved all my pb.
"Paul Ibison" wrote:

> Have a look at queued updating subscribers. This will
> solve the identity problem and will allow failback
> without having to set up everything again.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It looks like you have set it up as immediate updating
with queued failover. What I was suggesting is just a
queue. Have a look at this article for the source of the
error: http://support.microsoft.com/default.aspx?scid=%
2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D32 0773
However, I think that you'll have to reinitialize because
on failover if the publisher is not available, your
triggers will prevent any changes being made as they'll
error.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thank you for your feedback, i foudn this article earlier and fix my problem.
But u are right, as tested with my customer if publisher server fail, no
changes are possible on the subscriber which not answer the customer need of
failover system.
So i hope Transaction replication will do or i need to change to Merge?
Or as u mention should i will try the queue updating (subcription) and at
the publication level should i choose both (queue and immediate)?
I get an additional question about the fail over process: the subscriber SQL
DB should time to time be used to restore older database for application
purpose.
My idea is follow:
1. stop all agents on publisher sql server, which i believe should stop
replication to occurs between both SQL.
2. Backup database on subscriber (full one of course)
3. restore old DB for application purpose.
4. restore backup of point 2, once needed.
5. restart agent.
I hope this procedure will continue the replication where it stop, can you
confirm my idea?
I really appreciate your prompt feedback on my question, you are Master of
Replication in point of view.
My last question is the growing size of DB on both SQL, how to ensure i will
not get XXGb of DB.
"Paul Ibison" wrote:

> It looks like you have set it up as immediate updating
> with queued failover. What I was suggesting is just a
> queue. Have a look at this article for the source of the
> error: http://support.microsoft.com/default.aspx?scid=%
> 2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D32 0773
> However, I think that you'll have to reinitialize because
> on failover if the publisher is not available, your
> triggers will prevent any changes being made as they'll
> error.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Just queued on its own would meet your requirements.
Your backup plan looks sound. Also consider the timeframe
involved. You might need to change the transaction
retention period and history retention period and the
subscription expiration time as appropriate if
the 'downtime' lasts any length of time. In the worst
case if you don't do this, commands will be removed from
the distribution database before being propagated to the
subscriber and you'll be left having to reinitialize.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
It works like a dream, thanks for all.
Now i will make some more testing in our office to check HOW to setup
retention period, clean up transaction period, etc...
You have been my 'seifu' like we said in Asia. for master.
"Paul Ibison" wrote:

> Just queued on its own would meet your requirements.
> Your backup plan looks sound. Also consider the timeframe
> involved. You might need to change the transaction
> retention period and history retention period and the
> subscription expiration time as appropriate if
> the 'downtime' lasts any length of time. In the worst
> case if you don't do this, commands will be removed from
> the distribution database before being propagated to the
> subscriber and you'll be left having to reinitialize.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>