Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
1  
A big +1, you've reflected every single thought I had as I read the question. The DFS cluster part in particular had me wondering if anyone with SQL/storage expertise was involved in the design at all! –  Mark Storey-Smith Dec 2 '11 at 0:10
1  
I was really troubled by that statement. I would hope that their provider would know that isn't a viable solution, but apparently not. That alone makes me question the provider as a good hosting option. –  mrdenny Dec 2 '11 at 1:19

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.

share|improve this answer
    
Hey @dtmxxllc, do you have a reference to support your claim about PCI compliance and redundancy? –  Nick Chammas Dec 1 '11 at 22:18
    
PCI says nothing about redundancy it's all about production the data. Doesn't matter how many copies of the data you have, as long as it's encrypted. –  mrdenny Dec 1 '11 at 23:36

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

share|improve this answer
    
We can only cluster on one SAN, which will not buy us any more recoverability in the event of a SAN failure than the snapshot system provided by the hosting company. DFS would allow us to have clustering across sans, but is not PCI compliant. –  Josh Jun 15 '11 at 19:49
    
-1. The OP is right - they already have what clustering provides and are left with all the problems of clustering (i.e. corrupted databases not going online on a failover). Mirroring is the only solution. –  TomTom Jun 16 '11 at 4:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.