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
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,

Generally agree with ur comment about deadlocks except....

when u have tables with identity columns which are also referenced in other tables... if insert fails, publisher identity coumn gets incremented, but subscriber does not. because other tables need that identity as a reference... u got a problem.

that's why i was looking at the s/p's that can be created by replication setup (in conjunction with changing identity columns to 'NFR on both publisher and subscriber....


Tom (wherewasi)Hehe



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

Group: General Forum Members
Points: 114 Visits: 144
But in SQL Server 2005 the columns are NFR on the subscriber automatically so if the publisher identity range goes 1,2,4,5 then the subscriber also goes 1,2,4,5 ; ie the identity property is never used on the subscriber until failover.
Rgds,
Paul Ibison
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
I hear ya, Paul. We are just now starting to migrate to SQL Server 2005. Your article was valuable in fo to me...



Roddy.CAMERON
Roddy.CAMERON
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 2607
Coming from a Sybase background we always initialise using backups of the primary, never on a table by table basis. I'm surprised so few people know of, or use this feature with MS. It removes 95% of the pain when you want an identical copy of your primary db at the replicate site, or even if you are only replicating a subset of tables. It normally will not matter that the replicate contains the tables you are not replicating, if any. In SQL2005 it's also easier to use this feature than in 2000. It's actually really the only practical method once you start replicating databases which are hundreds of GB in size.

One point from James that I disagree slightly with is about determining the transactional state when there is a failure. It's no more difficult than with log shipping really. TR only replicates committed transactions. If the server fails, some committed transactions may not have been read by the log agent, granted, and so will not be at the replicate site, and you don't know what they are. But how do you know exactly which committed transactions were in the last transaction log backup used in shipping? You don't. So you are no worse off really. Neither solution allow you to know exactly what has been copied to DR and what hasn't. You need to determine it yourself. I'm not saying TR is a better solution, just that it's no less accurate in that respect than shipping. All solutions require you to have processes in place to detemine what is at the DR site. Of course if you are genuinely failing over to DR, you have lost your primary database, so what are you actually comparing DR against to determine its accuracy? In reallity you tend to end up with some users discovering data they entered is not there. It's a much bigger topic than most people realise and as James points out, most people don't plan adequately for using it in a real situation and are not properly prepared. They just assume everything will be there.

It's fair to say that if you have no need to access the DR site for reporting, etc. log shipping or mirrors are certainly likely to be easier to manage in general, assuming with shipping you can tollerate the latency. It all depends on the requirements of the business at the end of the day.
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9791 Visits: 1407
This may not be the best solution but this can be a good solution. The article is good.

Smile



James Luetkehoelter
James Luetkehoelter
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 33
That's an excellent point about the difference between log shipping and transactional replication - the latter does guarantee transactional consistency. I guess my point was that it isn't the easiest thing (especially if it set to continual replication) to determine exactly *what* that transactionally consistent state is (what made it over there last). Log Shipping is much more primitive, but when talking with business users and upper management it is easier to say "It will have lost 15 minutes of data" than X was the last transaction to get replicated.

Still, point taken, and a valid one. My stance on Log Shipping vs Transactional replication for DR purposes only is a controversial one. Still, transactional replication used for more than just DR (that would be a secondary or tertiary role) like have it for reporting servers and such is an excellent idea. And it's an excellent technology.

Thanks for including that point - I think it is important that people understand the pros and cons with each situation. There isn't a definitive answer for most of these topics.



davidsatz
davidsatz
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 181
James Luetkehoelter (4/17/2008)
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.


I have totally agree with you here (great article and that TR is not great for DR BigGrin )

If we assume transactional replication is setup perfectly and working, we still need to take your article a bit further and talk about how we would cutover the subscriber database to be the production database? And how we would get the original publishing db back in synch when it was ready to resume its role as primray? Another key consideration would be how much down time we need to make all these switches.
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