Em última análise, isso se resume a dinheiro. Há um custo para cada 'nove' no mítico 'cinco noves' (99,999% de disponibilidade, 5 minutos de tempo de inatividade por ano), e esse custo é bastante alto. Um sistema de disponibilidade de 99,999% custa em milhões de dólares, e tem que cobrir hardware, licenças de software, pessoal de especialistas altamente treinados, treinamento, procedimentos e assim por diante. Você deve considerar coisas como atualizações de sistema (patches de SO e de fornecedor), atualizações de aplicativos, vários procedimentos de manutenção, como reindexação de banco de dados, etc. etc.
Mas, para uma resposta muito simples, eu indicaria a Visão geral das soluções de alta disponibilidade :
Failover clustering provides high-availability support for an entire instance of SQL Server. A failover cluster is a combination of one or more nodes, or servers, with two or more shared disks. Applications are each installed into a Microsoft Cluster Service (MSCS) cluster group, known as a resource group. At any time, each resource group is owned by only one node in the cluster. The application service has a virtual name that is independent of the node names, and is referred to as the failover cluster instance name. An application can connect to the failover cluster instance by referencing the failover cluster instance name. The application does not have to know which node hosts the failover cluster instance.
Database mirroring is primarily a software solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database.
Like database mirroring, log shipping operates at the database level. You can use log shipping to maintain one or more warm standby databases for a corresponding production database that is referred to as the primary database. Standby databases are also referred to as secondary databases. Each secondary database is created by restoring a database backup of the primary database with no recovery, or with standby. Restoring with standby lets you use the resulting secondary database for limited reporting.
Replication uses a publish-subscribe model. This lets a primary server, referred to as the Publisher, distribute data to one or more secondary servers, or Subscribers. Replication enables real-time availability and scalability across these servers. It supports filtering to provide a subset of data at Subscribers, and also allows for partitioned updates. Subscribers are online and available for reporting or other functions, without query recovery. SQL Server offers three types of replication: snapshot, transactional, and merge. Transactional replication provides the lowest latency and is usually used for high availability.