April 17, 2008 at 12:19 am
Comments posted to this topic are about the item High Availability (DR) Using SQL Server 2005 Transactional Replication
Paul Ibison
Paul.Ibison@replicationanswers.com
April 17, 2008 at 2:04 am
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.
April 17, 2008 at 6:13 am
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
April 17, 2008 at 6:41 am
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
April 17, 2008 at 7:14 am
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
April 17, 2008 at 7:19 am
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
April 17, 2008 at 7:22 am
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
April 17, 2008 at 7:23 am
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
April 17, 2008 at 9:12 am
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.
April 17, 2008 at 9:52 am
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
April 17, 2008 at 10:18 am
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:
April 17, 2008 at 10:27 am
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
April 17, 2008 at 10:39 am
I hear ya, Paul. We are just now starting to migrate to SQL Server 2005. Your article was valuable in fo to me...
April 17, 2008 at 5:32 pm
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.
April 17, 2008 at 10:07 pm
This may not be the best solution but this can be a good solution. The article is good.
🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply