SQL Server 2008 offers a variety of options to keep your databases available in the event of hardware or network failures

Failover Clustering

SQL Server 2008 Failover Clustering allows you to install multiple servers and nodes underneath a single instance. If one server fails, another server can automatically take over handling connections and servicing requests. SQL Server 2008 Failover Clustering is implemented on top of Windows Clustering, acting as a cluster-aware application.

Each SQL Server instance installed into a cluster requires a unique IP address and a unique network name registered in DNS. Each SQL Server clustered instance is configured with a dedicated set of drive letters. At any given time, the drives are mounted to a single node in the cluster - no other node can access the drives. The SQL Server service accounts on each instance need to be domain accounts to ensure that each server sees the same SID for the service accounts.

Failover clustering continually monitors the health of each instance of SQL Server in a failover cluster, using two tests:

Ping Test

Simple IP Ping to establish that the server is responding to network requests

IsAlive Test

Involves connecting to the SQL Server and executing 'SELECT @@SERVERNAME', and checking for a valid resultset

If either test fails, the cluster initiates a failover as follows:

1. An attempt is made to restart the SQL Server service on the failed node
2. If the restart attempt fails to respond immeadiately, failover to another node is initiated
3. The network name registration for the SQL Server is removed from DNS
4. The SQL Server IP address is bound to the NIC on the secondary node
5. Disks associated to the instance are mounted on the secondary node
6. The network name for the SQL Server instance is re-registered in DNS
7. SQL Server service is started on the new node, followed by SQL Server Agent and Full-Text Indexing services
8. During the restart, active transactions are rolled-back

Database Mirroring

Clustering only maintains a single copy of the database on shared disks. Database mirroring maintains a second copy of the database on another SQL Server instance, thus protecting from both server and storage failures. Mirroring does not work for databases with FILESTREAM filegroups

Database Mirroring maintains and exact copy of the database on the mirror server and thus is only possible if both databases are configured to use the Full Recovery model

In mirrored configurations, servers are assigned one of three roles:

1. Principal: source of all transactions in the mirroring session. Acts as the primary server for connections
2. Mirror: receives and replays all transactions from principal server. The database is in continuous recovery mode and does not allow connections
3. Witness: an optional role, which acts as an arbiter to trigger automatic failover

The Principal and Mirror roles are assigned at the database level. The optional Witness role is assigned at the instance level, and a Witness instance can act as a witness for multiple mirrored database pairs. The sys.database_mirroring_witnesses catalogue stores information on mirrors witnessed by an instance.

Database mirroring can be configured for one of three operating modes:

High Availability

Requires a witness server. Involves increased processing times for transactions, which must be committed on both databases before processing is allowed to continue. High availability works as follows:

1. All transactions are written to memory buffers
2. Memory buffer contents are written to the transaction log
3. Database mirroring transfers transaction log rows to the mirror
4. When a commit is issued on the principal, the transaction is first committed on the mirror database
5. A commit acknowledgement is sent from the mirror to the principal
6. On receipt of the commit acknowledgement, the principal then commits the transactions
7. Principal then sends a commit acknowledgement to the application, allowing processing to continue

High Performance

Does not use a Witness server, therefore failover process is manual. The mirror database acts as a 'warm' standby. Transactions are committed on the principal and acknowledgement sent to the application. A separate, independant process is responsible for sending those transactions to the mirror for processing. Thus the mirror lags behind the principal, but application performance is improved

High Safety

Transactions are synchronously transferred and committed on the mirror server first. No Witness server is involved in this configuration, therefore failover must be initiated manually.

Communications for database mirroring is carried out over TCP endpoints configured at the instance level:

-- On Principal
CREATE ENDPOINT Mirroring
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
  FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
	ENCRYPTION = REQUIRED ALGORITHM RC4)

-- On Mirror
CREATE ENDPOINT Mirroring
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
  FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
	ENCRYPTION = REQUIRED ALGORITHM RC4)

-- On Witness
CREATE ENDPOINT Mirroring
  STATE = STARTED
  AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
  FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE,
	ENCRYPTION = REQUIRED ALGORITHM RC4)

Once endpoints have been configured, the mirror database is initialised by restoring a backup from the principal with NORECOVERY. Then issue the following commands:

-- On Mirror
ALTER DATABASE db_name SET PARTNER = 'TCP://principal_server:port_number'

-- On Principal
ALTER DATABASE db_name SET PARTNER = 'TCP://mirror_server:port_number'
ALTER DATABASE db_name SET WITNESS = 'TCP://witness_server:port_number'

Log Shipping

Log Shipping involves continually copying and applying transaction logs from a primary to one or more secondary servers. Secondary servers can be configured in STANDBY or NORECOVERY mode. In STANDBY mode the secondary server will accept connections and service SELECT requests. An optional monitor server can also be configured to send alerts when log shipping falls out of sync.

Log shipping is configured as follows:

1. Shares are created on primary and secondary servers
2. Jobs are created to backup the primary, copy backup files to secondaries, and restore the backups on the secondaries
3. A full backup is created on primary and restored to the secondaries in either STANDBY or NORECOVERY mode
4. All subsequent transactions are then restored to the secondaries
5. Jobs from step 2 are started

Because Log Shipping maintains copies via transaction logs, the databases must be in FULL RECOVERY model. Log Shipping will result in a copy of the database that lags behind the primary server, determined by the scheduling of the backup-copy-restore cycle.

Log Shipping can be configured via SQL Server Management Studio, via the properties tab of the desired database.

Replication

Replication is principally designed for data distribution, but can also be configured to improve availability. Replication occurs via a set of executables known as replication agents:

• Snapshot Agent (snapshot.exe) extracts the schema and data to be replicated
• Log Reader Agent (logread.exe) extracts committed transactions from the publisher that need to be replicated. Transactions are then written to the distribution database in the correct order
• Distribution Agent (distrib.exe) applies each snapshot to subscribers, and all transactions written to distribution database in correct order
• Merge Agent (replmerg.exe) applies snapshot generated when subscriber is initialised. Exchanges transactions between publisher and subscriber
• Queue Reader Agent (qrdrsvc.exe) is only used if the 'queued updating' option is selected for transactional or snapshot replication. Transfers the queue on subscriber to publisher

The specific combination of replication agents used is dependant on the type of replication configured:

Snapshot Replication

Snapshots the published data at regular intervals (snapshot.exe) and then collects and applies snapshot to each subscriber (distrib.exe)

Transactional Replication

Begins by applying an initial snapshot to the subscriber, and then uses logread.exe and distrib.exe to apply transactions

Merge Replication

Primarily designed to enable offline copies of a database to be used. Uses replmerg.exe to synchronise changes between the publisher and subscriber, when subscriber is brought back online

When configuring replication, a catalogue of published items is selected, and each item can be filtered allowing you to replicate specific objects and values

Replication can be configured via a dedicated node in SQL Server Management Studio