<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Paul Ibison
»
High Availability (DR) Using SQL Server 2005...
17 posts, Page 1 of 2
1
2
»»
High Availability (DR) Using SQL Server 2005 Transactional Replication
Rate Topic
Display Mode
Topic Options
Author
Message
Paul Ibison
Paul Ibison
Posted Thursday, April 17, 2008 12:19 AM
SSC 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
izquierdote
izquierdote
Posted Thursday, April 17, 2008 2:04 AM
Forum 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
daytripper
daytripper
Posted Thursday, April 17, 2008 6:13 AM
Forum 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
wherewasi
wherewasi
Posted Thursday, April 17, 2008 6:41 AM
Forum 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
paul.ibison
paul.ibison
Posted Thursday, April 17, 2008 7:14 AM
Grasshopper
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
paul.ibison
paul.ibison
Posted Thursday, April 17, 2008 7:19 AM
Grasshopper
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
daytripper
daytripper
Posted Thursday, April 17, 2008 7:22 AM
Forum 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
paul.ibison
paul.ibison
Posted Thursday, April 17, 2008 7:23 AM
Grasshopper
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
James Luetkehoelter
James Luetkehoelter
Posted Thursday, April 17, 2008 9:12 AM
SSC 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
paul.ibison
paul.ibison
Posted Thursday, April 17, 2008 9:52 AM
Grasshopper
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 »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
