Looking for recommendations for a high availability solution for our Data Center that has 2 instances with about 250+ databases. We are currently using Database Mirroring but are interested in seeking out another solution. Any recommendations would be greatly appreciated. We have multiple servers available for this project. Thanks again.
1. Do you need to fail over the entire SQL instance all at once? 2. Do you need to fail over specific databases or groups of databases? 3. Are you using SAN storage? 4. Are the servers in different physical sites? 5. What Edition are you using? Is Enterprise Edition pricing affordable or not? 6. What are your recovery point (amount of lose-able data) and recovery time (amount of time to recover) objectives?
1. It would be nice if everything failed over at an instance level. 3.no 4. No all servers are located together at our data center 5. We are using Standard Edition and unfortunately Enterprise would not be an option. 6. We could lose only 15 minutes of data and recovery time is crucial. (No down time).
Thank you for your response. Since enterprise is not an option I am hoping to find a better solution and any suggestions would be greatly appreciated :) Thanks again.
With the information you've given, mirroring would typically be your best option. As far as the others:
Clustering would be better but it requires shared storage, like a SAN or using third-party disk replication of local disks.
Availability Groups are Enterprise-only, so that's out.
I'm not a replication guru. While it may be possible to set up replication to provide the level of response you've mentioned, it would be pretty complicated and require a lot of attention. It also limits changing database schema.
Log shipping would be my recommendation if you can't continue with mirroring. They work on the same principle, replicating the transaction log to another server (mirroring is continuous, log shipping is done at intervals). More info here:
Without knowing more about how busy your databases are, I can't guarantee log shipping will meet your recovery point needs. You can ship logs pretty frequently (I think 1 minute is the shortest interval) but restoring them takes time and disk activity, and failover is a manual process. If you had a big outage (like server failure) it would be tricky to bring up 250+ log shipped DBs unless you automate the process.