In this FAQ guide, SQL Server expert Ross Mistry answers frequently asked questions about high availability in SQL Server. Learn how you can use failover clustering, database mirroring, log shipping and replication to offer high-availability services.
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
Listen to Ross Mistry answer other frequently asked questions in this podcast on SQL Server 2008 high-availability services.• What are the high-availability options in SQL Server 2008?
• Does SQL Server failover clustering require special hardware?
• Which editions of SQL Server 2008 do I need for high availability?
• Can I combine SQL Server high-availability technologies?
SQL Server 2008 has four technologies that can provide high availability: failover clustering, database mirroring, log shipping and replication. Some of them, such as failover clustering, protect the full instance of SQL Server. Others, such as log shipping and database mirroring, protect at the database level. In addition, some of the high-availability technologies offer automatic failover, which is seamless to the client and application. Most SQL Server experts believe that replication can be used for high availability, but it is best used for distributing data between SQL Server instances or systems.Does SQL Server failover clustering require special hardware?
Yes, the hardware in a SQL Server failover cluster must be identical in each associated node. This includes driver versions, service packs, hot fixes, memory and processors. Moreover, the hardware and all devices associated with the failover cluster must be certified by Microsoft. Shared storage and the enterprise edition of Windows are also required. You can leverage the enterprise editions of Windows Server 2003 or Windows Server 2008 to add value for customers. SQL Server 2008 Standard Edition supports up to two nodes within a cluster, but SQL Server 2008 Enterprise can support up to 16 nodes if customers are using Windows Server 2008 Enterprise or eight nodes if they're using Windows Server 2003 Enterprise.Which editions of SQL Server 2008 do I need for high availability?
It all depends on what technology you plan on using at the customer site. As I just mentioned, SQL Server 2008 Standard Edition supports only two nodes for failover clustering, and SQL Server 2008 Enterprise Edition is required if you want to cluster up to 16 nodes when running on Windows Server 2008 Enterprise Edition. Log shipping and database mirroring are supported in SQL Server 2008 Standard Edition, but the full mode of database mirroring is only included in the enterprise edition. Microsoft's SQL Server Developer Center has a webpage that lists all of the features supported by the different editions of SQL Server 2008.Can I combine SQL Server high-availability technologies?
Yes, it is possible to combine SQL Server high-availability options for customers. It can give them maximum protection against the loss of a physical SQL Server database or instance. Using failover clustering within a site is quite common along with adding database mirroring to satisfy disaster recovery requirements. 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 needs.
About the expert
Ross Mistry is a principal consultant at Convergent Computing, bestselling author and SQL Server MVP. He focuses on implementing SQL Server, Active Directory and Exchange solutions for fortune 500 organizations residing in the Silicon Valley. Ross' SQL Server specialties include high availability, security, migrations and virtualization. His recent books include SQL Server 2008 Management and Administration and Windows Server 2008 Unleashed. Ross frequently writes for TechTarget.com and speaks at international technology conferences around the world. His blog can be found at NetworkWorld.com.