Database mirroring is an option to improve the availability of a database which supports automatic fail over with no loss of data.
Inter changing of roles like principal and mirror are called role switching.
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.
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:-
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.
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.
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.02 II. RECTIFYING FULLY QUALIFYED NAMES
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.