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 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....
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
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.
[IMAGE]
[IMAGE] SQL SERVER AND MICROSOFT HYPER-V
[IMAGE] Part 1: Creating fault tolerant installations
[IMAGE] Part 2: Maintaining high availability
[IMAGE] Part 3: Protecting virtual databases
[IMAGE] Part 4: Creating virtual appliances
[IMAGE] Part 5: Deploying virtual appliances
[IMAGE][IMAGE]Danielle Ruest and Nelson Ruest are IT professionals specializing in systems administration, migration planning, software management and architecture design. Danielle is Microsoft MVP in Virtualization and Nelson is Microsoft MVP in Windows Server. They are authors of multiple books, including the free Definitive Guide to Vista Migration for Realtime Publishers and Windows Server 2008: The Complete Reference for McGraw-Hill Osborne. For more tips, write to them at info@reso-net.com.
