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 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 s...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
tandby 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.
[IMAGE]
[IMAGE] SQL SERVER AND MICROSOFT SHAREPOINT
[IMAGE] Part 1: How SQL Server recovery models impact MOSS
[IMAGE] Part 2: Achieving high availability and disaster recovery
[IMAGE] Part 3: The effects of SQL Server components on MOSS
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.
