Five Tips For Running A High Availability Server With Microsoft’s SQL Server 2012

Five Tips For Running A High Availability Server With Microsoft’s SQL Server 2012

Image Credit: Victor Grigas

If you’re running a database that serves as the core of a set of high-availability business apps, then you need to ensure the server running that database has as much uptime as possible. Today, we’re going to talk about how you can do so with Microsoft’s SQL server 2012.

It’s actually easier than you’d think.

Use Failover Clustering

Failover clustering has been baked into Microsoft SQL server for many releases, and makes use of a combination of one or more servers across a subnet (or a series of subnets). If one server goes inactive due to hardware failure, another server will immediately spin up to take its place. Multi-site failover clusters in particular make use of storage-level replication to maintain a copy of a user database at the disaster recovery site.

Database Mirroring and Availability Groups

Another important feature of SQL Server is Database Mirroring. Transaction streams are ‘mirrored’ on a backup server while being run on the main server. Again, this is a matter of redundancy – if the main transaction stream should fail, the secondary stream takes its place without interrupting anything critical.

Administrators can configure database mirroring in one of several modes:

 

  • High-safety with automatic failover: The transaction occurs simultaneously on both partners, and a third partner acts as a ‘witness’ in order to coordinate automatic failover should the main transaction fail. The main disadvantage of this mode is it tends to cause transaction latency, which can cause problems in certain high-performance applications.
  • High-safety with manual failover: The transaction occurs simultaneously on both partners, but without automatic failover – administrators must manually switch streams if an operation fails.
  • High-performance: The transaction on the main partner commits to disk, then the secondary partner processes the transaction afterwards.  

 

At this point, a warning is necessary: in later installations of SQL Server, Microsoft has declared that database mirroring is discontinued in place of AlwaysOn High Availability Groups – which actually works fairly similarly to mirroring; a group of databases failover together as a single entity, allowing for superior failover and redundancy.

Incorporate Log Forwarding

Although it has a great deal in common with database mirroring (the use of secondary servers to store log backups), log forwarding/shipping is distinct enough to warrant its own mention. A low-cost solution at the database level of a server, log shipping allows logs to be stored on a backup server (or servers) at a set interval, ensuring that if a failure occurs you can restore from a backup in short order.

Consider Hybrid Deployment and The Cloud

Rather than storing backups on-premises or in a co-op data center, many administrators are instead choosing to make use of cloud computing in order to ensure their databases are kept running as effectively as possible. By running SQL Server as a hybrid deployment, you can ensure more effective, faster failover…and by running it entirely in the cloud, you can add cost savings to the list, as well.

There also exists a growing camp of vendors that offer High-Availability-As-A-Service, such as Amazon and VMWare. It’s up to you whether or not you want to work with such vendors, but it’s worth mentioning that their solutions often contain a number of access restrictions and shortcomings that make them less than ideal when weighed against a deployment of your own.

Don’t Settle For The Standard Edition

Unfortunately, one of the biggest weaknesses of MS SQL Server is the fact that the Standard edition is actually quite limited in terms of high-availability features. With the Enterprise Edition of SQL Server 2014, for example, you’ve access to stuff like database snapshots, online re-indexing and parallel indexing, auditing, and business intelligence capabilities. With the standard edition, you’ve access to…none of that.

As such, if availability is one of your chief concerns, you’re going to need to shell out for the Enterprise Edition – because anything else isn’t going to serve your needs.

We Can Help

Liberty Center One can help you achieve the best of all worlds – a customized deployment so that you retain control, coupled with a cloud infrastructure as a service that results in cost savings. As a Microsoft SPLA vendor, we can supply the Enterprise SQL licenses you need at an affordable monthly price. Plus our Infrastructure as a Service platform is fully housed in our high availability data center. Let us help you build your own high availability services at Liberty Center One.


Subscribe with Feedly