Solution provider takeaway: Solution providers can choose among four high-availability options in SQL Server 2008.
Databases are the backbone of every organization. They're used to run Internet and intranet applications, line-of-business systems, human resources applications and business intelligence solutions. Because databases are so critical to an organization's success, and because SQL Server's market share is rising, SQL Server consultants are being engaged more frequently as their customers design and implement high-availability database solutions.
Solution providers and customers are facing the challenge of trying to understand the high-availability options offered with SQL Server 2008 and then selecting the correct alternative that meets the customer's goals. These options aren't new to SQL Server 2008, but some of them have been enhanced in the latest version.
High-availability options in SQL Server 2008 include failover clustering, database mirroring, log shipping and replication. Let's examine these options and how they positively affect a customer's high-availability and disaster recovery needs.
Failover clusters
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 the Enterprise Edition of the Windows Server operating system. A failover cluster is formed by having at least two or more servers, also known as nodes, connect to shared disk resources. 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 within the cluster. If the node hosting the SQL Server failover cluster application fails, another node within the cluster will take on its responsibilities. The failover process is automatic and does not affect clients or applications. With SQL Server failover clustering specifically, only one node manages one particular SQL Server instance, set of disks and associated services at any given time.
SQL Server components that are "cluster aware" include the database engine, full-text search, replication and analysis services. Unfortunately, SQL Server's integration services and reporting services cannot be clustered. However, a scale-out architecture is supported when designing and implementing high availability for the reporting services.
Up to 16 nodes can be configured within a failover cluster when running SQL Server 2008 Enterprise Edition on Windows Server 2008 Enterprise or Data Center Edition. It is good to note that an organization can also save a tremendous amount of money on Enterprise licensing since two-node failover clustering is supported with the Standard Edition of SQL Server 2008.
Database mirroring
Database mirroring, the second high-availability option in SQL Server 2008, offers increased database protection by providing and maintaining a hot standby database on another instance of SQL Server 2008. Note that the mirror database is an exact copy of the principal database, and all changes made on the principal are automatically synchronized to the mirror. With database mirroring, your customers will have continuous support, bolstering operations by decreasing downtime and reducing data loss on a specific database.
The usefulness of database mirroring is best witnessed when a failure takes place on a primary database. In this situation, the standby database becomes active and clients are redirected without any data loss or downtime to the organization.
Database mirroring is commonly used to meet disaster recovery requirements and therefore should not be recognized only as an availability mechanism for a local site. When database mirroring becomes an integral part of an organization's disaster recovery plan, a hot or warm standby database is typically placed in a physical location other than the primary active database. For example, the principal database could reside in a Toronto data center and the mirror database could reside in the disaster recovery site in San Francisco.
The principal database handles client activity, whereas the mirror database receives continuous transaction log changes through a dedicated and secure TCP endpoint. This process keeps the mirror database up-to-date and ready to take on client operations in the event of a failure. Depending on the configuration/operating mode, database mirroring can be configured for either synchronous or asynchronous operations.
Database mirroring is supported in both SQL Server 2008 Standard and Enterprise editions. The Enterprise Edition offers Full mode, whereas the Standard Edition only offers Safety Full mode.
Log shipping
Similar to database mirroring, log shipping offers increased database availability and database protection by maintaining a warm standby database on another instance of SQL Server 2008. Unlike database mirroring, log shipping can maintain one or more warm standby databases, and the standby database can be used for reporting purposes. However, log shipping failover is not seamless or automatic -- you must perform several manual steps, using Transact-SQL, to successfully complete a failover. Clients and applications using the database must be manually redirected from the primary database to the secondary database after manually bringing the secondary database online.
Log shipping is supported on both the Enterprise and Standard editions.
Replication
In SQL Server, there are many types of replication, allowing organizations to copy databases or portions of the database, known as articles, from one SQL Server instance to another SQL Server instance. Replication can be used for data distribution, synchronization, fault tolerance, disaster recovery, load balancing, reporting or testing. Replication uses a publish-subscribe model; in this model, a primary server, referred to as the publisher, distributes data to one or more secondary servers, or subscribers.
The high-availability replication option in SQL Server is called peer-to-peer transactional replication. When using peer-to-peer transactional replication, all participants in the replication topology are peers. There isn't a hierarchy as with normal transactional replication, and data can be updated on all databases configured in the peer-to-peer replication topology. Therefore, one of the advantages of peer-to-peer replication is that if one of the peers is unavailable, traffic can be redirected to another peer as a form of high availability. In addition, because all peers are updatable and support bidirectional replication, this model can be used for load balancing clients across multiple SQL Server instances.
Peer-to-peer replication is supported on both the Enterprise and Standard editions.
Choosing a high-availability option
In deciding which of the four options make sense for a customer, it's important to consider both how effective the approach is as well as how much effort is involved to get it set up. To get started, you should first establish your customer's SQL Server availability goals and service-level agreement (SLA) requirements. For example, are your customers looking for 99.99% uptime? Do they need to protect just a database or the whole SQL Server instance? Do they require automatic or manual failover capabilities? And do they need to protect against a site going offline? Each of these requirements will dictate which solution to choose.
Clearly, failover clustering is the best choice to reduce system downtime and provide higher application availability for the whole instance of SQL Server within a site; however, a failover cluster does not protect against disk failure and requires a SAN. Database mirroring is a great choice if there is a need to provide high availability with or without automatic failover on one or more databases within an instance of SQL Server.
Unlike failover clustering, it does not require a SAN, and the disks are not a single point of failure -- the data is stored twice, once on the principal and again on the mirror. If the customer is trying to implement a high-availability or disaster recovery solution with minimal costs and wants multiple standby databases, log shipping would be a perfect fit, since the standby recovery database can span one or more geographical data centers. Replication can be used if there is a need to distribute data from one SQL Server instance to another or if load balancing will be used between two identical read-only databases.
When it comes to implementation, SQL Server failover clustering requires intimate knowledge of Windows failover clustering and configuring shared storage systems (SAN and/or NAS) based on a Windows failover clustering model.
Configuration of log shipping, on the other hand, is relatively easy. By using a wizard included in SQL Server 2008, log shipping can be implemented within hours, unlike the others, any of which could take a few weeks depending on the scenario.
Implementing database mirroring between two physical sites could take time to implement, especially if there is a tremendous amount of data to synchronize and the bandwidth between the two sites is slow and/or has excessive latency.
Peer-to-peer transactional replication is probably the most complex of the alternatives to implement -- specifically when it comes down to troubleshooting or management. You have to understand the replication terminologies such as publisher, distributor, subscriber and articles, and you have to understand how to implement each of these roles onto a SQL Server instance.
Finally, it's worth noting that you will most likely want to combine SQL Server high-availability options for maximum protection against loss of a physical SQL Server database or a SQL Server instance. It is common to use failover clustering within a site and then add database mirroring on top to satisfy disaster recovery requirements to another physical site. Clearly, there isn't one solution that addresses every customer's SQL Server high-availability requirements. But by understanding the alternatives and combining the technologies, it's possible to take care of their high-availability needs.
About the author
Ross Mistry is a partner and principal consultant at Convergent Computing, located in the San Francisco Bay area. He is a co-author of SQL Server 2005 Management & Administration and Windows Server 2008 Unleashed. In addition, he was contributing writer on Windows Server 2008 Hyper-V Unleashed, Exchange Server 2007 Unleashed and SharePoint Server 2007 Unleashed. Ross frequently speaks at international conferences such as SQL Server PASS and Dev Connections. He is currently finalizing his latest title, SQL Server 2008 Management & Administration, which is scheduled for release in Fall 2008. Ross blogs at Network World.
