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 10:18 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,

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)



Post #486596
Posted Thursday, April 17, 2008 10:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 8, 2013 5:26 AM
Points: 14, 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
Post #486604
Posted Thursday, April 17, 2008 10:39 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
I hear ya, Paul. We are just now starting to migrate to SQL Server 2005. Your article was valuable in fo to me...


Post #486618
Posted Thursday, April 17, 2008 5:32 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 11, 2014 4:11 PM
Points: 786, Visits: 2,217
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.
Post #486852
Posted Thursday, April 17, 2008 10:06 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,471, Visits: 1,402
This may not be the best solution but this can be a good solution. The article is good.

:)



Post #486885
Posted Friday, April 18, 2008 10:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 6, 2011 8:20 PM
Points: 227, 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.



Post #487363
Posted Tuesday, April 29, 2008 2:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:03 AM
Points: 10, Visits: 159
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 :D )

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.
Post #492477
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse