Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

Question

We have a situation where we are moving to a new hosting facility and are attempting to configure our new database environment. However, none of us are formal DBA's so a lot of what we are doing is guessing and reading articles while attempting to make an informed decision.

Firstly, we are currently a small company who takes in several hundred thousand records (about 1/2 of them have image data) in a month. We are growing quickly, and after a severe failure (a SAN array went down completely and we lost everything over a 12 hour period), we are moving to a new hosting facility with a better disaster recovery.

The new hosting facility will have our database on a 50 disk SAN. Every minute, a snapshot is taken. Should the SAN fail, the snapshot is loaded to another SAN, and the server is automatically brought up on the new SAN. Downtime is a few minutes.

In addition to this, we want a redundant database setup. We discussed having a Sql Server cluster using DFS on 2 separate SANs, but that violates PCI Compliance, which we must have. Therefore, it is my opinion that a Cluster buys us no more redundancy than the hosting company's built in snapshot system in the event of a SAN failure.

The other option we are discussing is using mirroring. However, the information we have read leads us to believe that mirroring with a witness will affect performance too greatly. One option presented was to use "Safety Off" mirroring until we want to perform maintenance, then fire up the witness, take one server down at a time and perform the maintenance. Then, when complete, power down the witness. This seems the best of both worlds, but also a headache.

So now the big question - With the snapshot system of our hosting company taking our downtime to mere minutes, what configuration is recommended to give use high availability as well as the best performance?

Answer 1

I would go with mirroring with a witness, do you want to do the work of a manual fail-over at 3AM when "socks hit the fan"? Check out Paul Randal's MCM prep videos, they might help you out in your decision.

Answer 2

First things first. If you configure a Witness, but choose "High Performance" ("Safety Off"), the witness will cause a voting quorum to be formed and quorum will be required for the principal to remain available. This is different from "High Performance" without a witness where a voting quorum is not formed. Ref: Asynchronous Database Mirroring (High-Performance Mode)

You have not mentioned how you store the image data, but the FileStream will not function under Database Mirroring. Ref: Using FILESTREAM with Other SQL Server Features

Microsoft has released guidance on the load generated by Database Mirroring. You might want to review Database Mirroring Best Practices and Performance Considerations, Things to consider when setting up database mirroring in SQL Server (A little old, but still good), and check out the book "Pro SQL Server 2008 Mirroring" by Robert Davis (Amazon link)

I suggest not passing on Database Mirroring until reviewing your workload against the Microsoft guidance; perhaps even making use of the SQLIOSim utility -- Ref: How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem

If you choose to not use Database Mirroring and Fail-Over Clustering is not an option, then I would review Log-Shipping. The primary drawback of Log-Shipping, when compared to Database Mirroring, is the increased delay for availability of data on the receiving systems. In other words, Log Shipping will not provide the data-delivery timeliness of Database Mirroring.

We do not have your workload information or hardware configuration and we do not know of any changes you have considered (application and database) which will reduce modifications in the database. Given this, none of us can tell you whether Database Mirroring is for your environment or not.

Answer 3

I see some issues here.

1. Unless the snapshots which are being taken are VSS complient the disk based snapshots of the databases aren't guaranteed to be valid.

2. Disk based snapshots aren't a good backup solution as they rely on the same blocks as the production data. If there's a major SAN failure your snapshots will be useless as well.

3. You can't use DFS for a SQL Cluster.

Based on what you've mentioned as your load (a few hundred thousand records a month = 683 records a minute) mirroring will work just fine for you.

If I was setting this up for a client, I'd scrap the storage snapshot unless I know a LOT more about the storage config and how this would all be done and more towards database mirroring with a witness.

Answer 4

If you have the budget, clustering your SQL servers will give you the best HA with the least downtime.

Answer 5

In regard to PCI compliance, redundancy is allowed so long as the database is encrypted. This means your solution of having 2 separate SANs is actually valid.