Log shipping vs Mirroring vs Replication for Data Architects/ DBAs

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. 

Basic requirements for each of them

 
Log Shipping

• 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. 

Mirroring

• 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. 

Replication

• 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.