In this article,we’re going to explored what why when to use of Log Shipping,Mirroring & Replication.

Architecture of Log Shipping and Mirroring :

LMR01

Architecture of Replication :

LMR02

 

Explanation Point Log Shipping Mirroring Replication
What In Log shipping mechanism, periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s) which is based on SQL Server Agent jobs. Log shipping supports an unlimited number of secondaries for each primary database Database mirroring is SQL Server engine reads from the transaction log and copies transactions from the production server instance to the mirror server instance. 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.
Data synchronization Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule T-Logs are backed up and transferred to secondary server Database mirroring can operate synchronously or asynchronously. The transaction on the production database will not be committed until it is hardened to disk on the mirror. It can be transactional, snapshot or Merge which is defined by requirements & DBA.
Data Transfer   Logs are backed up and transferred to secondary server     Individual Transaction Log records are transferred from production Database to mirrored instance using TCP endpoints   In this mechanism, system are tracking/detecting changes (either by triggers or by scanning the log) and shipping the changes.
Requirements In log shipping, production database, secondary server and monitor server (Optional) required Production Database, mirror server, and witness server (Optional) are needed to setup. Production database is Publisher, Subscribers, Distributor (Optional).
Transactional Consistency All committed and un-committed transaction are transferred Transfer only committed Transactions no uncommitted transaction are allowed transferred. No uncommitted transaction are allowed Committed transactions are transferred to the subscriber database.
Reporting Server In log shipping the secondary database will be in Read-only mode so that it can be used for reporting purposes. In database mirroring the mirror database will be in Restoring state and hence cannot be used for accessing. If you want it for reporting purposes then make use of database snapshot. The Subscriber Database is open to reads and writes.
Server Limitation Log shipping can be applied to multiple stand-by servers Mirrored server only one. Central publisher/distributor, multiple subscribers. Central Distributor, multiple publishers, multiple subscribers. Central Distributer, multiple publishers, single subscriber. Mixed Topology.
Failover Log Shipping supports only manual failover Both automatic and manual failover supports. Manual failover
Failover Duration it takes more than 30 mins Failover is fast within 3 to 10 seconds but it depends on current situation.   Failover concept is not available.
Client Re-direction Client Re-direction:  Manual changes required Client Re-direction:  Fully automatic as it uses .NET 2.0  
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
  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)
Primary key No need No Need All replicated table should have Primary Key
latency There will be data transfer latency. >1min. There will not be data transfer latency. Potentially as low as a few seconds.
DDL Operations DDL changes are applied automatically. DDL changes are applied automatically. only DML changes to the tables you have published will be replicated.
Requirement §  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.  
Components §  Primary server, secondary server and monitor server (Optional) §  Principal server, mirror server, and witness server (Optional). §  Publisher, Subscribers, Distributor (Optional).