SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


High Availability (DR) Using SQL Server 2005 Transactional Replication


High Availability (DR) Using SQL Server 2005 Transactional Replication

Author
Message
Paul Ibison
Paul Ibison
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 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
izquierdote
izquierdote
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.
daytripper
daytripper
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 73
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
wherewasi
wherewasi
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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



paul.ibison
paul.ibison
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 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
paul.ibison
paul.ibison
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 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
daytripper
daytripper
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 73
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
paul.ibison
paul.ibison
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 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
James Luetkehoelter
James Luetkehoelter
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 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.



paul.ibison
paul.ibison
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 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 Smile. 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search