This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is an asynchronous operation mode?
This is high-performance mode.
The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.
In high-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It does not wait for an acknowledgement from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk.
What is a synchronous operation mode?
This is high-safety mode.
When a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
Can you mirror the master, msdb, tempdb, or model databases?
Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.
When Is High-Performance Mode Appropriate?
The High-performance mode can be useful in a disaster-recovery scenario in which the principal and mirror servers are separated by a significant distance and where you do not want small errors to impact the principal server.
Are you using a witness server on High-Performance Mode?
No, it is not recommended because of asynchronous operation mode.
A witness can coexist with high-performance mode, but the witness provides no benefit and introduces risk.
If the witness is disconnected from the session when either partner goes down, the database becomes unavailable. This is because, even though high-performance mode does not require a witness.
How database mirroring works under the synchronous operation mode?
- On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
- The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
- The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
- On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.
T-SQL Script to FULL/OFF Transaction Safety option.
Set FULL:
Set OFF:
Can you use single witness server for multiple database mirroring sessions?
Yes,
A specific server instance can act as a witness in concurrent database mirroring sessions, each for a different database. Different sessions can be with different partners.
During the mirroring session, can you turn off the witness server?
At any time during a database mirroring session, the database owner can turn off the witness for a database mirroring session.
T-SQL Script to turn off the witness server.
|
1 |
ALTER DATABASE Database_Name SET WITNESS OFF; |
During the database mirroring session, Can you perform role switching?
Yes,
In role switching, the mirror server acts as the failover partner for the principal server, taking over the principal role, recovering its copy of the database and bringing it online as the new principal database. The former principal server, when available, assumes the mirror role, and its database becomes the new mirror database.
How Automatic Failover Works?
- If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.
- The witness and mirror servers register that the principal server is unavailable.
- If any log is waiting in the redo queue, the mirror server finishes rolling forward the mirror database.
- The former mirror database moves online as the new principal database, and recovery cleans up all uncommitted transactions by rolling them back as quickly as possible.
- When the former principal server rejoins the session, it recognizes that its failover partner now owns the principal role. The former principal server takes on the role of mirror, making its database the mirror database. The new mirror server synchronizes the new mirror database with the principal database as quickly as possible.
What are the different types of Mirroring states?
- SYNCHRONIZING: The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.
- SYNCHRONIZED: When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.
- SUSPENDED: SUSPENDED is a persistent state that survives partner shutdowns and startups.
- The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.
- PENDING_FAILOVER: This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.
- DISCONNECTED: The partner has lost communication with the other partner.
Can you set up Database Mirroring session using windows authentication?
Yes, you can configure database mirroring using windows authentication mode.
During the mirroring session, Can you add data file into primary database?
When using Database Mirroring and if you have not placed your database files for your principal database and mirrored database on an identical path then adding a database file to the principal database is quite different than the normal process. In this case, when you add a database file to a database, your mirroring configuration will be suspended because SQL Server will not be able to create that file on the mirrored server. This will not allow the databases to be in sync and will force mirroring to go into a suspended state.
But can add data file during the mirroring session, If the complete path (including drive letter and folder names) for the database files exists on both the principal and mirrored server.
What are the steps to add a data file in primary server, when primary and mirror database path are different?
We will take to add a new database file for a mirrored database that has different file paths on the principal and mirror. First remove the mirror partner, then create the database file on the principal server. After that I will take a log backup and restore it on the mirrored server using the WITH MOVE option. Once the restore is done, I will re-establish database mirroring.
What are the steps to add a data file in primary server, when primary and mirror database path are different?
We will take to add a new database file for a mirrored database that has different file paths on the principal and mirror. First remove the mirror partner, then create the database file on the principal server. After that I will take a log backup and restore it on the mirrored server using the WITH MOVE option. Once the restore is done, I will re-establish database mirroring.
If we add any users to the Primary database, will it be copied to the Mirror databases or not?
Yes, It will be copied to the Mirror database with the permissions. However, you have to create the login manually with the same user ID.
