3.8 Remote Data Replication
Both SAN and NAS array controllers
offer external data replication. This feature enables two or more array
controllers to synchronize data. Vendors provide either a real-time or
a point-in-time data migration framework.
Real-time frameworks replicate changes as they
are written to the array. This replication is performed synchronously
or asynchronously. Synchronous data replication is extremely sensitive
to latency and is therefore not used over great distances.
Synchronous Replication
When SQL Server writes changes to
either the data or the log, remember that both the user database and
the log files must be replicated; a synchronous replication system
writes the I/O to its battery-backed data cache. The array then sends
the I/O to its replication partner. The partner writes the I/O to its
own cache and then sends a successful acknowledgment to the source
array.
At this point, based on proprietary caching
algorithms, the storage arrays write the data from their cache to disk,
and a success acknowledgment is sent to the SQL Server. As you can see,
this process adds latency to the entire write process. Distance
directly translates to latency. If the systems are asked to handle too
much replicated data, or are placed too far apart, the database will
slow considerably.
Asynchronous Replication
Rather than wait for a single change to
be committed to both arrays, asynchronous replication sends a stream of
changes between the arrays. As I/O is successfully written to cache,
the source array returns a success acknowledgment to SQL Server. This
way, changes are written without slowing the SQL Server processes.
The primary array sends changes to the remote
array in a group. The remote array receives the group of changes,
acknowledges the successful receipt, and then writes the changes to
physical media. Each vendor offering asynchronous remote replication
has a property method for ensuring successful delivery and recovering
from communication issues between the arrays.
From a BCDR prospective, the drawback to
implementing asynchronous replication is the potential for some data
loss. Because the write I/O is instantly acknowledged on the source
array, some amount of data is always in transit between the arrays.
Most unplanned failover events will result in transit data being lost.
Due to the design of SQL server lost data does not mean that the remote
database is corrupt.
NOTE
All of
these replication technologies must provide the capability to keep I/O
exchanges intact. If only part of a write is delivered and written to a
log file or data page, it is considered torn. A
torn I/O is not recoverable by SQL Server DBCC. For this reason, SQL
Server requires storage arrays to maintain atomic I/O. The SQL Server
team offers a compliance program called SQL Server I/O Reliability.
This program requires storage vendors to guarantee that they are fully
compliant with all SQL Server data integrity standards.
Several storage vendors combine point-in-time
technology with remote replication. These features enable the
administrator to take a clone or snapshot of the user databases (using
VDI to create an application-consistent data copy if desired) and send
the data to a remote array.
As with asynchronous replication, point-in-time
remote replication offers the advantage of low server impact. Remotely
sending a snapshot enables you to specifically control the potential
for data loss. If you set the snap interval for five minutes, you can
be reasonably certain that in the event of unplanned failure you will
lose no more than five minutes of changes. Identifying an exact amount
of expected data loss can offer huge advantages when negotiating the
parameters of BCDR with the business.
3.9 Windows Failover Clustering
Many SQL Server installations utilize
Windows Failover Clustering to form high-availability SQL Server
clusters. These systems create a virtual SQL Server that is hosted by
an active server and at least one passive server. SQL Server 2008 and
2008 R2 failover clusters supported only shared storage failover
clustering. The SAN storage array allows all SQL Server clusters to
access the same physical storage and move that storage between nodes.
SQL Server failover clusters are based on the
concept of “shared nothing”; the SQL Server instance (or instances) can
live on only one active server. SQL Server places an active lock on log
and data files, preventing other servers or programs from accessing the
files. This is incorporated to prevent data changes that SQL Server
would have no way to detect. In addition to file locks, the failover
cluster also actively prevents other systems from using the storage
volumes by using SCSI reservation commands.
Failover clustering can be set to automatically
fail the instance over to a standby server when a failure occurs. SQL
Server failover clusters also support the use of geo-clustering.
A geo-cluster uses storage replication to ensure that remote data is
in-sync with the source data. Storage vendors provide a proprietary
cluster resource dynamic link library (DLL) that facilitates
cluster-to-storage-array communication.
The use of a resource DLL enables a rapid
failover between sites with little or no administrator involvement.
Traditional failover systems require the interaction of many teams.
Returning to our example, assume that Widget Company has just
implemented a local SQL Server with a remote failover system.
The system in use by Widget uses traditional
storage replication. When the Widget DBAs try to execute a failover
they first need to contact the storage administrators so they can run
failover scripts on the storage arrays. Once the storage is failed to
the remote site, the DBAs can bring the database online. Because the
server name is different, the DBAs need to reconfigure the database to
do that. Now the application owners need to point all the middle-tier
systems to the new server.
Even when the failover is planned it is extremely
resource intensive to implement. The use of a geo-cluster greatly
simplifies the failover process. The failover is implemented
automatically whenever the SQL Server geo-cluster is failed over to a
remote node. Unfortunately, SQL Server 2008 and 2008R2 only supported
geo-clustering when both servers shared the same IP subnet.
This networking technology is called a stretch virtual local area network, or stretch VLAN.
The stretch VLAN often requires that network engineers implement
complex networking technologies. SQL Server 2012 solves this through
AlwaysOn Failover Cluster Instances. This new feature enables each SQL
Server to utilize an IP address that is not tied to the same IP subnet
as the other hosts.
NOTE
Multi-subnet SQL Server failover cluster network names enable the RegisterAllProvidersIP property.
This provides all the IP addresses that SQL Server is configured to
use. It is important that newer SQL Server client drivers (such as the
SQL Server Native Client) be utilized, as they support this
configuration. The use of older SQL Server client drivers requires
advanced configuration.
3.10 SQL Server AlwaysOn Availability Groups
The configuration and setup of AlwaysOn
Availability Groups is beyond the scope of this article . From a storage
perspective, Availability Groups offer a new, server-based method for
replicating data that is application centric rather than platform
centric. As discussed earlier, storage systems can use point-in-time
data copies to facilitate data backup and performance isolation.
Deciding between application based failover and hardware based failover
is an architecture choice.
Earlier versions of SQL Server utilized log
shipping, and later SQL Server mirroring, to keep standby SQL Servers
updated with information. In the case of both log shipping and storage
replication, the remote database must be taken offline prior to data
synchronization. Availability Groups enable the use of near-real-time
readable secondary database servers.
The read-only data copy can facilitate reporting
offload or even backup. In addition, Availability Groups support
synchronous or asynchronous data replication and also multiple
secondary servers. You can configure one secondary as a reporting
system that is hosted locally, and a second server can be hosted
remotely, thus providing remote BCDR.
From a storage perspective, note the following
caveats: Each remote secondary server needs as much storage capacity
(and usually performance) as the primary database. In addition, it is
critical that your network infrastructure is designed to handle the
increased network traffic that can be generated by replicating data
locally and remotely. Finally, all your servers must have enough
performance available to handle the replication. If your server is
already processor, storage, or memory bound, you are going to make
these conditions worse. In these cases it is often advantageous to
enable storage-based replication. Think of AlwaysOn Availability Groups
as a powerful new tool that now enhances your SQL Server toolkit.
3.11 Risk Mitigation Planning
All of the replication and backup
technologies described here are designed to mitigate risk. To properly
define the specific procedures and technologies that will be used in
your BCDR strategy, you need to decide how soon you need the system
online and how much data your business is willing to lose in the
process.
The projected time that it will take to bring a failed system online is called the recovery time objective (RTO). The estimated amount of original data that may be lost while the failover is being executed is called the recovery point objective (RPO). When designing a recovery plan it is important to communicate clear RTO and RPO objectives.
Ensure that the recovery objectives will meet
business requirements. Be aware that systems that provide a rapid
failover time with little or no data loss are often extremely
expensive. A good rule of thumb is that the shorter the downtime and
the lower the expected data loss, the more the system will cost.
Let’s look at an example failover system
that has an RTO of 1 hour and an RPO of 10 minutes. This imaginary
system is going to cost $10,000 and will require DBAs to bring up the
remote system within one hour. If we enhance this example system to
automatic failover it will reduce our RTO to 10 minutes, and we should
not lose any data with an RPO of 0. Unfortunately, this system will
cost us half a million dollars.
