1

I'm looking for advice on the best HA/DR strategies for SQL Server. Currently I'm using Express edition with backups being copied over a the WAN to a remote site and being restored.

The SQL Express instances are hosted on a virutalised server (currently VMWare) on a box with direct RAID storage.

I'm keen to reduce the potential data loss and improve the recovery time from what the current solution is. I see a few options to help this.

1) Move to SQL enterprise and use replication

2) Move data to an iSCSI SAN and replicate the virtual disk containing SQL Data, then use a VMotion or XenApp equivilant to move from one site to the other. (I don't know if this will work from a SQL server transactional perspective)

3) Cluster virtual machines across the WAN link.

Currently the WAN link is 10Mb, and I would be reluctant to increase this - so I'm curious to find which solution will give best bandwidth utilisation, the best reliability and be most cost effective.

I am open to other suggestions as well.

Thanks, Chris

bikkies
  • 136
  • 3

4 Answers4

1

Clustering in a virtual environment is going to be very difficult and expensive because of how the shared storage on a cluster works.

Replication also has limits and may be more than you need, especially since sql 2005 enterprise is going for about $25k for each processor or $14k per server + CAL's. With replication you would need to have two licenses of enterprise, a cluster you would only need a single license however.

Since you are only on SQL Express now though, You might want to make your first jump to SQL workgroup edition and configure log shipping from one server to the other. If you have a need for the next step up to SQL Standard, then I would go with database mirroring.

In fact, personally, unless there is a specific reason not to, I would go with database mirroring over most of those solutions, although there is a change of slight performance degradation on very high transaction systems as well as a little more complexity to setup than something like log shipping. Clustering would be the most complex setup and definitely most expensive due to hardware requirements.

Check out these links: http://msdn.microsoft.com/en-us/library/ms190202%28SQL.90%29.aspx - High Availability Solutions
http://msdn.microsoft.com/en-us/library/ms189134%28SQL.90%29.aspx - Failover Clustering
http://technet.microsoft.com/en-us/library/cc917680.aspx - Database Mirroring
http://msdn.microsoft.com/en-us/library/ms188698.aspx - Log Shipping
http://www.microsoft.com/Sqlserver/2005/en/us/pricing.aspx - Pricing
http://www.sqlservercentral.com/articles/Disaster+Recovery/sqlserver2005highavailability/2421/ - High Availability at SQL Server Central
http://www.amazon.com/gp/product/159059780X - Awesome High Availability book for SQL 2005 that I recommend to every MS DBA

tcnolan
  • 432
  • 2
  • 12
1

I also found the following:

http://www.xlink.com/REPLICA/ent/clusterdescription_ent.aspx

Seems like quite a low-cost solution to me. Anyone any experience?

bikkies
  • 136
  • 3
0

I agree with tnolan in that you should try frequent log shipping or database mirroring. These are very cost-effective solutions and are designed specifically for the scenario you have. Clustering is designed for and works best for local resilience, not geographically dispersed databases. Clustering also requires hardware that is on the clustering HCL.

If you don't want to upgrade your edition of SQL Server you could roll your own log shipping solution using stored procedures and SQL Agent. Google for log shipping - there are plenty of home-grown solutions you could try.

Mark Allison
  • 2,188
  • 7
  • 26
  • 45
  • Mark - my only concern with mirroring is the number of instance that I have --> being over 40 on a server --> i've read that mirror won't be particularly effective? – bikkies Sep 14 '09 at 13:19
  • It doesn't depend on the number of databases being mirrored, rather the amount of committed transactions per second that you are trying to send across the WAN. What kind of throughput do you expect, and how many databases? – Mark Allison Sep 14 '09 at 15:05
0

Take a look also at 3rd party disk replication tools if you don't want to invest heavily in licensing.

I use Double Take on our work servers which provides block level data replication like you'd expect on a SAN and performance to date has been fantastic. We have it copying the SQL binaries as well as the data - plus I can use it to create extra replica sets for non SQL data. It's certified by MS to work wit SQLs data files.

I'm sure there's others out there that do a similar job but this is the product our hosting company swears by.

Chris W
  • 2,670
  • 1
  • 23
  • 32
  • Hi Chris, thanks for your suggestion. Can you comment on bandwidth usage of this solution? I was also under the impression that licensing for Double Take would be more expensive that SQL Enterprise. Am I wrong on that? Thanks, Chris – bikkies Sep 14 '09 at 13:17
  • We pay per month as part of our hosting agreement - it's an expensive product but it works out at about the same as we'd be charged to upgrade to Enterprise edition. Given that it'll replicate the binaries and non SQL stuff we're happy with it. We assessed switching to mirroring in enterprise recently and it would have introduced more maintenance work for us so it was more cost effective to use Double Take. – Chris W Sep 15 '09 at 08:17