 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Replication / February 2007General SQL Server 2005 transactional replication question
I want to implement transactional replication for failover purposes. I need to replicate data from a primary database to a secondary one that will be used if the primary goes down. In the event of a primary failure I need to switch the applications to run off the secondary. When the primary is back up I then need to update it with all the changes made to the secondary while the primary was down, switch the replication direction, and switch the applications back to the primary again. I'd like some advise on how best to set this up. For instance: 1) How do I go about delivering secondary updates back to the repaired primary? 2) Should I use push or pull subscription? 3) The database uses a number of triggers. How should I ensure that replication works correctly for these - is it just a matter of using the NOT FOR REPLICATION option? I've read a couple of posts indicating that this may not work properly in the case of updateable subscriptions. 4)Does peer-to-peer replication offer any advantages over the traditional publication-subscription in this scenario? Any assistance much appreciated. Apologies for the scope of the question, but I'm new to sql server replication and would like some expert advice to get me started. I would consider using queued updating subscribers for this purpose. You can have identity columns taken care of and fail back is relatively simple - just run the queue reader agent. All triggers shoulld be ser to NFR and the triggered changes replicated as per normal. Peer-to-peer wouldn't enable change management (schema changes) so easily so I'd still use queued updating subscribers instead. Only extra consideration is BLOB datatypes which would prohibit this method. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com I would use bi-directional transaction replication with push subscribers. With the NFR property on your triggers it will work correctly. Peer-to-peer does offer the feature where you can replicate ddl. With pure bi-directional replication you will have to drop the publications before making schema changes. I would not recommend using queued, it is best used when the majority of the DML originates on the publisher.  SignatureHilary 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 >I want to implement transactional replication for failover purposes. I need > to replicate data from a primary database to a secondary one that will be [quoted text clipped - 22 lines] > but I'm new to sql server replication and would like some expert advice to > get me started. Thanks for your replies. 1) If the primary is down and I'm now running off the secondary (subscriber), presumably all my changes to that database are relayed to the distribution database. If the primary is still down do they just accumulate in the distribution database until it comes back up? At that point are they then automatically delivered to the primary, or is there some manual process involved? What difference does usingnot using queued make in this process? 2) For the setup described, do I need to configure both servers as distributors? I don't have an additional standalone windows server available to put a distribution database on. 3) The posts I referred to were discussing how NFR didn't work properly when using updating subscribers - ie: when inserting a row into the subscriber database, apparently two rows (one from replication, one from trigger firing) were inserted into the primary. It sounds like from your answers that this is not the case? Many thanks. > I would use bi-directional transaction replication with push subscribers. > With the NFR property on your triggers it will work correctly. Peer-to-peer [quoted text clipped - 31 lines] > > but I'm new to sql server replication and would like some expert advice to > > get me started. Kev, your questions really apply to Hilary's recommendation (my answer applied to queued updating subscribers which I prefer n this scenario) so I'll leave this one to him. Rgds, Paul Ibison Hi Paul, Could you tell me your reasons for preferring queued updating subscriptions? Regards, Kev. > Kev, > your questions really apply to Hilary's recommendation (my answer applied to > queued updating subscribers which I prefer n this scenario) so I'll leave > this one to him. > Rgds, > Paul Ibison It's out of the box, deals with schema changes, handles identity ranges automatically and you can easily find what is waiting at the subscriber to be sent back to the publisher. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Paul, Is it mandatory that you need primary key constraints on all replicated tables in queued updating subscribers? Is a unique index good enough? Also, on which server would you place the various agents - queue reader, distributor etc? Thanks, Kev. > It's out of the box, deals with schema changes, handles identity ranges > automatically and you can easily find what is waiting at the subscriber to > be sent back to the publisher. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com Kev, yes - PK is mandatory. Queue reader will be on subscriber. Distribution agent depends on push or pull and pros and cons of each approach. To offload some processing usage onto the subscriber I'd use pull as this server is unlikely to be used until failover. Also need to set automatic identity range management on each relevant article. Main caveat is for BLOBS - you won't be able to replicate back from the subscriber to the publisher. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Immediate handles identity ranges transparently, with queued you have the option to use automatic identity ranges which if not well designed can be highly problematic. SignatureHilary 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 > It's out of the box, deals with schema changes, handles identity ranges > automatically and you can easily find what is waiting at the subscriber to > be sent back to the publisher. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com The thing is that Kev wants to be able to make changes to the subscriber while the publisher is being repaired, so immediate updating wouldn't work. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Yikes, I just realized that with sql 2005 queued has automatic identity range management enabled by default. Sorry about that. SignatureHilary 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 > The thing is that Kev wants to be able to make changes to the subscriber > while the publisher is being repaired, so immediate updating wouldn't > work. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com answers inline. SignatureHilary 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 > Thanks for your replies. > [quoted text clipped - 8 lines] > process > involved? What difference does usingnot using queued make in this process? Exactly, when the primary is down, they will be queued in the distribution database on the subscriber. With queued updating triggers are involved and it will not be as scalable as with bi-directional transactional replication. > 2) For the setup described, do I need to configure both servers as > distributors? I don't have an additional standalone windows server > available > to put a distribution database on. Yes, you don't need a standalone distributor but it is recommended if your publisher(s) is under high load. > 3) The posts I referred to were discussing how NFR didn't work properly > when [quoted text clipped - 4 lines] > is > not the case? Yes, with NFR it is not the case. Can you post links to the posts you are referrring to here? > Many thanks. > [quoted text clipped - 46 lines] >> > to >> > get me started. Hilary, In addition to my earlier reply to your reply: Does bidirectional replication necessarily involve a lot of scripting - ie: enabling each article for replication in each database? Or is there a short cut via wizards or higher level constructs? Is it possible to add additional read-only subscriptions? Thanks, Kev. > I would use bi-directional transaction replication with push subscribers. > With the NFR property on your triggers it will work correctly. Peer-to-peer [quoted text clipped - 31 lines] > > but I'm new to sql server replication and would like some expert advice to > > get me started. There is a lot involved. 1) create the publication on the publisher and create the subscription, script it out and add the loopback_detection=true parameter to the sp_addsnapshot proc. 2) drop the publication but save the modified script 3) backup the database on the publisher and apply it on the subscriber (or initialize subscription from backup if using sql 2005) 4) make the seed 1 and the increment two on all the identity columns on the publisher, make the seed 0 and the increment 2 on all the identity columns on the subscriber. 5) change all identity columns, triggers, and constraints to NFR. 6) run dbcc checkident to make sure the next value to be assigned on the publisher is odd and on the subscriber is even. 7) generate the replication procs, edit them to make sure the identity column is never updated. 8) Create your publication and subscription using a no-sync subscription, edit the script for the subscriber and run it on the subscriber. SignatureHilary 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 > Hilary, > [quoted text clipped - 57 lines] >> > to >> > get me started. It looks like from the info you've both provided that I may not be able to use transactional replication after all. The main problem is that the database makes little use of primary key constraints (just uses unique indexes) and I would have to add them to all replicated tables which is probably a no goer from a retesting standpoint at present, although I think it will definitely need to be scheduled later on. With this issue in mind: Is there anything else I can use or any "workaround" that you know of? Presumably peer-to-peer and bi-directional replication would require the same primary key changes? From what I've read it seems to be the transactional publication part that requires the primary key constraints rather than the subscription side per se, so this would rule out things like using transactional replication to the secondary and then using log shipping back to the primary. The system has a large number of real-time updates and inserts, so I think that snapshot and merge replication are out for starters. Log shipping would be a fall back position. Thanks, Kev. > There is a lot involved. > [quoted text clipped - 75 lines] > >> > to > >> > get me started. A unique index is like a primary key with the exception that it tolerates a single null and you can have multiple unique indexes on a table. I would evaluate whether you can change your unique indexes into real PKs. SignatureHilary 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 > It looks like from the info you've both provided that I may not be able to > use transactional replication after all. The main problem is that the [quoted text clipped - 111 lines] >> >> > to >> >> > get me started. Hilary, It's certainly something I can discuss but I have a feeling it'll get blocked. Did you have any other thoughts on my last post? Thanks, Kev. > A unique index is like a primary key with the exception that it tolerates a > single null and you can have multiple unique indexes on a table. [quoted text clipped - 116 lines] > >> >> > to > >> >> > get me started. Log shipping has the problems of not really being scalable for large databases (although I know an online bank who uses it extensively and is happy with it), and having a larger exposure to data loss than other options. SignatureHilary 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 > Hilary, > [quoted text clipped - 147 lines] >> >> >> > to >> >> >> > get me started. In that case I'd go for log shipping. Once the primary server is back up and going, you could restore the database from the standby server or use DataCompare to sync up the databases. Higher latency than replication but it looks like you have little choice. SQL Server 2005 will offer mirroring which you can use as a lower latency version of log shipping. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Paul, Hilary, Thanks very much for all the assistance. I'll probably go for log shipping in the short term. Regards, Kev. > In that case I'd go for log shipping. Once the primary server is back up and > going, you could restore the database from the standby server or use [quoted text clipped - 3 lines] > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.