Database Mirroring is the concept of making duplicate database. The reason why a database is mirrored is to make sure the database is available at all times even in case of down time or network issues or any data loss. Do search in wisdom jobs for Database Mirroring job listings for full time and part time positions updated today. If you are looking for job then go check out interview questions page to get more information on the kind and level of questions you will come across during the interview. Various jobs were present in all over India. Wisdomjobs interview questions provides you with complete guide on the Database Mirroring job interview questions and answers and makes you prepared for winning job interview.
Database mirroring is an option to improve the availability of a database which supports automatic fail over with no loss of data.
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.
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.
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)
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.
In mirroring the principal and mirror databases are used only full recovery model.
Inter changing of roles like principal and mirror are called role switching.
Alter database <database name> set partner off.
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:-
As quickly as possible, the log buffer is written to the transaction log on disk, a process called hardening.
A log buffer is a special location in memory (RAM). SQL Server stores the changes in the database log buffer.
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 > Remove TCP address from the Witness > Click oK.
T-SQL:- ALTER DATABASE Adventure Works SET WITNESS OFF.
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.
Database Mirroring Related Interview Questions
|Oracle DBA Interview Questions||SQL Database Interview Questions|
|Database Interview Questions||SQL DBA Interview Questions|
|SQL Interview Questions||Database Administration Interview Questions|
|Database Optimization Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.