| Groups | Blog | Home |
sql server replication : Fail over scenario under Transaction replication
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
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)
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 [quoted text, click to view] "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 [quoted text, click to view] "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. [quoted text, click to view] "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%3D320773 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. [quoted text, click to view] "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%3D320773 > 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. [quoted text, click to view] "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) >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |