LOG SHIPPING VS. MIRRORING VS. REPLICATION
July 24, 2011 17 Comments
It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.
Database mirroring is a primarily software solution for increasing database availability.
It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
Log Shipping::Primary server, secondary server and monitor server (Optional).
Mirroring::Principal server, mirror server, and witness server (Optional).
Replication::Publisher, Subscribers, Distributor (Optional).
Log Shipping::T-Logs are backed up and transferred to secondary server.
Mirroring::Individual T-Log records are transferred using TCP endpoints.
Replication::Replication works by tracking/detecting changes (either by triggers or by scanning the log) and shipping the changes.
Log Shipping::It can be configured as One to Many. i.e one primary server and many secondary servers. Or
Secondary server can contain multiple Primary databases that are log shipped from multiple servers.
Mirroring::It is one to one. i.e. One principal server to one mirror server.
Mirroring::Automatic or manual.
Log Shipping::You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.
Mirroring::Mirrored DB can only be accessed using snapshot DB.
Replication::The Subscriber Database is open to reads and writes.
Log Shipping::Log shipping supports both Bulk Logged Recovery Model and Full Recovery Model.
Mirroring::Mirroring supports only Full Recovery model.
Replication::It supports Full Recovery model.
Log Shipping::The restore can be completed using either the NORECOVERY or STANDBY option.
Mirroring::The restore can be completed using with NORECOVERY.
Replication::The restore can be completed using With RECOVERY.
Log Shipping::This can be done manually or
through Log Shipping options.
Mirroring::User make backup & Restore manually.
Replication::User create an empty database with the same name.
Log Shipping::The monitor server should be on a server separate from the primary or secondary servers to avoid losing critical information and disrupting monitoring if the primary or secondary server is lost. . If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance.
Mirroring::Principal server can’t act as both principal server and witness server.
Replication::Publisher can be also distributer.
Log Shipping::All servers should be SQL Server.
Mirroring::All servers should be SQL Server.
Replication::Publisher can be ORACLE Server.
Log Shipping::Yes. Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.
Mirroring::Independent on SQL Server agent.
Replication::Yes. Snapshot agent, log reader agent & Distribution agent (transactional replication)
Merge agent (merge replication).
Log Shipping::Log shipping can be used with Database mirroring, Replication.
Mirroring::Database mirroring can be used with
Log shipping, Database snapshots , Replication.
Replication::Replication can be used with log shipping, database mirroring.
Log Shipping::DDL changes are applied automatically.
Mirroring::DDL changes are applied automatically.
Replication::only DML changes to the tables you have published will be replicated.
Log Shipping::No limit.
Mirroring::generally good to have 10 DB’s for one server.
Log Shipping::There will be data transfer latency. >1min.
Mirroring::There will not be data transfer latency.
Replication::Potentially as low as a few seconds.
Log Shipping::Both committed and uncommitted transactions are transferred to the secondary database.
Mirroring::Only committed transactions are transferred to the mirror database.
Replication::Only committed transactions are transferred to the subscriber database.
Log Shipping::Not required.
Replication::All replicated table should have Primary Key.
Log Shipping::Monitoring and history information is stored in tables in msdb, which can be accessed using log shipping stored procedures.
Replication::Creates new SPs ( 3 Sps of one table).
Rowguid column will be created.
Log Shipping::No. Whole database must be selected.
Mirroring::No. Whole database must be selected.
Replication::Yes. Including tables, views, stored procedures, and other objects. Also filter can be used to restrict the columns and rows of the data sent to subscribers.
Log Shipping::Log shipping supports FILESTREAM.
Mirroring::Mirroring does not support FILESTREAM.
Replication::Replication supports FILESTREAM.
Log Shipping::The secondary database can be either the same name as primary database or it may be another name.
Mirroring::It must be the same name.
Replication::It must be the same name.
Log Shipping::In case of standby mode: read only database.
In case of restoring with no recovery: Restoring state.
Mirroring::In Recovery state, no user can make any operation.
You can take snapshot.
Other types (Database are available).
Log Shipping::It provides a warm standby solution that has multiple copies of a database and require a manual failover.
Mirroring::When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
Replication::It provides a warm standby solution that has multiple copies of a database and require a manual failover.
Mirroring::You cannot mirror the Master, msdb, tempdb, or model databases.