February 25, 2011 at 7:45 am
Hello,
Does any one know about disaster recovery solutions for SQL server in long distances?
I've read about log shipping in SQL server books and I know the difference between offline backups and log shipping, But I know there are third party softwares or hardwares that do this for us? Whis of tem is better considering bandwidth and data loss? There's no reliable and high speed link between two sites. I have about 50 databases, which are not large in size, except one of them which is about 5GB.
are 3rd party utilities reliable when a disaster occures? Do they need less bandwidth? Are they able to work on unreliable link?
after all which solution is better? If 3rd party utiliies are better, which of them do you recommend?
February 25, 2011 at 8:19 am
Being that your link is the "bottleneck" so to speak your solution should be something that can be sensitive to that and is going to send the least amount of data over that link. Looking at 3rd party tools is probably a wise choice there as there are some that are going to only send block level changes vs all changes which can be a good savings if data blocks are being touched more than once, etc.
With that being said, I haven't used anything besides SAN technology which is offered by most vendors. I have seen a presentation by DoubleTake which seemed pretty solid and price reasonable. So, that may be a place to start looking. I'm sure there are others as well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 8:33 am
If you really are on an unreliable network, then most of the really stable and fast DR scenarios are not going to work too well for you. Log shipping might be your best bet because it's more likely to be forgiving of you losing the connection to the backup site regularly. If you can get good connections though, your choices open up, a lot. I'd look into mirroring, but it's likely to be problematic for you with a bad connection.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2011 at 8:55 am
Mirroring is not a good solutions since it's limited to 22 DBs although It can be used in async mode in long distance.
I visited Double-Take website. But I don't know if it's reliable. I mean assume a condition where 10000 records have been inserted to the db, then they are backed up to another site, then the transaction rolled back and no record are in the main DB. But...if a disaster occures and no back ups run.......we have 10000 false records in the backup DB.
SAN solutions are expensive. I have a EVA in the main site, but for the backup site it costs a lot. I've heard about other solutions like veritas netbackup...but....don't know about it
February 25, 2011 at 9:04 am
The reality is though that DR is expensive and that is something that the company you work for is going to have to work with you on to determine what is acceptable recovery for a complete disaster situation.
As to your question about DoubleTake, I would guess that they, along with all DR solutions, are reliable in the sense that they offer data consistency. Again though that level of consistency is something that comes with a price, and you will have to determine what you are willing to pay for that security.
It may be that you can accept copying your nightly backups to the other site in which case your network connection may not be that big of an issue as the copy will not affect any of your internal processes.
If you have some hard recovery requirements that you can share then some more definitive solutions could probably be offered but as Grant mentioned your connectivity constraints are difficult to work around from a DR solution standpoint.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 1:18 pm
Hello,
In my opinion, from SQL 2005 and onwards, you can get pretty decent data protection with using both database mirroring and transaction log shipping. What I personally prefer, when looking to protect data is something along these lines:
Mirroring with high performance, you risk losing some transactions with it but it doesn't affect your production server. In smaller and quieter environments I might go with high protection, so you don't end up losing any transactions.
On top of that I usually keep log shipping, but I keep it intentionally several hours behind the production environment. I do copy the transaction log backup files continuously, but I restore them with delay of several hours. Log shipping is also very network friendly if you take log backups with short intervals, so they don't grow too large.
This way I can protect my data against hardware failures with mirroring and if some user goes about accidentally deleting something, I can restore that information from log shipped copy without needing to take production environment offline.
This is also very affordable, since you're not required to buy licenses for SQL Server instances that has mirrored or log shipped databases, as long as you don't use them. So it's just the cost of server hardware.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply