SQL Server DBA Interview Questions and Answers – Database Mirroring – 1

In this blog I am discussing the SQL Server Interview Questions answers on Database Mirroring. This is very interesting topic and consists of very wide range of terminologies and scope. I will cover this topic in two blogs so that I can touch base all the possible Questions from Database Mirroring.

1. What is Database Mirroring?

Database mirroring was introduced with Microsoft SQL Server 2005 technology that can be used to design high-availability and high-performance solutions for database redundancy.

In database mirroring, transaction log records are sent directly from the principal database to the mirror database. This helps to keep the mirror database up to date with the principal database, with no loss of committed data. If the principal server fails, the mirror server automatically becomes the new principal server and recovers the principal database using a witness server under high-availability mode. We will discuss these modes later. Fundamentally to summarize there are three jargon to understand – Principal database is the active live database that supports all the commands, Mirror is the hot standby and witness which allows for a quorum in case of automatic switch over.

2. How does Database Mirroring works?

In database mirroring, the transaction log records for a database are directly transferred from one server to another, thereby maintaining a hot standby server. As the principal server writes the database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance. The mirror server continuously applies the log records to its copy of the database. Mirroring is implemented on a per-database basis, and the scope of protection that it provides is restricted to a single-user database. Database mirroring works only with databases that use the full recovery model.

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

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

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

6. 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.

7. What is a Principal server?

Principal server is the server which serves the databases requests to the Application.

8. What is a Mirror?

This is the Hot standby server which has a copy of the database.

9. What is a Witness Server?

This is an optional server. If the Principal server goes down then Witness server controls the fail over process.

10. What is Synchronous and Asynchronous mode of Database Mirroring?

In synchronous mode, committed transactions are guaranteed to be recorded on the mirror server. Should a failure occur on the primary server, no committed transactions are lost when the mirror server takes over. Using synchronous mode provides transaction safety because the operational servers are in a synchronized state, and changes sent to the mirror must be acknowledged before the primary can proceed

In asynchronous mode, committed transactions are not guaranteed to be recorded on the mirror server. In this mode, the primary server sends transaction log pages to the mirror when a transaction is committed. It does not wait for an acknowledgement from the mirror before replying to the application that the COMMIT has completed. Should a failure occur on the primary server, it is possible that some committed transactions may be lost when the mirror server takes over.

11. 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

12. What is High Availability operating mode?

It consist of the Principal, Witness and Mirror in synchronous communication. In this mode SQL server ensures that each transaction that is committed on the Principal is also committed in the Mirror prior to continuing with next transactional operation in the principal. The cost of this configuration is high as Witness is required. If the network does not have the bandwidth, a bottleneck could form causing performance issue in the Principal. If Principal is lost Mirror can automatically take over.

13. What is High Protection operating mode?

It is pretty similar to High Availability mode except that Witness is not available, as a result failover is manual. It also has transactional safety FULL i.e. synchronous communication between principal and mirror. Even in this mode if the network is poor it might cause performance bottleneck.

14. What is High Performance operating mode?

It consists of only the Principal and the Mirror in asynchronous communication. Since the safety is OFF, automatic failover is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation.

15. What are Recovery models support Database Mirroring?

Database Mirroring is supported with Full Recovery model.

16. 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.

17. How can we create an end point using SQL script?

CREATE ENDPOINT Endpoint1
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO

18. 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

19. What is Log Hardening?

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

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

Yes, using database snapshots.

21. What is Role-switching?

Inter changing of roles like principal and mirror are called role switching.

22. How to Set a Witness Server to Database Mirroring?

ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://SQLWITN.local:5024'

23. How to Remove a Witness Server from Database Mirroring?

ALTER DATABASE AdventureWorks SET WITNESS OFF

24. What are the Database Mirroring states?

• SYNCHRONIZING
• SYNCHRONIZED
• SUSPENDED
• PENDING_FAILOVER
• DISCONNECTED

25. What does SYNCHRONIZING state means in Database Mirroring?

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. At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.

26. What does SYNCHRONIZED state means in Database Mirroring?

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.

If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.

27. 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.

28. 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.

29. What does DISCONNECTED state means in Database Mirroring?

The partner has lost communication with the other partner

30. Why we get the below error message while configuring database mirroring?

Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring

We 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.

31. Can we configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping?

No, It is not possible.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://extremeexperts.com/sql/articles/DBMirroring1.aspx

http://sql-articles.com/articles/high-availability/advantages-a-disadvantages-of-database-mirroring/

http://dcx.sap.com/1100/en/dbadmin_en11/backup-s-3422468.html

http://sql-articles.com/articles/high-availability/types-and-operating-modes-in-mirroring/