Achieving high availability and disaster recovery with SharePoint databases
Ross Mistry and Shirmattie Seenarine, Contributors
Microsoft SQL Server is the back-end repository for Microsoft Office SharePoint Server 2007, or
MOSS. As a result, it is important to consider SQL Server when designing a high availability and
disaster recovery option for your SharePoint databases.
SQL Server offers four options for high availability or disaster recovery: failover clustering ,
database mirroring, log shipping and replication. Of these technologies, failover clustering and
database mirroring are regularly used with SharePoint. Both options offer automatic failover, which
is seamless for the MOSS application and its clients.
SharePoint and SQL Server failover clustering
A failover cluster is achieved when two or more servers (also known as nodes) connect
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in August 2009
to shared
disk resources. Failover clustering with SQL Server 2008 provides server level redundancy for an
entire instance of SQL Server by leveraging the shared-nothing cluster model included with Windows
Server.
When SQL Server is installed on a failover cluster, it appears on the network as a single
computer. This is independent of how many physical nodes are actually within the cluster.
During SQL Server failover clustering, one node manages a specific SQL Server instance, a set of
disks and any associated services. To achieve high availability within a physical site, all
SharePoint-related databases should be placed on a SQL Server instance residing on a failover
cluster.
If the node hosting the SQL Server failover cluster application fails, another node within the
cluster will take on its responsibilities. This will also be the case if a SQL Server node hosting
the MOSS-related databases fails. The failover is automatic and seamless to end users and the
SharePoint application.
SharePoint and SQL Server database mirroring
SQL Server database mirroring increases protection and availability for SharePoint-related
databases by providing -- and maintaining -- a hot standby database on a separate instance of SQL
Server. This mirrored instance of SQL Server can live in either the same site as the principal
server or in another geographical location. Since the mirrored database is a copy of the principal
database, all changes made on the principal are automatically synchronized to the mirror.
With database mirroring, a SharePoint database will have continuous support, bolstering
operations by decreasing downtime and reducing data loss on a specific database.
Companies have a choice of the following three database mirroring operating modes:
- High availability with automatic failover. This mode, also known as the high
availability mode, provides maximum availability for a database mirroring session. A witness server
is necessary for automatic failover between the principal and mirrored database.
- High safety without automatic failover. This mode is very similar to the high
availability mode since it provides maximum availability through synchronous
operations. However, a witness server is not required as the failover between principal and
mirror database. This mode is also referred to as High Safety - Synchronous without a
Witness.
- High performance. This mode operates in an asynchronous fashion since transactions
commit on the principal without waiting for the mirror server to write the log to disk. While this
mode offers superior performance, it also increases the possibility of data loss. In this mode, you
must force the failover between principal and mirror.
Using mirroring to protect MOSS-related databases allows you to implement database mirroring
within or across a farm. From a performance, compatibility and support standpoint, the best
practice is to maintain one-to-one mappings of a principal server and mirror server.
The high availability mode is a practical alternative when the principal and mirror server are
within the same physical location, while the high performance mode is great for disaster recovery
scenarios -- providing the network has at least 1 millisecond latency and 1 GB between SQL Server
and MOSS front-end Web servers.
Overall, failover clustering should be used for local server redundancy within a site because a
failover cluster provides high availability for the entire SQL Server instance. This translates
into protection for all MOSS-related databases within the SQL Server instance. Furthermore, since
the failover is automatic and seamless to the SharePoint application, there are no interruptions or
downtime to worry about.
Database mirroring is configured at the database level, however, which means you must ensure all
databases affiliated with the MOSS high availability strategy are mirrored or you will not achieve
high availability and disaster recovery.
Take note: SharePoint options are not mirroring aware. Therefore, it is up to you to ensure
front-end Web servers are connected to the active database (the principal).
In addition, combining failover clustering and database mirroring provides maximum availability.
For example, all databases can be placed on a failover cluster within a site and then the databases
can be mirrored to another SQL Server instance in the disaster recovery site.
Ultimately, Microsoft SQL Server and Microsoft Office SharePoint Server have an intricate
relationship. In the end, it is up to you to select how high availability and disaster recovery
will be handled in your environment.
SQL
SERVER AND MICROSOFT SHAREPOINT
Part 1:
How SQL Server
recovery models impact MOSS
Part 2:
Achieving high availability and disaster recovery
Part 3:
The
effects of SQL Server components on MOSS
ABOUT THE AUTHORS
Ross Mistry is a principal consultant at Convergent Computing, a best-selling
author and a SQL Server MVP. He installs SQL Server, Active Directory, SharePoint and Exchange
Server software within Fortune 500 organizations in the Silicon Valley. His SQL Server and
SharePoint specialties include high availability, security, migrations and virtualization. You can
follow him on Twitter @RossMistry.
Shirmattie Seenarine is an independent technical writer with more than 10 years
of experience. She has contributed to many books, including Windows Server 2008 Unleashed, Exchange
Server 2007 Unleashed, SharePoint Server 2007 Unleashed and SQL Server 2008 Management and
Administration.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation