Full Working of SQL Database Mirroring

SQL server database mirroring use to increase the database availability, Users can protect their database with this approach. Here is the full working overview of SQL Database Mirroring process:

Principal: Database mirror maintains two copies of a single database, but client can access only one copy. From this database copy, the application connects. This database is called as principal database. The server which is host the principal database is called as principal server.

Mirror: As the name indicates, the mirror maintains the copy of the principal database. The mirror is always shows in restoring state that’s why it is not accessible to the applications. The database keep up-to-date by transferring log records from the principal database and applies on the mirror database. The server which is host the mirror database is called as mirror server.

Witness: Witness is an optional option, a SQL Server instance. This process is totally separate from the principal and mirror instances. The witness provides a automatic failover when database mirroring used in synchronous mode. 

Send Queue: As I have already explained earlier that Database kept up-to-date by transferring log records from the principal database. Send queue is a queue which builds up at the principal database when the log records can’t be send at the generated rate. It is not use any extra memory & exists in the principal database transaction log.  

Redo Queue: It is just opposite of Send queue & builds up at the mirror database when the log records can’t be applied at the received rate. Just like send queue, the redo queue is also not use extra memory & exists in the mirror database transaction log of the mirror.

Endpoint: It is an object & enables SQL Server to communicate over the network. It encapsulates a transport protocol and a port number. 

Failover: Database mirroring provides a tool to fail over to the mirror database when the principal database gets fail. 

Some important factors of database mirroring:

• Database mirror maintains two copies of a single database, but only one database copy is accessible to the applications. 
• Don't forget, we cannot mirror the master, msdb, temp, or model databases.
• Database mirroring only used in full recovery model. You can’t use it in the simple or bulk-logged recovery models. 
• SQL Server 2005 allows only one mirror database for each principal database. 
• Multiple databases in an instance can be mirrored.  
• You can repair your corrupt database with database mirroring after media failure, software malfunctioning etc.  

From this post, you can easily understand the full working of SQL database mirroring process.