High Availability Solutions in SQL Server 2008:
High-availability solutions available in SQL Server 2008 improve the availability of servers or databases. High-availability solutions are for masking the hardware or software failures and maintains the availability of applications so that the perceived downtime for users is minimized.
There are several options for High Availability both on server level or database level.
High-availability options include the following:
• Failover clustering: provides high-availability support for an entire sever/instance level for SQL Server. A failover cluster is a combination of one or more nodes/servers which use shared storage 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.
A SQL Server failover cluster instance appears on the network as a single computer, but has functionality that provides failover from one node to another if the current node becomes unavailable. A failover cluster does not protect against disk failure. You can use failover clustering to reduce system downtime and provide higher application availability
For example: Non-disk hardware failure, operating system failure, or planned operating system upgrade.
The Step by Step Setup of Failover Cluster will follow soon.
• Database mirroring: is a software solution which can 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.
The mirror database is created by restoring a database backup of the principal database with no recovery. This makes the mirror database is inaccessible to clients. However, you can use it indirectly for reporting by creating a database snapshot on the mirror database. The database snapshot provides clients with read-only access to the data in the database as it existed when the snapshot was created.
For more information on DB Mirroring, read my post on DB Mirroring here.
The Step by Step Setup of DB Mirroring will follow soon.
• Log shipping: log shipping is one of simple methods of High Availability which 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.
A log shipping configuration includes a single primary server that contains the primary database, one or more secondary servers that each have a secondary database, and a monitor server. Each secondary server updates its secondary database at set intervals from log backups of the primary database. Log shipping involves a user-modifiable delay between when the primary server creates a log backup of the primary database and when the secondary server restores the log backup. Before a failover can occur, a secondary database must be brought fully up-to-date by manually applying any un-restored log backups.
For more information on DB Mirroring, read my post on Log Shipping here.
The Step by Step Setup of Log Shipping will follow soon.
• Replication: In Replicatio a publisher-subscriber model is used which lets a primary server referred to as the Publisher can distribute data to one or more secondary servers/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.
The Step by Step Setup of Various Replications will follow soon.