Mirroring

1) 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.

2) T-SQL Script to FULL/OFF Transaction Safety option.

Set FULL:

1	ALTER DATABASE database_name SET PARTNER SAFETY FULL

Set OFF:

1	ALTER DATABASE database_name SET PARTNER SAFETY OFF

3) 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.

4) 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.

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

5) 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.

6. What are the benefits of that Database Mirroring?

· Database mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimized data loss.

· It has automatic server failover mechanism.

· Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).

· Because propagation can be done asynchronously, it requires less bandwidth than synchronous method (e.g. host-based replication, clustering) and is not limited by geographical distance with current technology.

· Database mirroring supports full-text catalogs.

· Does not require special hardware (such as shared storage, heart-beat connection) and cluster ware, thus potentially has lower infrastructure cost

7. What are the Disadvantages of Database Mirroring?

· Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.

· It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.

· Automatic server failover may not be suitable for application using multiple databases.

8. What are the minimum requirements for Database Mirroring?

· Database base recovery model should be full

· Database name should be same on both SQL Server instances

· Server should be in the same domain name

· Mirror database should be initialized with principle server

9. What are the Restrictions for Database Mirroring?

· A mirrored database cannot be renamed during a database mirroring session.

· Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.

· Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.

· On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance.

· Database mirroring is not supported with either cross-database transactions or distributed transactions.

10. What are the operating modes of Database Mirroring?

SQL Server provides 3 operating modes for database mirroring.

· High Availability Mode

· High Protection Mode

· High Performance Mode

11. What are End Points and its usages?

An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.

12. What is the default of end points (port numbers) of principal, mirror and witness servers?

The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024

13. What is Log Hardening?

Log hardening is the process of writing the log buffer to the transaction log on disk, a process called.

14. Is it possible to perform read only operation at mirrored database in mirror server?

Yes, using database snapshots.

15. What are the Database Mirroring states?

· SYNCHRONIZING

· SYNCHRONIZED

· SUSPENDED

· PENDING_FAILOVER

· DISCONNECTED

16. What does SUSPENDED state means in Database Mirroring?

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. A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session. SUSPENDED is a persistent state that survives partner shutdowns and startups.

17. What does PENDING_FAILOVER state means in Database Mirroring?

This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.

When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.

18. What does DISCONNECTED state means in Database Mirroring?

The partner has lost communication with the other partner

19 Why I’m getting the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
Ans : You need to restore the Full backup from principal server using With NoRecovery option and also one transactional log backup from principal server using With NoRecovery option and then start configuring mirroring.

20 How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec.

Ans: ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30

21 What status of mirroring has if secondary is down?
Ans: If secondary is down principle or Mirror show status disconnected

22 What status of mirroring has if principle is down?
Ans: If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring

23. What status of mirroring has if mirroring is paused?

Ans: Is mirroring is set to paused from principle then then both principle & mirror in suspending

24. Pausing or Removing Database Mirroring?

To pause the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER SUSPEND;

To resume the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER RESUME;

To remove the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER OFF;