Log Shipping Vs Mirroring Vs Replication

Log Shipping:

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. 

Mirroring:

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.

Replication:

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	Mirroring	Replication
Components	Primary server, secondary server and monitor server (Optional)	Principal server, mirror server, and witness server (Optional)	Publisher, Subscribers, Distributor (Optional)
Data Transfer	T-Logs are backed up and transferred to secondary server.	Individual T-Log records are transferred using TCP endpoints.	Replication works by tracking/detecting changes (either by triggers or by scanning the log) and shipping the changes.
Server Limitation	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.	It is one to one. i.e. One principal server to one mirror server	§  Central publisher/distributor, multiple subscribers. §  Central Distributor, multiple publishers, multiple subscribers. §  Central Distributor, multiple publishers, single subscriber. §  Mixed Topology.
Type of Failover	Manual	Automatic or Manual	Manual
DB Access	You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode	Mirrored DB can only be accessed using snapshot DB.	The Subscriber Database is open to reads and writes.
Recovery Model	Log shipping supports both Bulk Logged Recovery Model and Full Recovery Model	Mirroring supports only Full Recovery model	It supports Full Recovery model.
Restoring State	The restore can be completed using either the NORECOVERY or STANDBY option	The restore can be completed using with NORECOVERY.	The restore can be completed using With RECOVERY.
Backup/Restore	This can be done manually or through Log Shipping options	User make backup & Restore manually	User create an empty database with the same name.
Mirror/ Distributor/Witness	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.	Principal server can’t act as both principal server and witness server	Publisher can be also distributor.
Type of servers	All servers should be SQL Server	All servers should be SQL Server	Publisher can be ORACLE Server
SQL Server Agent Dependency / Jobs	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.	Independent on SQL Server agent.	Yes. Snapshot agent, log reader agent & Distribution agent (transnational replication) Merge agent (merge replication).
Using with other features or components	Log shipping can be used with Database mirroring, Replication.	Database mirroring can be used with Log shipping, Database snapshots , Replication	Replication can be used with log shipping, database mirroring
DDL Operations	DDL changes are applied automatically	DDL changes are applied automatically	only DML changes to the tables you have published will be replicated.
Database Limit	No Limit	Generally good to have 10 GB’s for one server	No limit
Latency	There will be data transfer latency. >1min	There will not be data transfer latency	Potentially as low as a few seconds
Committed / Uncommitted Transactions	Both committed and uncommitted transactions are transferred to the secondary database	Only committed transactions are transferred to the mirror database	Only committed transactions are transferred to the subscriber database.
Primary Key	Not Required	Not  Required	All replicated table should have Primary Key
New Created Database & Stored Procedure	Monitoring and history information is stored in tables in msdb, which can be accessed using log shipping stored procedures.	Creates new SPs ( 3 Sps of one table). Distribution Database	Rowguid column will be created.
Individual Articles	No. Whole database must be selected	No. Whole database must be selected	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.
File Stream	Log shipping supports FILESTREAM	Mirroring does not support FILESTREAM.	Replication supports FILESTREAM
DB Name	The secondary database can be either the same name as primary database or it may be another name.	It must be the same name	It must be the same name.
DB Availability	In case of standby mode: read only database. In case of restoring with no recovery: Restoring state.	In Recovery state, no user can make any operation. You can take snapshot	Snapshot (read-only). Other types (Database are available).
Warm/ Hot Standby Solution	It provides a warm standby solution that has multiple copies of a database and require a manual failover	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)	It provides a warm standby solution that has multiple copies of a database and require a manual failover
System Data Transferred	Mostly	Yes	No
System Database		You cannot mirror the Master, msdb, tempdb, or model databases
Mode or Types	Standby mode (read-only)-you can disconnect users when restoring backups . No recovery mode (restoring state)-user cannot access the secondary database.	high-safety mode supports synchronous operation. high-performance mode, runs asynchronously. High-safety mode with automatic fail over.	Snapshot replication. Transnational replication. Transnational publication with update subscriptions. Merge publication. Pull/Push subscription.
Requirements	The servers involved in log shipping should have the same logical design and collation setting. The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model. The SQL server agent should be configured to start up automatically. You must have sysadmin privileges on each computer running SQL server to configure log shipping.	Verify that there are no differences in system collation settings between the principal and mirror servers. Verify that the local windows groups and SQL Server logins definitions are the same on both servers. Verify that external software components are installed on both the principal and the mirror servers. Verify that the SQL Server software version is the same on both servers. Verify that global assemblies are deployed on both the principal and mirror server. Verify that for the certificates and keys used to access external resources, authentication and encryption match on the principal and mirror server.	Verify that there are no differences in system collation settings between the servers. Verify that the local windows groups and SQL Server Login definitions are the same on both servers. Verify that external software components are installed on both servers. Verify that CLR assemblies deployed on the publisher are also deployed on the subscriber. Verify that SQL agent jobs and alerts are present on the subscriber server, if these are required. Verify that for the certificates and keys used to access external resources, authentication and encryption match on the publisher and subscriber server.