Saturday, August 11, 2007

Microsoft SQL Server 2005 High Availability options...

Recently I was proposing some of the high availability options for Microsoft SQL Server 2005, so thought of summarizing the same here....

SQL Server 2005 provides 4 basic options for high availability.

  1. SQL Clustering
  2. Database Mirroring
  3. Transactional Replication
  4. Log Shipping

Other than these 4 options some people also consider backup-restore and Database snapshots as other high availability options, which is really not true.....

"Database snapshots can be used as a recovery option from user/application specific errors or for reporting purposes when combined with other technologies. A snapshot is simply a read-only view of a database at a particular point in time, that’s why called - Database snapshots"

So let’s see all of them in more detail...

I will evaluate each high availability option on these parameters

1.

Failover type

Automatic or Manual

2.

Downtime period

Time to recover or get back the system in place

3.

Availability Scope

Database level or Server level

4.

Special hardware

Any special hardware required

5.

Standby Type

Hot / Warm

6.

Complexity

Configuration/maintenance complexity

7.

Accessibility

Is it possible to check the other server or can we make use of that anyway

8.

Client Access

Client redirection, Automatic or Manual

9.

Additional servers

Any additional Microsoft SQL Server required

10.

Distance limit

How far the servers can sit

11.

Automatic Role Change

Is it possible for the server to resume work automatically or need manual intervention

12.

Multiple storage location

Multiple data storage locations

13.

Hardware Setup Cost

How expensive the whole system will be

14.

SQL Server Editions support

What all options are available with each edition of Microsoft SQL Server 2005

SQL Clustering

Database Mirroring

Transactional Replication

Log Shipping

Failover type

Automatic / Manual

Automatic / Manual

Manual

Manual

Downtime period

30 seconds

30 seconds

Manual effort required

Manual effort required

Availability Scope

Server

Database

Database / DB Objects

Database

Special hardware

Yes

No

No

No

Standby Type

Hot

Hot

Warm

Warm

Complexity

Complex

Little Complex

Little Complex

Easy

Data Accessibility (other than primary server)

No

Possible

Possible

Possible

Client Access

Automatic redirection

Automatic redirection

Manual effort required

Manual effort required

Additional servers (including base server)

At least >= 2 Servers

At least >= 3 Servers (automatic failover)

At least >= 2 Servers

At least >= 2 Servers

Distance limit

100 Miles

No Limit but depends upon network bandwidth

No Limit but depends upon network bandwidth

No Limit but depends upon network bandwidth

Automatic Role Change

Yes

Yes

No

No

Multiple storage location

No

Yes

Yes

Yes

Hardware Setup Cost

High, special certified hardware required

Medium

Low

Low

SQL Server Editions support

SQL Server Enterprise and Standard (2 node only)

SQL Server Enterprise and Standard (Restricted)

SQL Server Enterprise, Standard and Workgroup

SQL Server Enterprise, Standard and Workgroup

- Mohit

2 comments:

Hemalatha said...

Was looking for an article regarding clustering and mirroring.
Got exactly what I required.
Thanks and Hope many more articles are on the way

deepak said...

Was looking for a comparison data between the various tech. and I got exactly what I wanted.

This gives me ready data sheet for use

Thanks -