This article introduces several SQL Server high-availability solutions that improve the availability of servers or databases. A high-availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized. This article will discuss about High Availability in SharePoint using SQL Server and its options.
SQL Server provides several options for creating high availability for a server or database. High-availability options include the following:
Failover clustering provides high-availability support for an entire instance of SQL Server. A failover cluster is a combination of one or more nodes, or servers, with two or more shared disks. Applications are each installed into a Microsoft Cluster Service (MSCS) cluster group, known as a resource group. At any time, each resource group is owned by only one node in the cluster.
The application service has a virtual name that is independent of the node names, and is referred to as the failover cluster instance name. An application can connect to the failover cluster instance by referencing the failover cluster instance name. The application does not have to know which node hosts the failover cluster instance.
A SQL Server failover cluster instance appears on the network as a single computer, but has functionality that provides failover from one node to another if the current node becomes unavailable. For example, during a non-disk hardware failure, operating system failure, or planned operating system upgrade, you can configure an instance of SQL Server on one node of a failover cluster to fail over to any other node in the disk group.
A failover cluster does not protect against disk failure. You can use failover clustering to reduce system downtime and provide higher application availability. SQL Server Enterprise and SQL Server Developer edition supports Failover clustering with some restrictions, in SQL Server Standard.
Database mirroring is primarily a software solution to increase database availability by supporting almost instantaneous failover. It can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database.
The mirror database is created by restoring a database backup of the principal database with no recovery. This makes the mirror database is inaccessible to clients. However, you can use it indirectly for reporting by creating a database snapshot on the mirror database.
The database snapshot provides clients with read-only access to the data in the database as it existed when the snapshot was created.
Each database mirroring configuration involves a principal server that contains the principal database, and a mirror server that contains the mirror database. The mirror server continuously brings the mirror database up to date with the principal database.
It runs with either synchronous operation in high-safety mode, or asynchronous operation in high-performance mode. In high-performance mode, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.
Using high-safety mode, a committed transaction is committed on both partners, but at the risk of increased transaction latency.
In its simplest configuration, database mirroring involves only the principal and mirror servers. Within this configuration, if the principal server is lost, the mirror server can be used as a warm standby server, with possible data loss.
High-safety mode supports an alternative configuration, high-safety mode with automatic failover. This configuration involves a third server instance, known as a witness, which enables the mirror server to act as a hot standby server. Failover from the principal database to the mirror database typically takes several seconds.
Since SQL Server 2005 Service Pack 1 (SP1), database mirroring partners and witnesses have been supported by SQL Server Standard and SQL Server Enterprise. But the partners must use the same edition, and asynchronous database mirroring (high-performance mode) is supported only by SQL Server Enterprise. Witnesses are also supported by SQL Server Workgroup and SQL Server Express.
Like database mirroring, log shipping operates at the database level. You can use log shipping to maintain one or more warm standby databases for a corresponding production database that is referred to as the primary database. Standby databases are also referred to as secondary databases.
Each secondary database is created by restoring a database backup of the primary database with no recovery, or with standby. Restoring with standby lets you use the resulting secondary database for limited reporting.
A log shipping configuration includes a single primary server that contains the primary database, one or more secondary servers that each have a secondary database, and a monitor server. Each secondary server updates its secondary database at set intervals from log backups of the primary database.
Log shipping involves a user-modifiable delay between when the primary server creates a log backup of the primary database and when the secondary server restores the log backup. Before a failover can occur, a secondary database must be brought fully up-to-date by manually applying any un-restored log backups.
Log shipping provides the flexibility of supporting multiple standby databases. If you require multiple standby databases, you can use log shipping alone or as a supplement to database mirroring. When these solutions are used together, the current principal database of the database mirroring configuration is also the current primary database of the log shipping configuration.
Log shipping is supported in the SQL Server Enterprise, Standard, and Workgroup editions.
Replication uses publish-subscribe model. This lets a primary server, referred to as the Publisher, distribute data to one or more secondary servers, or Subscribers. Replication enables real-time availability and scalability across these servers.
It supports filtering to provide a subset of data at Subscribers, and also allows for partitioned updates. Subscribers are online and available for reporting or other functions, without query recovery.
SQL Server offers three types of replication: snapshot, transactional, and merge. Transactional replication provides the lowest latency and is usually used for high availability.
Replication is supported in all editions of SQL Server. Replication publishing is not available with SQL Server Express or SQL Server Compact 3.5 SP1.
Scalable shared databases
The scalable shared database feature lets you scale out a read-only database built exclusively for reporting. The reporting database must reside on a set of dedicated, read-only volumes whose primary purpose is hosting the database.
By using commodity hardware for servers and volumes, you can scale out a reporting database that provides the same view of the reporting data on multiple reporting servers. This feature also allows a smooth update path for the reporting database.
Selecting a High Availability Solution
The following list presents considerations for selecting a high-availability solution:
Failover clustering and database mirroring
Failover clustering and database mirroring both provide the following:
- Automatic detection and failover
- Manual failover
- Transparent client redirect
Failover clustering has the following constraints:
- Operates at the server instance scope
- Requires signed hardware
- Has no reporting on standby
- Utilizes a single copy of the database
- Does not protect against disk failure
Database mirroring offers the following benefits:
- Operates at the database scope.
- Uses a single, duplicate copy of the database
- Uses standard servers
- Provides limited reporting on the mirror server by using database snapshots.
- When it operates synchronously, provides for zero work loss through delayed commit on the principal database.
Database mirroring offers a substantive increase in availability over the level previously possible with SQL Server and offer an easy-to-manage alternative to failover clustering.
Log shipping can be a supplement or an alternative to database mirroring. Although similar in concept, asynchronous database mirroring and log shipping have key differences. Log shipping offers the following distinct capabilities:
- Supports multiple secondary databases on multiple server instances for a single primary database.
- Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. A longer delay is useful, for example, if data accidentally changes on the primary database. And If the accidental change is noticed quickly, a delay can let you retrieve, still unchanged data from a secondary database before the change is reflected there.
Asynchronous database mirroring has the potential advantage over log shipping of a shorter time between when a given change is made in the primary database and when that change is reflected to the mirror database.
An advantage of database mirroring over log shipping is that high-safety mode is a no data loss configuration that is supported as a simple failover strategy.
Replication offers the following benefits:
- Filtering in the database to provide a subset of data at the secondary databases because it operates at the database scope
- More than one redundant copy of the database
- Real-time availability and scalability across multiple databases, supporting partitioned updates
- Complete availability of the secondary databases for reporting or other functions, without query recovery.
Usability in SharePoint Farm
Failure Clustering and Database Mirroring
These are the great way to have a readily available copy of your database, primarily used for failover. It is specifically a High Availability application. If you want to access the “copied” database you won’t want to do mirroring/clustering because the mirrored or failure database is inaccessible and is only used when failover is needed. You can’t hit it for anything, simple reporting, select statements, view table structure…nothing.
It will just sit there and mirror your principle database until you either manually or automatically failover (depending on how you set it up).
Hence, copied database from DC cannot be used by other applications (e.g. by DR applications).
Replication is a way to have accessible data in 2 locations. There are many different ways to replicate like snapshot, transactional, merge. You will need to look at which best fits your environment.
Replication, however, does not avail itself to failover either automatically or manually. You can failover to a replicated database by changing all connection strings or manipulating DNS, you just have to make sure everything is where it needs to be and test a manual redirect before relying on it. Failover handing isn’t what replication is made for.
Based on above discussion, we can get an assumption that we can configure replication between database objects at DC & DR and then add seconday database to DR SharePoint web application. But when, It is tried at destination web application, following error is thrown at the time of adding secondary database as content database:
WSS_Content_Int_Replication on DRSQLSRV08 contains user-defined schema. Databases must be empty before they can be used. Delete all of the tables, stored procedures and other objects or use a different database.
Here, WSS_Content_Int_Replication is a replicated database name and DRSQLSRV08 is SQL Server instance.
When, backup of source database is restored in other SharePoint farm and this restored database is added as content database to any SharePoint web application. Everything works fine.
It would also be reluctant to say, Mirroring and log shipping are basically the same thing. There are many differences that make them optimal solutions in particular scenarios. Log shipping also does not support automatic failover or client redirection; it is much like replication in that matter meaning most everything is manual.
Log shipping ships both committed and uncommitted transactions, whereas mirroring only ships committed transactions. With mirroring any database (principal) can only have one mirror. With LOG shipping you can ship to multiple secondary servers.
In log shipping, secondary databases can be used as read only databases for applications.
Hope you enjoyed reading it.
Please don't forget to Share, Follow and Like to get updated with latest posts.
It gives me motivation to share more knowledge with you.
Project Management Professional (PMP) and Microsoft certified, motivated, energetic and accomplished Project Manager / Architect with 15+ years of work experience in Management, Architecture, Analytics, Development and Maintenance. I have been fortunate to be a part of over 25+ .Net / SharePoint projects delivery with various companies across different industry sectors. This has provided me a valuable insight and experience especially in successful implementation of SharePoint based solutions.
My experience in Web application implementation includes technology strategy and road-map definition, business and technical requirements identification, governance, platform architecture, solution design, configuration, development, quality assurance, training, post-production support, team lead and overall project delivery along with project management.
Satyendra Mishra holds a B.Tech. in Computer Science & Engineering and PG Diploma in Advance Computing from Center for Development and Advance Computing, Pune, India. He is also a certified Project Management Professional (PMP).
I love to share Project Management Tips and Tricks by writing Blogs in the Project Management India Community. I have written around 300+ articles in the Project Management, .Net, SharePoint and related client side technologies.