Top 20 Database Mirroring Interview Questions You Must Prepare 19.Mar.2024

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.

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

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

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

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

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

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

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)

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, Traction 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

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

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.

  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.

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.

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

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 traction 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 traction 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 tritioned 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.

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.

Alter database <database name> set partner off.

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)

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.

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