Understanding the Threats to SharePoint Data
A SharePoint document
management and collaboration environment is a mission-critical service
for many organizations. In many cases, a SharePoint environment contains
the physical representation of the intellectual property of an
organization, and it is subsequently critical that the data in a
SharePoint environment is secure and reliable.
Modern threats to
SharePoint data integrity take many forms, but organizations can’t
afford to ignore them. Some common issues are as follows:
Data redundancy—
The ability to have an up-to-date copy of data in more than location is
a critical requirement because it provides a data redundancy solution
for hardware and site disaster scenarios or outages. This type of
requirement is provided for SharePoint 2010 with SQL database mirroring.
Data high availability—
The ability to eliminate single points of failure in an environment
that houses mission-critical data is key. High availability in
SharePoint can take multiple forms, but often includes network load
balancing used together with either SQL clustering or SQL database
mirroring.
Data integrity—
The ability to control the integrity and security of data, in transit,
at rest, and when backed up, is critical. Technologies such as SQL TDE
can help to secure data in storage and when backed up, providing better
overall data integrity.
Data leakage—
The ability to control what happens to sensitive data after it has been
accessed is becoming more and more critical. Technologies such as AD
RMS can be used to provide for this much-needed functionality, as they
restrict the ability of users to print, copy/paste, or send data outside
of a company.
SQL Server Database Mirroring for SharePoint Farms
Introduced in SQL Server
2005 Service Pack 1, database mirroring is a software solution that
delivers high availability/database redundancy of SQL databases,
including SharePoint databases. SQL mirroring is highly valuable for
SharePoint environments because it provides the promise of having an
always-available constant replica of SharePoint databases on a different
server and being able to fail over to that server, either automatically
or manually, as needed.
There are two primary
partners in a database mirroring session: the principal server and the
mirror server. Essentially, database mirroring works by maintaining a
copy of the same database or databases on both partners. This is
accomplished by streaming the active transactional log records from the
principal server to the mirror server, which in turn applies the log
records to the mirror database. In comparison to failover clustering,
database mirroring works on a per-database basis, and provides high
availability and data protection for both storage and hardware failures.
In addition to the two partners involved in a database mirroring
session, a third server instance, known as the witness server, can be added to provide automatic failover by verifying whether the principal server is up and functioning.
Utilizing SQL database mirroring
for a SharePoint environment can have the advantage of having a second,
fully complete copy of all SharePoint content automatically replicated
to a secondary location. This is the equivalent of running a constant
backup of SharePoint data, and has the added advantage of allowing for
instant failover to a secondary SQL server in the event of a failure.
Indeed, this version of SharePoint has been written to be
“mirror-aware,” and all databases can be configured with a secondary SQL
server instance chosen to allow for mirroring failover scenarios.
Understanding Operating Modes
There are essentially three operating modes for database mirroring, as follows:
High-safety mode (synchronous)—
After a database mirroring session is initiated under high-safety mode,
the mirror server database synchronizes with the principal server
database and then writes the logs to disk. After the logs have been
written to disk, all transactions are committed on both servers.
Although this mode guarantees no data loss between the two servers, it
does come with the expense of increased transaction latency.
High-availability mode (synchronous)—
Also referred to as high-safety mode with automatic failover, this
operating mode is made available only with the presence of a witness
server. This mode is the recommended operating mode for database
mirroring within a SharePoint farm because of its ability to provide
increased high availability through automatic failover. Similar to
high-safety mode, the mirror server synchronizes the mirror database
with the principal database and proceeds by committing transaction logs
on both servers after the mirror server has written the logs to disk.
High-performance mode (asynchronous)—
Running under asynchronous operation, this operating mode provides an
increased level of performance by sending logs from the principal server
to the mirror server and then immediately committing the transactions
on the principal server without waiting on the mirror server to write
the logs to disk. By minimizing the latency of committed transactions,
this mode increases the overall performance of the principal server.
However, it also increases the risk of data loss between the two
partners. Asynchronous mirroring is available only with the Enterprise
edition of SQL Server, whereas the other two modes are available in
either the Standard or Enterprise editions.
Although it is possible to
switch between operating modes once a database mirroring session is
configured, it is essential that administrators understand each
operating mode and their respective advantages and disadvantages to
successfully meet the availability requirements of their organization.
Understanding Transaction Safety Levels
Transaction
safety levels work hand in hand with operating modes. Based on the
configured operating mode, the transaction safety level will either be
set to FULL or OFF. In turn, if the database mirroring session is
configured using Transact-SQL statements instead of SQL Server
Management Studio, the transaction safety level determines the operating
mode. Essentially, if the operating mode is running under synchronous
transfer mode, the transaction safety level will be FULL, whereas if the
transfer mode is running asynchronously, the transaction safety level
will be OFF.
When using SQL Server
Management Studio to configure a database mirroring session, the
transaction safety level will automatically be set to FULL if the
partner and mirror servers operate in either high-safety or
high-availability mode. If the two partners are configured for
high-performance mode, the transaction safety level will automatically
be set to OFF. If Transact-SQL statements are used to configure a
database mirroring session, the SAFETY property in the ALTER DATABASE
statement should be set to either FULL or OFF, depending on the desired
operating mode. The following statement is an example of setting the
transaction level safety to FULL:
ALTER DATABASE WSS_Content SET SAFETY FULL;
Table 1 lists the different operating modes and their respective transaction safety levels.
Table 1. Operating Modes
| Operating Mode | Transaction Safety Level | Transfer Mode |
|---|
| High Safety | FULL | Synchronous |
| High Performance | OFF | Asynchronous |
| High Availability | FULL | Synchronous |
