What is mirroring in SQL Server and how does it differ from replication?

Mirroring in SQL Server is a high-availability technology where a database is synchronously or asynchronously duplicated on another server (mirror). In case of a failure of the primary server, automatic or manual failover to the mirror server occurs, ensuring minimal downtime.

Main features of mirroring:

• Operates at the level of a single database.
• Supports two modes: synchronous (high safety) and asynchronous (high performance).
• Has a principal (primary) server role and a mirror role.
• Can use an additional server for automatic failover (witness).

Replication, on the other hand, is a mechanism for copying and distributing data and database objects from one server to another (or others) for various purposes: scalability, load distribution, data integration, etc. Replication can be unidirectional or bidirectional, supports data filtering, and operates at the transaction or snapshot level.

Key differences:

• Mirroring is designed for high availability and quick recovery, while replication is for data distribution and scaling.
• Mirroring works with a single database, replication can cover multiple databases and objects.
• Mirroring does not allow data modification on the mirror server, while replication can support different topologies with changes on different nodes.

Example of using mirroring:

-- Setting up mirroring requires server and security configuration, here is a simple example query to check status:
SELECT * FROM sys.database_mirroring WHERE database_id = DB_ID('MyDatabase');