Categories
High Availability

Database Mirroring Role Change

Role Change in Mirroring :

Database mirroring failover is an issue that can be considered from the standpoint of the database mirroring servers or the application. From the standpoint of the database mirroring servers, failover is the conversion of the mirror server into a principal server and the use of the newly recovered database as the principal database in the session. The failover may be automatic, manual, or forced.

  • Automatic – occurs only in the High Availability mode (safety is FULL and a witness is part of the session.
  • Manual – occurs in the High Availability and High Protection operating modes (safety is FULL) and the partner databases are both SYNCHRONIZED.
  • Forced service (allow data loss) – used primarily in the High Performance (safety OFF) mode to immediately and manually recover the mirror database.

When safety is FULL, the best way to reverse the roles of the servers is to use manual failover, not forced service.

Automatic Failover :

Automatic failover is a feature of database mirroring in the High Availability operating mode (safety FULL with a witness). In most cases, SQL Server can accomplish an automatic database mirroring failover in a few seconds. It can do this in part because the SQL Servers involved in a database mirroring session all test each other’s presence. This process is called a ‘ping’, though it involves much more than an ordinary IP address ping. The mirror and witness servers contact the principal server for the presence of the physical server, for the presence of the SQL Server instance, and the availability of the principal database. Similarly, the principal and the witness each ping the mirror server for availability of the physical server, the SQL Server instance, and the recovering state of the mirror database.

Suppose the database mirroring session has been set up with safety FULL and with a witness server. The mirror server, Server B, finds through its ping that the principal Server A is unavailable. Server B communicates with the witness server and receives confirmation that the witness also cannot see Server A. Then Server B will form a quorum with the witness server and promote itself into the principal role. It will recover its database and notify the witness that it is now has the principal role (though the database will be in a disconnected state, and the new principal database’s transaction log cannot be truncated).

Server B’s new principal database continues to replay transaction log activity, but it has been in a redo state continuously and in most cases there will be little left to do. In database mirroring for all SQL Server editions, the new principal database becomes available as soon as it finishes its redo state. When the database enters the undo state, it becomes available for user connections. Finishing the redo state occurs normally in a few seconds, although the remaining undo phase could be much longer. In database mirroring, the new principal database becomes available for serving user connections as soon as the redo process is finished. The new principal server B’s database is in a DISCONNECTED state and is exposed, but it can place its database into service just as soon as the redo phase is completed.

Now suppose the old principal server comes online. There is a negotiation process that must occur between the two servers if they are reversing roles, as in a manual failover, or an automatic failover where the old principal is repaired fairly quickly. Before mirroring can restart, the two partner servers need to find out how they can synchronize with each other. The mirroring failover lsn plays a critical role in this process.

Server A (the new mirror) is behind, but it is not clear how much. Server A reports to server B (the new principal) the last mirroring failover lsn that it received from server B. Server B, on the other hand, has had committed work which has brought its mirroring failover lsn to a more recent state. Both servers then agree that server B has the correct failover lsn, and that server A must catch up to it. Server B sends a sufficient number of transaction records to server A that it can replay in order to become synchronized.

Manual Failover :
A manual failover is a way of causing the two partner servers to reverse their roles in an orderly way and without any errors. It requires that safety be set to FULL, and that the principal and mirror databases are in the SYNCHRONIZED state.
You cause a manual failover by invoking the ALTER DATABASE command on the principal server:

ALTER DATABASE AdventureWorks SET PARTNER FAILOVER

or by clicking the Failover button in the Database Properties/Mirroring dialog in Management Studio. A manual failover causes current users to be disconnected and rolls back any unfinished transactions from the old principal database. It will recover the mirror database by finishing all completed transactions in the redo queue, and rolling back (in the undo phase) unfinished transaction. The old mirror is assigned the principal role, and the old principal database takes on the new role of mirror. The two servers will negotiate a new starting point for mirroring based on their mirroring failover lsns, and proceed with their roles reversed.

Forced Service :
You can cause a forced service on the mirror by invoking the ALTER DATABASE command:

ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Normally this is only useful if you have safety OFF, and if the principal is no longer operating. You can also use this command with safety FULL but if the recovered mirror server cannot form a quorum, it cannot serve the database. Therefore it’s better to use this command only with safety OFF (the High Performance mode). Some data may be lost because the asynchronous data transfer may not have kept the mirror fully up to date with committed transactions from the principal.

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*