Explore topic-wise InterviewSolutions in Current Affairs.

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.

SYNCHRONIZING:-

SYNCHRONIZED:-

SUSPENDED:-

PENDING_FAILOVER:-

DISCONNECTED:-

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.

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

Reconfigure the Database mirroring either GUI or T-SQL.

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.

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.

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)

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)

A log BUFFER is a SPECIAL LOCATION in memory (RAM). SQL Server stores the changes in the database log buffer.

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

As QUICKLY as possible, the LOG buffer is WRITTEN to the TRANSACTION log on disk, a process called hardening.

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

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

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:-

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

ALTER DATABASE <database name&GT; set PARTNER off.

Alter database <database name> set partner off.

INTER changing of roles like PRINCIPAL and MIRROR are called role SWITCHING.

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.

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

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

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.

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.

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)

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)

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.

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.

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.

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.

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

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

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.

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

Benefits:-