Upcoming Webinar
Cayosoft Shows How Forest Recovery Tech Addresses Survey Issues
Join this webinar and not only learn about what your peers are doing, but also learn about a new patent-pending modern approach to AD forest recovery from Cayosoft.
New episode!
The Scoop on Loop: The Latest Innovations Directly From Microsoft!
Darrell Webster speaks to Rebecca Keys, a Program Manager from the Microsoft Loop team.
Turn Data Protection into an MRR Growth Engine
Security for your customers, revenue for your MSP practice. Access key insights on how to scale your practice with SkyKick’s new eGuide.
MSP eGuide to Package, Sell and Deliver M365 Security Services
Based on input from our top-performing MSPs, SkyKick prepared the MSP Guide to help you navigate the security landscape.

SharePoint Planning: Understanding SQL Server High Availability Options

SharePoint can be complicated all in itself. But in many ways, SharePoint is just a web front end for your SQL servers. Without SQL Server, there is no SharePoint. Because SQL Server is so critical to the success of SharePoint, it can be very important to plan out your options for SQL Server before you even begin installation of SharePoint on your application servers.

This article will show you your high availability options in SQL Server. Using these solutions can help your SharePoint infrastructure to quickly recover from, and in some cases completely withstand the loss of, one of your databases or SQL Servers.

SQL Server High Availability Solutions

The three options that we have for high availability with SQL Server are SQL database mirroring, SQL server clustering, and SQL Server AlwaysOn. Both mirroring and clustering are available on SQL Server 2008, but SQL Server AlwaysOn is a new feature of SQL Server 2012.

1. SQL Database Mirroring

SQL database mirroring is one of the simplest forms of high availability. It is very easy to understand: Simply keep an extra copy of your database on a separate server and continuously copy over from one to the other.

DB mirroring duplicates the DBs from a primary instance to a secondary. While you have two copies of the databases in a mirror, you won’t be making data writes into both copies of the database. While changes to the database are kept in sync between the mirrors, one server will be indicated as the primary server, and the second server will be a secondary. In case of the primary server becoming unresponsive, the secondary server will be able to continue to provide access to the data in the database.

DB to DB relationship (one DB per relationship). One thing to keep in mind when considering database mirroring is that mirroring is configured on a per-database basis, so each database can have different mirroring configuration settings. You may determine that some databases need to be mirrored while others do not require high availability.

No matter what you determine to be your needs, each database that you want mirrored needs to be configured separately.

Use a witness for automatic failover. Another thing to keep in mind about SQL database mirroring is that the failover from the primary server to the secondary instance has to be done manually unless you use a third SQL instance to monitor the availability of the instances. This instance that monitors the primary and secondary mirrors is called a witness.

2. SQL Server Clustering

The big difference between mirroring and clustering is that instead of keeping multiple copies of the database, you actually use shared storage such as a SAN to keep a single copy of the database.

Servers are called nodes. Since in a cluster you’ve got only one copy of the shared database, you can have multiple SQL servers all looking at the database, and all are able to make changes to the database. In terms of the cluster, each SQL Server is called a node, and you can have two or more nodes in a SQL cluster.

Nodes use a shared disk. Since all of the nodes use a shared disk, you can easily scale out the resiliency of the farm by adding more nodes to a cluster.

Nodes communicate with each other. During the normal operation of the databases in the cluster, there is a steady stream of communication happening between the nodes. This communication keeps checking in with all of the nodes to make sure that each node is still operating normally and responding to requests.

If communication fails, other servers take ownership. In the event that one of the nodes stops responding to the communication requests, another node in the cluster will nominate itself to take over ownership duties of any databases that were owned by the nonresponsive server.

3. SQL Server AlwaysOn (SQL Server 2012 Only)

SQL Server AlwaysOn is new to SQL 2012. If you’re putting your SharePoint 2013 farm on a SQL 2012 backbone, you can consider AlwaysOn as a high availability option for you.

SQL Server AlwaysOn combines mirroring and clustering. SQL Server AlwaysOn takes some of the best approaches from both mirroring and clustering to enable an automatic failover that is resilient to either the loss of a SQL server or the loss of a storage array.

AlwaysOn availability groups work like mirroring for your storage. Availability groups operate like clusters but for the databases instead of the servers. You can configure your storage to create multiple replicas of the database instance across a number of drives.

Updates to the primary replica can be synchronized with a secondary replica. If the primary is ever offline, then the secondary replica takes over and continues providing service.

AlwaysOn failover cluster works like SQL 2008 clusters. The servers themselves are also still able to be clustered together in an AlwaysOn configuration. The servers are assigned into a logical failover cluster. If any of the servers in the failover cluster stop responding, then another server in the cluster takes over.

Additionally, you can have multiple failover clusters and configure it so that if one cluster goes offline the other cluster picks up the service from the first.

SharePoint Planning and High Availability

This is something that is going to be largely determined not by the SharePoint architect but by the business that is installing SharePoint. How long the SharePoint services can be offline – and in the case of a failure, how much data loss is considered acceptable – will be the driving force of what options you choose.

But now that you know your options, you’ll be ready to dig deeper into mirroring, clusters, and AlwaysOn availability to help satisfy your SharePoint infrastructures high availability needs.