Database Clustering, Mirroring, Log Shipping, and Replication for SQL Server 2008

Prior to SQL Server 2012 Availability Group, SQL Server 2008 uses Mirroring and Log Shipping.

Database Mirroring

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

A copy of the database is created on other server. However, database is not readable. Only on standby. Databases will appear as “Restoring” state.

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017

Database Log Shipping

Use only for backward comparability. Use Availability Group instead.

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary serverinstance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.

Log shipping consists of three operations:

1. Back up the transaction log at the primary server instance.
2. Copy the transaction log file to the secondary server instance.
3. Restore the log backup on the secondary server instance.

Note: During step #3 restoring, database will disconnect all connections.

https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-2017

Database Replication

Replication 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. Use replication to 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.

Replication uses Publication/Subscription and can be implemented at table level.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication?view=sql-server-2017

Database Cluster

A Microsoft SQL Server Cluster is nothing more than a collection of two or more physical servers with identical access to shared storage that provides the disk resources required to store the database files. 

These servers are referred to as “nodes“.  Each of the nodes talk to one another via a private network, sending a heartbeat signal between them.  Should one node not communicate its heartbeat to the other node in the cluster the secondary node will take ownership of any dependent services being run by the node that lost communication.  This process is referred to as “failover“.

https://www.mssqltips.com/sqlservertip/1541/getting-started-with-sql-server-clustering/