Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

High Availability (DR) Using SQL Server 2005 Transactional Replication Expand / Collapse
Author
Message
Posted Thursday, April 17, 2008 12:19 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32
Comments posted to this topic are about the item High Availability (DR) Using SQL Server 2005 Transactional Replication


Paul Ibison
Paul.Ibison@replicationanswers.com
Post #486136
Posted Thursday, April 17, 2008 2:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 24, 2009 10:13 AM
Points: 2, Visits: 14
How about the fields "image"?
Why doesn't it work?

I had problems with this and I had to erase the tables with fields of this type from the publication.
Post #486189
Posted Thursday, April 17, 2008 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:43 PM
Points: 3, Visits: 68
Hi Paul

Am new to sql replication and it sounds like replication was never meant as a DR (failover) method? I was surprised to learn that you can only replicate a set of filtered objects (not replicate the entire database).

So replication was really intended for a "subset" of data within a database" correct?

Thanks
John
Post #486325
Posted Thursday, April 17, 2008 6:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2008 6:58 AM
Points: 3, Visits: 12
Hi Paul,

Great article by the way...

I still exist in the SQL 2000 world. I run transactional replication. The way we're set up, we use the stored procedures for our application to maintain teh data at the subscriber. As you might imagine, as long as the application runs perfectly, there are no problems. But once you introduce an occasional deadlock, or an error on an insert etc, things can get out of sync.

I have been testing the use of the sp_msins, sp_msdel, sp_msupd schema that can be created when setting up replication. These will eliminate the possibility of errors/deadlocks in the application causing inconsistency.

My issue is performance.... and my test-side hardware etc is never going to give me a good comparison. Are there performance issues with the setup I'm trying? Just want to get some comfortability before I move to production. Any assistance/info is appreciated.

Cheers!

Tom



Post #486360
Posted Thursday, April 17, 2008 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:26 AM
Points: 14, Visits: 144
Ok - there are various considerations for BLOBS eg updates have to be done in a transaction and the textptr retreived in the same transaction and max text repl size set appropriately. Please look here for more info:
http://technet.microsoft.com/en-us/library/ms151254.aspx
HTH,
Paul Ibison
Post #486395
Posted Thursday, April 17, 2008 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:26 AM
Points: 14, Visits: 144
Transactional replication is considered by MS as a DR solution and is part of the High Availability set of Technet articles. Really there isn't really a one-size-fits-all here in the DR arena. There are pros and cons of each technology. For example, TR doesn't deal with database metadata and permissions - at least not in a straightforward way (sp_addscriptexec) but it does have some advantages over mirroring and log-shipping in that the Standby server can be additionally used for reporting.
Rgds,
Paul Ibison
Post #486400
Posted Thursday, April 17, 2008 7:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:43 PM
Points: 3, Visits: 68
Hi Paul

Thanks for clearing that up , were using mirroring for DR right now, but i see what you mean "one size does not fill all". Thank you for your response.

John
Post #486404
Posted Thursday, April 17, 2008 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:26 AM
Points: 14, Visits: 144
Message for WherewasI: to be honest I'm not too sure of your configuration. Deadlocks on the publisher won't bother the subscriber as only committed transactions are replicated. Perhaps the deadlocks are on the subscriber? This shouldn't be the case as the subscriber is to be treated as read only.
However if there is some sort of contention issue onthe subscriber then if necessary you can use dirty reads (NOLOCK) to minimise concurrency issues or use snapshot_committed isolation level - both on the subscriber.
HTH,
Paul Ibison
Post #486407
Posted Thursday, April 17, 2008 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 06, 2011 8:20 PM
Points: 227, Visits: 33
A great article, but...

I fundamentally disagree with using replication as a DR technique. Even with 2005, I've run into too many clients that often use replication to keep a standby database but find it difficult in the event of a failure (usually because they aren't prepared) to determine the transactional status of the database - is it current up to what point?

I know that's controversial, but, that's my take. It's also a very "busy" technology that has lots of spots of other failure (usually through misconfiguration). My preference is either low-tech log-shipping, or database mirroring.

Still, an excellent article! I'm just playing devil's advocate.



Post #486538
Posted Thursday, April 17, 2008 9:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:26 AM
Points: 14, Visits: 144
To be honest I'm not promoting the case for transactional replication as the best DR solution for all cases, so I don't necessarily disagree with you :). At my current shop we actually primarily use database mirroring for DR (esp for sharepoint databases). However if the DBA has sufficient skills and time, a TR setup can offer a DR solution with reporting capabilities, which gives added benefit so I can see the attraction.
Rgds,
Paul Ibison
Post #486576
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse