High Availability Options For SQL Server

Question

I'm looking to upgrade my single server SQL Server 2008 environment to a platform with much greater redundancy. The current system was put in 5 years ago when the company started and so was built down to as low a cost as possible. However, we're now well established with a large client base and as such the replacement system needs to be much more redundant.

We have a few sites around the country (as we're part of a larger group with multiple sites), so have access to backup offices etc and remote sites to host servers.

I'd like suggestions about how best to achieve having our two or three key databases available throughout hardware failure or lack of access to site. I'm thinking of some type of cluster with a pair of local servers, then one off-site server with a copy of the DBs on. The DBs themselves are actually very small, so hardware can be pretty low spec and I can get away with a few physical boxes. However, I'm conscious that SQL Enterprise 2016 is VERY expensive as we'd have to licence by core.

I'm open to all suggestions/options etc. We're a pure MS house, so we've no other OS platforms. I'm reading conflicting reports regarding DB mirroring vs clustering etc.

Thanks

Answer 1

For your onsite solution Standard Edition allows you to implement a two node Failover Cluster Instance with Basic Availability Group or database mirroring. https://msdn.microsoft.com/en-us/library/mt614935.aspx

For your offsite solution you could implement log shipping.

They are using the "Always On" naming convention for a lot of the high availability options now. There is Always On Failover Cluster Instances which is the old Windows Server Cluster Service that is for hardware failover with SQL Failover Cluster Instance that is for SQL Instance failover. Then, there is Always On Availability Groups which requires Enterprise Edition and consists of a group of databases that can failover to replicas and replicas can be read-only, if one so desires. Basic Availability Group is a limited version of the Always On Availability Group, and is for just one database. It will completely replace database mirroring when it is removed from future versions. Lastly, database mirroring still exists.

SQL Server 2016 Edition Feature Comparison: https://msdn.microsoft.com/en-us/library/cc645993.aspx

If you can fulfill business requirements with Standard Edition, then there is less reason to go virtual, which adds a whole other layer of complexity.

Answer 2

As with anything it depends on how much you are willing to spend on your solution versus what the SLA says about recoverability and availability.

Brent Ozar has a very good document to plan an HA environment with broad costs on each solution. Link

What you are describing is a 2 node Windows cluster with either Log Shipping, Mirroring or Always On copy of the databases. Each solution will grow in price as you need greater redundancy and higher availability.

Thinking on your needs I might also suggest a virtual environment. This is depending on the transaction count though. In a high transactional environment I would stay physical, but with Virtual you can assign the number of cores and I believe licensing will be better.

If I were building out what you suggested, I would go with a 2 node cluster for hardware failover. If using 2016, then it would depend on uptime and what the copy needs to do and if you can have any discrepancy in how live the data is. You can do log shipping for the low end, transactional replication for the middle and then AlwaysOn group for the high-end.

It always depends on how much money you have to spend on your solution.