Database Mirroring Interview Questions & Answers

Question 1. What Is Database Mirroring? What Are The Benefits Of That?

Answer :

Database mirroring is an option to improve the availability of a database which supports automatic fail over with no loss of data.

Benefits:-

• Increases data protection.
• Increases availability of a database.
• Improves the availability of the production database during upgrades.

Question 2. What Are The Prerequisites For Database Mirroring?

Answer :

• Database base should be in full recovery model.
• Database name same on both servers.
• Server should be on same domain name.
• Mirror database should be initialized with principal server.

Question 3. What Are The Restrictions For Database Mirroring?

Answer :

• Maximum 10 databases per instance can support on a 32-bit system.
• Database mirroring is not supported with either cross-database transactions or distributed transactions.

Question 4. Explain About Principal, Mirror And Witness Servers?

Answer :

1. Principal Server: - One Server serves the database to client is called Principal server and it having original data. Can have only one Principal Server and it has to be on a separate server.
2. Mirror Server: - Other server instance acts as a hot or warm standby server is called Mirror server and it having copy of database.
3. Witness Server: - The witness server is an optional server and it controls automatic failover to the mirror if the principal becomes unavailable. To support automatic failover, a database mirroring session must be configured in high-availability.

Question 5. In Which Operations Are Running The Operating Modes?

Answer :

Asynchronous:- Under asynchronous the principle server does not wait for the response from the mirror server after sending log buffer.

Synchronous:- Under synchronous the principle server sends the log buffer to the mirror server and then waits for an acknowledgement from the mirror server.

Question 6. What Are The Operating Modes And Explain Them?

Answer :

High Availability (principle+mirror+witness):- High-availability mode, runs synchronously. Requires a witness Server instance. The Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.
Principle is not available the witness and mirror will decide automatic failover .mirror becomes online.

High Protection (principle+mirror):- High-protection mode, runs synchronously. Always commit changes at both the Principal and Mirror. Automatic failover is not possible.

High Performance: - High-performance mode, runs asynchronously and the transaction safety set to off. The Principal server does not wait for a response from the mirror server after sending the log buffer. The principal server running nice and fast, but could lose data on the mirror server.

Question 7. What Is End Point? How U Create End Point?

Answer :

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

Creation of an end point:-

Create endpoint <endpoint name> State=started/stopped/disabled

as tcp (listener port=5022/5023) for database mirroring (role=partner/witness)

Question 8. What Is The Default Of End Points (port Numbers) Of Principal, Mirror And Witness Servers? How To Find The Port Numbers?

Answer :

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

To Find Port Number:- SELECT name, port FROM sys.tcp endpoints.

Question 9. Which Trace Flag Is Used In Mirroring?

Answer :

• Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. 1400 Trace flag is used in mirroring.
• To set trace flag for Database mirroring:- Configuration Manager > Right click on server instance > Properties > Advanced tab > Startup parameters > -t1400 (add)

Question 10. In Which Recovery Model We Can Use In Mirroring?

Answer :

In mirroring the principal and mirror databases are used only full recovery model.

Question 11. What Is Role-switching?

Answer :

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

Question 12. What Is The Syntax To Stop The Database Mirroring?

Answer :

Alter database <database name> set partner off.

Question 13. How To Configure Mirroring?

Answer :

• Choose Principal Server, Mirror Server, and optional Witness server.
• The principal and mirror server instances must be running the same edition either Standard Edition or Enterprise Edition.
• The Witness server instance can run on SQL Server Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition.
• Mirror database requires restoring a recent backup and one or more T.log backups of the principal database (with No recovery).

Question 14. How To Monitoring Mirroring?

Answer :

There are six methods are available for monitoring the Database Mirroring:

Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.

To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.

SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.

SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.

Performance Monitor:- It can provide real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.

Profiler:- Profiler many events are providing the status of the Database mirroring

System Stored Procedures:-

• sp_dbmmonitorupdate monitoring
• sp_dbmmonitorchange monitoring
• sp_dbmmonitorhelpmonitoring
• sp_dbmmonitordropmonitoring

Question 15. What Is Hardening?

Answer :

As quickly as possible, the log buffer is written to the transaction log on disk, a process called hardening.

Question 16. What Is Log Buffer?

Answer :

A log buffer is a special location in memory (RAM). SQL Server stores the changes in the database log buffer.

Question 17. How To Set A Witness Server To Database Mirroring?

Answer :

SSMS:- Right Click on Principal Database > Tasks > Mirror > Click on Configure Security > Provide the End point for Witness server > Click oK

T-SQL:- ALTER DATABASE Adventure Works SET WITNESS = 'TCP://prasad.local:5024' (Do this from the Principal Server)

Question 18. How To Remove A Witness Server From Database Mirroring?

Answer :

SSMS:- Right Click on Principal Database > Tasks > Mirror > Remove TCP address from the Witness > Click oK.

T-SQL:- ALTER DATABASE Adventure Works SET WITNESS OFF.

Question 19. How To Setup Fully Qualified Names For Database Mirroring?

Answer :

FQDN Error:

One or more of the server network addresses lacks a fully qualified domain name (FQDN).  Specify the FQDN for each server, and click Start Mirroring again.

The syntax for a fully-qualified TCP address is:

TCP ://< computer_name>.<domain_segment>[.<domain_segment>]:<port>

Section 1.01 

Section 1.02 II. RECTIFYING FULLY QUALIFYED NAMES

1. To View Endpoints:-SELECT * FROM sys.database_mirroring_endpoints;
2. Remove existing all Endpoints from Principal, Mirror and Witness servers:- DROP ENDPOINT [ENDPOINT_NAME]
3. Adding "local" as the primary DNS suffix as follows:-

• Right-click My Computer, and then click Properties. The System Properties dialog box will appear.
• Click the Computer Name tab.
• Click Change. The Computer Name Changes dialog box will appear.
• Click More. The DNS Suffix and NetBIOS Computer Name dialog box will appear.
• Enter the appropriate DNS suffix for the domain.
• Select the Change primary DNS suffix when domain membership changes check box.
• Click OK to save the changes, and then click OK to exit the Computer Name Changes dialog box.
• Click OK to close the System Properties dialog box, and then restart the computer for the change to take effect.

Reconfigure the Database mirroring either GUI or T-SQL.

Question 20. What Are The Database Mirroring States?

Answer :

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

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

SUSPENDED:-

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

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

DISCONNECTED:-

•   The partner has lost communication with the other partner.