Creating fault-tolerant SQL Server installations
Danielle Ruest and Nelson Ruest, Contributors
Organizations banking on SQL Server to protect and manage their internal data are in for a treat
when they upgrade to SQL Server 2008. That's because the latest version of SQL Server provides a
host of new features geared toward data protection and database fault tolerance, with both
traditional and new or updated fault tolerance configurations now available.
Traditional fault tolerance configurations for SQL Server rely on the
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
This was first published in June 2009
Windows
Server Failover Clustering service. Windows Server supports two types of failover clusters:
single-site and multi-site. In a
single-site
cluster, you create a server configuration that includes up to 16 nodes all linked to the same
shared storage container. Shared storage must be in the form of a storage area network (SAN), iSCSI
targets or both.
In the simplest single-site cluster configuration, one node runs the SQL Server service and the
other waits in stand-by to take over the service in the event of a hardware, operating system or
application failure on the original node. This is an active-passive cluster, meaning one
node is active while the other is passive.
When your hardware includes enough spare resources, you can create active-active clusters
where each node runs its own SQL Server implementation, but also acts as a stand-by node for the
others. While this configuration offers fault tolerance for the SQL Server service, it does nothing
for SQL Server data. If the shared storage container fails, then all nodes will lose data.
In a multi-site
failover cluster configuration, cluster nodes do not rely on shared storage because each node
is located in a different site. In many cases, organizations rely on direct-attached storage (DAS)
to create the cluster. Note that you can also use SANs, iSCSI or both to provide additional storage
protection. However, since all data containers must include the same data in order to support a
failover, you must use a replication engine to ensure that all copies of the data are in synch at
all times. In fact, a third-party replication tool is needed to do so since SQL Server does not
include the ability to provide this type of real-time replication service.
 |
|
|
|
|
With database mirroring you can apply fault tolerance at the
database level. [It] doesn't require any special hardware or software. Databases can be
mirrored from one installation to any other.
, |
|
|
|
|
|
|
|
|
|
Failover clusters work at the instance level in SQL Server. Each time you create a SQL Server
failover cluster, you create a fault tolerant instance of SQL. Each of the databases you create
within the instance then automatically inherit the fault tolerance you prepared for the instance.
Failover clusters also require either custom hardware (as in the case of the single-site cluster)
or third-party tools (as with multi-site clusters). Finally, failover clusters only protect
databases that are contained within clustered SQL Server instances.
This is one reason why the Microsoft SQL Server team enhanced the database mirroring engine in
SQL Server 2008. With database mirroring you can apply fault tolerance at the database level.
What's even better is that database mirroring does not require any special hardware or software
tools. Databases can be mirrored from one SQL Server installation to any other. Just keep in mind
that you should use the same SQL Server versions and editions to keep it as simple as possible. In
addition, the mirrored database can be used to provide additional functionality such as reporting
services. Finally, database mirroring offers the same type of fault tolerance as failover
clustering, since you can configure the mirrored database to automatically pick up the service
should the primary database fail for any reason.
Database mirroring is the poor man's failover clustering, and offers unprecedented fault
tolerance to organizations of all sizes. Larger organizations that want instant-on fault tolerance
and data protection can even combine failover clustering with database mirroring to create more
comprehensive fault-tolerant installations.
You can even combine physical and virtual machines in any of these fault tolerance scenarios,
running a physical machine as the main production system and using virtual machines as the backups.
This offers truly low-cost fault tolerance for SQL Server installations. If data availability is
something that is important to your organization, then take the time to examine these options to
determine which best suits your needs.
Creating fault-tolerant virtual installations
More and more organizations are moving their physical workloads to virtual machines to reap the
benefits of a consolidated datacenter. Database servers are no exception to this process since they
can operate quite well in a virtual environment. In fact, the Microsoft SQL Server Support Team has
outlined specific strategies for
virtualizing SQL Server installations. Both SQL Server 2005 and 2008 are supported on Hyper-V
and other, non-Microsoft hardware virtualization technologies.
One of the great advantages of virtualizing SQL Server installations is that it allows you to
create more cost-effective fault-tolerant installations. For example, creating a two-node failover
cluster in the virtual realm really means creating two virtual machines and linking them to shared
storage, usually in the form of an iSCSI target which can be either located on a SAN. For those who
do not have the means to obtain a SAN, the target can be created on a file server using low-cost
software such as Rocket Division Software's StarWind solution. Doing this creates a guest
failover cluster — one that operates at the virtual machine layer.
Guest clusters are sometimes better than host server clusters because they allow the application
contained within the guest to be aware of potential failures. In some cases, this can provide
better data consistency within the application.
While guest clusters are useful, host clusters are an absolute -- especially when 20-plus
virtual machines are running on a single host. You simply can't afford to have all of these
machines fail in the event of a hardware failure on the host. Using a host cluster will
automatically move the VMs from the failing host to another host with spare resources. In addition,
a host cluster is an absolute must for SQL Server since the Microsoft Customer Support Services
team for SQL Server does not support guest clustering.
If you cannot cluster SQL Server in the virtual layer, then one of the only ways to provide
fault tolerance for your virtual instance of SQL Server is through host failover clustering. This
means all of your virtual SQL Server installations must be standalone. It also means that in the
event of a hardware failure, the users connected to the virtual instance of SQL Server will
experience a loss of productivity since the VM fails and is restarted on another host.
The other means for providing high availability of a virtual SQL Server installation is once
again database mirroring. As mentioned before, database mirroring does not require any special
hardware or software tools, and databases can be mirrored from one SQL Server virtual machine to
another SQL Server VM. In the event of a host server failure or even a VM failure, users will
automatically be redirected to the mirror database with little or no service interruption. Using
this strategy, you can provide fault tolerance to your virtual installation of SQL Server in a
supported configuration. As you can see, database mirroring is an integral part of SQL Server and
has been since SQL Server 2005.
If you decide to use database mirroring, then you should not make the SQL Server virtual
machine highly available through a host cluster, otherwise the host cluster will restart the VM in
the case of a failure. This could cause two versions of the same database to be live on the
network, something you'll want to avoid at all costs. Instead, configure the SQL Server virtual
machine as a standalone VM without fault tolerance, then use the SQL Server management tools to
configure database mirroring for the most precious databases you run. This way, your virtual
instances of SQL Server will be available on a constant basis and your users will never know the
difference.
SQL
SERVER AND MICROSOFT HYPER-V
Part 1:
Creating fault tolerant installations
Part 2:
Maintaining
high availability
Part 3:
Protecting
virtual databases
Part 4:
Creating
virtual appliances
Part 5:
Deploying
virtual appliances
ABOUT THE AUTHORS
Danielle Ruest and Nelson Ruest are technology futurists focused on datacenter
optimization and continuous service availability. They are authors of multiple books, notably "Training Kit 70-652: Configuring Windows Server Virtualization with Hyper-V"
published by Microsoft Press and "Virtualization, A Beginner's Guide" published by McGraw-Hill Osborne. For more
tips, write to them at [email protected].
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