Maintaining SQL Server High Availability
- Failover Clustering
- Log Shipping
- Database Mirroring
Failover Clustering : Used for entire SQL server Instance , Hardware Solution based on MSCS
Shared disks configured between MSCS cluster and automatic failover Will be achieved if one of the node goes down
Database Mirroring :: High availability for Database, Software solution based on HOT standby (New 2005)
- Principle database is applied at Mirror database almost real time.
- There is one t one relationship and means there would be only one HOT standby for any database.
- The mirrored database is in recovery mode and never available until failed over
- A witness box could be used to carry automatic failover when needed
- Will only work with FULL recovery Model
- Could be configured into 3 different modes- High Availability (Sync operation with Witness server ), High Protection (Sync Operation with NO Witness server ) & High Performance (ASync operation with NO Witness server )
Log Shipping : High availability for Database, Software solution based on WARM standby (Old 2000)
- Secondary database is not sync with primary . The primary database doesn’t send the transaction logs directly to the Secondary. The moving of logs is done by the SQL agent using a network share. Primary server has a backup Job which backs up the transaction logs to a network share while Secondary server have Job to apply them at destination.
- The warm standby can have one to one else one to many relationship with Primary database.
- The Warm standby is always available in read only mode
- A monitor server could be used to setup all jobs needed between Primary and Secondary server. We Can omit the Monitor server and push jobs into Primary & secondary if needed.
- Supports Manuel Failover only
- Can work with FULL else BULK recovery Model