What Is Mirroring
- Database mirroring is a primarily software solution for increasing database availability
- Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.
- The simple and bulk-logged recovery models do not support database mirroring
- Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
- One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server)
Prerequisites :
- For a mirroring to be established, the partners and the witness (if any) must be running on the same version of SQL Server.
- The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server
- The database must use the full recovery model
- Verify that the mirror server has sufficient disk space for the mirror database
- Take full backup of database & restore it on mirror database with NORECOVERY option.Also , log backups that were created after that backup was taken must also be applied to mirror database , again WITH NORECOVERY.
Restrictions :
- Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases
- A mirrored database cannot be renamed during a database mirroring session
- Database mirroring is not supported with either cross-database transactions or distributed transactions
Benefits of Database Mirroring:
- Increases data protection.
- Increases availability of a database.
- Improves the availability of the production database during upgrades.
How Database Mirroring Works:
- The principal and mirror servers communicate as partners in a database mirroring.
- The two partners : the principal role and the mirror role. one partner performs the principal role, and the other partner performs the mirror role.
- The partner that owns the principal role is called as the principal server and The partner that owns copy of principle database is called as Mirroring Server.
- Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible.
- Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible.
- A database mirroring session runs with either synchronous or asynchronous operation
A. synchronous operation
- A transaction is committed on both partners
- Increase in transaction latency.
B. Asynchronous operation
- The transactions commit without waiting for the mirror server to write the log to disk
- This maximizes performance
Operating Modes
High-safety mode
- It supports synchronous operation
- When session starts the mirroring Server synchronizes mirroring DB with principle DB ASAP.
- As soon as the databases are synchronized, a transaction is committed on both partners
High-performance mode
- It supports asynchronous operation
- When session start The mirror database might lag somewhat behind the principal database.
- It does not wait for mirroring server to acknowledge to principle server for commit. This means that transactions commit without waiting for the mirror server to write the log to disk.
All database mirroring sessions support only one principal server and one mirror server.
High-safety mode with automatic failover requires a third server instance, known as a witness.
Witness server send packets to each server continuously at an interval of 10 sec to verifying whether the principal server is up and functioning. If any problem occures say due to network latency then witness server will automatically failover databases.
We can change this witness interval value
Role Switching:
Automatic failover
This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.
Manual failover
This requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.
Forced service (with possible data loss)
Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.
More:
Reference :
https://msdn.microsoft.com/en-us/library/ms189852(v=sql.105).aspx
No comments:
Post a Comment