We are researching various ways to create an offsite disaster recovery solution. We are talking about either Transactional Replication or Log Shipping. Which would you use?
Using:
SQL2000
Down time max of 4 hours.
My concerns are once a failure occurs and we are running production at the offsite facility how quickly could we get back to the primary cluster?
Thanks for any input... :DThe ever popular answer: It depends.
If cost control is the primary concern, log shipping is almost always cheaper and easier. If quick failover is the primary concern, transactional replication allows you to get failover down to minutes, maybe less. Depending on how you implement things, there can be more than a factor of ten difference in price.
This is one area where I would recommend nearly anyone hire a consultant. You need to have someone with considerable experience help you make these decisions, because you've got a lot riding on the outcome and it is an area that very few people really understand.
-PatP|||i'm a big fan of logshipping in these situations, mostly because as pat said it is cheap but also because its is an automated restore and very simple.
xact repl is a very good situation but the simplest way for you to see it's downside is pretty simple. search through this forum for replication and count how many questions there are then try log shipping, i think log shipping will have fewer due to it's simplicity and if we have learned anything, it is K.I.S.S.
now to get your five nines up consider hardware clustering with log shipping.
you will have immediate uptime with parallel data at all times.|||OK, I have to bite!
How can Log Shipping be cheaper than replication? IT'S THE OPPOSITE!!! For replication the minimum requirement is SQL Standard and a connection (doesn't even need to be a network connection, it can be a dial up). For Log Shipping you HAVE TO HAVE EE ON BOTH BOXES, unless you script your own "custom log shipping". But then you can't call it "Log Shipping" because the participants do not have a knowledge of server roles as they do in EE environment.
To answer the question, I'd say if money is an issue, then write your own transaction backup/restore process that will keep the servers semi-synched with a lag equal to the scheduled frequency.
And Scott, the number of questions on replication is not indicative of it's simplicity or complexity. It demonstrates poor planning and lack of experience with the technology.|||When you are looking at DR, you have to scale the whole system (including the infrastructure) to support the largest possible load. That means buying a LOT of network "pipe" to move your data or a truly massive distributor (a separate box from the production database to avoid single point of failure). By the time you buy the third box, its licenses, and the "pipe" (which is an ongoing monthly expense) replication is usually considerably more expensive than log shipping.
The answer still boils down to "it depends" in my mind, due to the number of possible influences, many of which are non-technical in nature.
-PatP|||Now you really contradicted yourself. In the first post you said that ...log shipping is almost always cheaper and easier...while now you're saying that replication is the way to go...Is it Kerry's influence from last night debate?
Here's more food for thought in regards to non-technical aspect of the dilema (I didn't have MS pricing handy, so here's their comparison with Horacle spin):
http://www.microsoft.com/sql/evaluation/compare/pricecomparison.asp
10K (2CPU SE) vs. 40K (2CPU EE)
Which one? Decisions, decisions...|||Now you really contradicted yourself. In the first post you said that while now you're saying that replication is the way to go...Is it Kerry's influence from last night debate?Would you care to reevaluate that observation just a wee bit? Unless I missed my own point (which I suppose is possible), replication offers lots of benefits, but at (potentially) much higher cost. At least I think that I said the same thing both times.
-PatP|||i agree replicatioin has a much higher cost in man hours.
my point in the earlier post about how many questions are for repl vs logship was designed to highlight the common knowledge about repl that we all have.
It aint always the easiest of db technologies to implement. occasionally you get lucky where it sets up textbook and you will never have any issues with it. but then there is the other 90% of the time.
by the way rdjabarov , I cant believe i am agreeing with pat. i feel so john mccain right now. :eek:|||"I just love this country!" (has to be heavily accented - RUSSIAN)
If cost control is the primary concern, log shipping is almost always cheaper and easier.You guys rock! ...But please stick to the principles, don't try to make a "quick win", and we'll all be happy, I promise!!!|||I guess I forgot to "reevaluate my observation", huh?
Log Shipping will require EE on both boxes. Assuming each box has at least 2CPUs, that's $40K per box. If the current prod server is running EE already, then the number above is the minimum non-hardware investment required to implement Log Shipping.
For warm stand-by solution or DR site (which is pretty much the same thing) I am not even recommending replication because it's not what it was designed for to begin with. I think writing a couple of scripts (literally a couple) while having even MSDE edition is all that is needed.
I started arguing with everybody here because I thought that suggestions made did not target the requirement presented by RobS.We are researching various ways to create an offsite disaster recovery solution. We are talking about either Transactional Replication or Log Shipping. Which would you use?|||Ok here we go. More things I thought about after reading your all's posts and doing more thinking over the weekend.
What happens if Host A goes down (internet connectivity) and we have to switch to Host B (DR Site). No need to worry about how that is done right now. My question is now which would be the easiest to get Host A back in sync?
My thought, expense has not yet been budgeted so we'll go with Money is not an obstacle at the moment.
Using replciation, setup a distribution server at Host C. Use Bi-Directional Replication so when Host A goes down Host B is up to date. The Host C (Distribution Server) is queing active transactions waiting for Host A to come back online. Once Host A is back online Host C should bring it back in Sync continuing to sync Host A while Host B is being used for production. At night (Maintenance Window) everything is then switched back to Host A with minimal downtime.
During normal operations (Host A) Host B would not be doing anything so there would be no transactions coming down the pipe to Host A.
I see replication being the quickest way to get Host A back online if a failure occurs. This entire system would have to be babysat I know. Are my thoughts flawed? :D Be kind... ;)|||My $0.02 worth since I had to maintain replicated databases for over 2 1/2 years. The replicated database(s) cannot be fully functional at once if the application(s) relies (rely) on identity columns in any table, because the value if the identity column is replicated and the receiving table has to accept that value and not try to create it's own identity value.
RobS ... as you can see, there are pro and con for each method. I personally like log shipping, even if home grown (ala BORK 4.5 for SQL 7).
And of course that brings back to the never ending debate of surrogate keys vs. natural keys :eek:|||There is one aspect of replication that I do not like. When you go to change a table (add/drop a column) or add a new column, there is manual intervention involved with getting the new data added to the publication. Admittedly, this does not happen a lot in most shops, but it happens just often enough to bite you in a sensitive spot.
The other aspect that I never liked may have been fixed. Has anyone played around with replicating stored procedures and views in SQL 2000?
If you can, I would go with the Log Shipping. In order to fail back to your regular systems, you would have to set up log shipping in "reverse" for the time it takes to get the regular system back on its feet data-wise. This could take a lot of time behind the scenes with no backup server in place, depending on how large the database is.|||For DR solution there 2 proven methods that have been tested and retested for the past 5 years by many companies: Log Shipping (or a custom backup/restore), and HW-based replication, like EMC or other giants of proprietary technologies. Caveats mentioned by the previous 2 posts clearly demonstrate that replication is not a solution for DR, because it's not what it is designed for.
