SQL Server 2005 – Introduction to Data Availability
In this tutorial you will learn about Data Availability in SQL Server 2005 – Failover clustering, Database mirroring, Log shipping, Replication, Introduction to Database Mirroring and advantages of mirroring.
SQL Server 2005 introduces several high availability solutions to improve the availability of servers and databases. These solutions mask the effect of hardware or software failure and maintain the availability of applications so that users perceive a minimum of downtime.
The options for creating high availability for a server or a database include:
Failover clustering
The failover cluster is a combination of one or more nodes with two or more shared disks. SQL Server and Notification services are installed on Microsoft Cluster Service known as a resource group. The resource group is generally owned by a single node in the cluster at any given point of time. The service has a virtual name that is independent of the node names. This server appears on the network as if it were a separate computer and has the capacity to provide failover from one node to another if the current node becomes unavailable. Though it does not protect against disk failure, it reduces system downtime and provides higher application availability.
Database mirroring
Database mirroring is a software solution for increasing database availability. The mirroring technology can be used to maintain a single mirror database for a corresponding read-write database called principal database. The mirror database is inaccessible to clients but can be used indirectly for reporting purposes by creating a snapshot on the mirror database.
Each mirroring configuration is made up of a principal server, a mirror server and an optimal witness server. The mirror database is maintained up to date with the principal server and failover is instantaneous.
The availability level in database mirroring is tremendous and offers an easy to manage alternative to failover clustering. Compared to log shipping, it is a synchronous configuration and simple failover strategy.
Log shipping
Log shipping operates at database level and is used to maintain one or more standby databases called secondary databases. Each secondary database is created by restoring a full backup of the primary database without recovery. The configuration includes a single primary server and one or more secondary servers and a monitor server. The secondary servers update their secondary databases at regular predetermined intervals from log backups of the primary database. However, in the event of failure of the primary database the secondary database must be manually brought up to the level of the primary database. Secondary databases also have limited availability during restores.
Log shipping nevertheless, provides flexible support for multiple standby databases. Log shipping also allows user specified delay between primary server log backup and restore of the log backup.
Replication
In Replication the primary server allows the DBA to distribute data to one or more secondary servers and allows availability and scalability across these servers. Filters are available to provide subsets of data to subscribers and also allow partitioned updates of data. Three types of replications are possible:-snapshot, transactional and merge. Transactional replication has the lowest latency and is more commonly used. Replication is supported in all editions of SQL Server 2005.
Introduction to Database Mirroring –the concepts of database mirroring
Database mirroring is a software solution to high availability of data. In this technique the principal server is related to a mirror server and the transactions performed in the primary server are mirrored in the mirror server creating a standby database that can be instantly brought online in the event of failure of the primary server. Sometimes a third computer is configured as a witness server to facilitate automatic failover in the event of the principal server becoming unavailable.
Servers configured to perform mirroring operations are known as a quorum. The process of mirroring provides the following advantages:
- Full database redundancy
- Near instantaneous failover within 3 seconds
- Failover can be performed manually or automatically.
- No special hardware requirements.
- Ease of manageability as it is easy to set up and can be configured to be self monitoring for automatic failover.
- Transparent client redirection
