High Availability Features
- In order to reduce or avoid server or database downtime so that the applications/users works continuesly with the server we can configure high availability features.
- SQL Server supports 3 types of DR (Disaster Recovery) features.
2. Database Level ,, ,,
* T.Log Shipping
* Database Mirroring
3. Object Level HA features
1. Log shipping
- It is simple method of keeping entire database in different server.
- It works with backup, copy and restore jobs.
- We need 3 servers
* Primary server
* Secondary ,,
* Monitor server (optional)
- In primary server database is in online state where the appls or users are connected.
- In secondary server database is in standby mode where we can read data.
- In secondary server T.Log backups are applied either with
* Norecovery or
- We can have multiple secondary servers.
- To reduce downtime for the applications or users in case of primary server failed.
- To implement load balancing. i.e we can allow reading from secondary server and manipulations in primary server.
- To maintain multiple secondary databases.
- We can ship all databases except master, model, read-only and the databases whose recovery model is SIMPLE.
- We should have sysadmin privileges on both servers.
- Changing recovery model from FULL or BULK LOGGED to SIMPLE will break log shipping process.
- We cannot take ad-hoc Transaction Log backups from primary server as it breaks log shipping log backup chain.