Comparison between Mirroring VS Log shipping and Mirroring VS Clustering

Database Mirroring Vs Clustering

Clustering Database Mirroring
Clustering failover using SQL Virtual Server provides the highest availability because it immediately fails over to the second node. This failover is transparent to the end-user/client application. Clustering failover provides protection against SQL Server failures, Windows operating system crashes and hardware failures (other than disk subsystem). This same can be achieved with High-Availability operation mode without additional witness server. Db mirroring provides protection against SQL server, database failures.
Clustering failover requires special hardware. Also, failover clustering uses a shared disk subsystem, and therefore, the computers must be physically located in the same data center, unless you plan to implement Distance Clustering. Failover clustering does not protect you against a failure in the disk subsystem and the data loss that results because of the hardware failure. Here incase of db mirroring you dont require any additional hardware. To configure db mirroring, we need an SQL instance or server linked to the primary server which will provide you high availability.

Database Mirroring Vs Log Shipping

Log Shipping Database Mirroring
Log shipping always propagate changes from primary database asynchronously by backing up transaction log file, copying it across to target server through share directory, and restoring it at the target or secondary server. All of the processes are scheduled at predefined interval by SQL Jobs In database mirroring, principal and mirror server communicate through TCP connection and write data change directly to remote transaction log file. Database mirroring process architecture is more robust and efficient than log shipping.
Secondary database can be used in read-only mode Secondary database will be always in restoring state. We can take snapshots from the secondary database and make read-only to users
Database backup strategy must be considered here, since taking backup in middle of log shipping will change the flag entries and may cause log shipping to fail. Database mirroring is transparent from database backup strategy.
Dependent of SQL Server agent, if sql server agent is down, then log shipping will be unavailable. Independent on SQL Server agent, since communication between primary and secondary happens using TCPIP ports
Log shipping cannot handle database file operations, such as adding files etc. DB mirroring will handle those operations automatically
Log shipping can be configured as One to Many. i.e one primary server and many secondary servers. In db-mirroring, its one to one. i.e. One primary server to another secondary server

Leave a Reply

Your email address will not be published. Required fields are marked *

*