Interview Questions.

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

Answer :

Database mirroring is an choice to enhance the supply of a database which supports computerized fail over and not using a lack of data.

Benefits:-

Increases facts safety.
Increases availability of a database.
Improves the provision of the manufacturing database all through enhancements.
Question 2. What Are The Prerequisites For Database Mirroring?

Answer :

Database base need to be in complete restoration version.
Database call equal on both servers.
Server ought to be on same area call.
Mirror database ought to be initialized with principal server.
Oracle DBA Interview Questions
Question 3. What Are The Restrictions For Database Mirroring?

Answer :

Maximum 10 databases in keeping with example can aid on a 32-bit gadget.
Database mirroring is not supported with both go-database transactions or allotted transactions.
Question four. Explain About Principal, Mirror And Witness Servers?

Answer :

Principal Server: - One Server serves the database to patron is known as Principal server and it having authentic records. Can have most effective one Principal Server and it needs to be on a separate server.
Mirror Server: - Other server instance acts as a hot or warm standby server is known as Mirror server and it having copy of database.
Witness Server: - The witness server is an optionally available server and it controls automated failover to the mirror if the important will become unavailable. To help automatic failover, a database mirroring consultation need to be configured in high-availability.
Oracle DBA Tutorial
Question 5. In Which Operations Are Running The Operating Modes?

Answer :

Asynchronous:- Under asynchronous the principle server does now not watch for the reaction from the mirror server after sending log buffer.

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

SQL Database Interview Questions
Question 6. What Are The Operating Modes And Explain Them?

Answer :

High Availability (precept+mirror+witness):- High-availability mode, runs synchronously. Requires a witness Server instance. The Principal server sends the log buffer to the replicate server, and then waits for a response from the replicate server.
Principle isn't available the witness and reflect will decide automated failover .Replicate becomes on-line.

High Protection (precept+replicate):- High-safety mode, runs synchronously. Always dedicate adjustments at each the Principal and Mirror. Automatic failover isn't feasible.

High Performance: - High-overall performance mode, runs asynchronously and the transaction safety set to off. The Principal server does now not watch for a reaction from the mirror server after sending the log buffer. The principal server jogging excellent and fast, but should lose data on the replicate server.

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

Answer :

An endpoint is a network protocol that's used to talk Principal, Mirror and Witness servers over the network.

Creation of an stop point:-

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

as tcp (listener port=5022/5023) for database mirroring (position=companion/witness)

SQL Database Tutorial Database Interview Questions
Question eight. 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 foremost, reflect and Witness servers are 5022, 5023 and 5024.

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

Question nine. Which Trace Flag Is Used In Mirroring?

Answer :

Trace flags are used to temporarily set particular server traits or to replace off/on a specific conduct. 1400 Trace flag is used in mirroring.
To set trace flag for Database mirroring:- Configuration Manager > Right click on server example > Properties > Advanced tab > Startup parameters > -t1400 (add)
SQL DBA Interview Questions
Question 10. In Which Recovery Model We Can Use In Mirroring?

Answer :

In mirroring the important and reflect databases are used simplest complete recovery version.

Question 11. What Is Role-switching?

Answer :

Inter converting of roles like foremost and reflect are known as function switching.

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

Answer :

Alter database <database name> set partner off.

Oracle DBA Interview Questions
Question 13. How To Configure Mirroring?

Answer :

Choose Principal Server, Mirror Server, and optionally available Witness server.
The primary and mirror server instances must be going for walks the equal version both Standard Edition or Enterprise Edition.
The Witness server example can run on SQL Server Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition.
Mirror database requires restoring a recent backup and one or greater T.Log backups of the predominant database (with No healing).
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 indicates update fame and to configure caution 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 reflect server is indicates jogging properly. A pink arrow suggests troubles that want to investigate.

SQL Server Log:- It presents statistics of Mirroring established order and status. If any mistakes occurs it will likely be logged to SQL Server log and Windows event log.

Performance Monitor:- It can provide real-time facts approximately Database mirroring. We can use performance counters to get reputation of the database mirroring which includes Bytes received/sec, Bytes sent/sec, Transaction postpone and many others.

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 fast as possible, the log buffer is written to the transaction log on disk, a method called hardening.

Database Administration Interview Questions
Question sixteen. What Is Log Buffer?

Answer :

A log buffer is a unique region in memory (RAM). SQL Server stores the changes within 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 factor for Witness server > Click oK

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

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

Answer :

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

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

SQL Database Interview Questions
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 certified domain name (FQDN).  Specify the FQDN for every server, and click on Start Mirroring once more.

The syntax for a totally-qualified TCP deal with is:

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

Section 1.01 

Section 1.02 II. RECTIFYING FULLY QUALIFYED NAMES

To View Endpoints:-SELECT * FROM sys.Database_mirroring_endpoints;
Remove present all Endpoints from Principal, Mirror and Witness servers:- DROP ENDPOINT [ENDPOINT_NAME]
Adding "nearby" because the number one DNS suffix as follows:-
Right-click My Computer, after which click Properties. The System Properties conversation field will appear.
Click the Computer Name tab.
Click Change. The Computer Name Changes dialog box will seem.
Click More. The DNS Suffix and NetBIOS Computer Name dialog container will seem.
Enter the correct DNS suffix for the domain.
Select the Change number one DNS suffix while domain membership changes take a look at box.
Click OK to shop the modifications, and then click OK to go out the Computer Name Changes conversation box.
Click OK to close the System Properties conversation field, and then restart the laptop for the exchange to take impact.
Reconfigure the Database mirroring either GUI or T-SQL.

Question 20. What Are The Database Mirroring States?

Answer :

SYNCHRONIZING:-

The contents of the reflect database are lagging behind the contents of the main database. The important server is sending log statistics to the reflect server, which is making use of the modifications to the replicate database to roll it ahead.
At the begin of a database mirroring consultation, the database is in the SYNCHRONIZING state. The essential server is serving the database, and the reflect is attempting to seize up.
SYNCHRONIZED:-

When the mirror server will become sufficiently caught as much as the essential server, the mirroring kingdom adjustments to SYNCHRONIZED. The database remains in this nation as long as the fundamental server keeps to ship changes to the replicate server and the mirror server maintains to use changes to the replicate database.
If transaction protection is set to FULL, automatic failover and guide failover are both supported inside the SYNCHRONIZED kingdom; there may be no information loss after a failover.
If transaction safety is off, some facts loss is constantly possible, even in the SYNCHRONIZED country.
SUSPENDED:-

The reflect replica of the database isn't always to be had. The primary database is jogging without sending any logs to the mirror server, a condition referred to as walking uncovered. This is the nation after a failover.
A session can also come to be SUSPENDED because of redo errors or if the administrator pauses the consultation
SUSPENDED is a chronic state that survives accomplice shutdowns and startups.
PENDING_FAILOVER:-

This country is found most effective on the major server after a failover has began, however the server has not transitioned into the reflect role.
When the failover is initiated, the main database is going into the PENDING_FAILOVER state, speedy terminates any person connections, and takes over the replicate function soon thereafter.
DISCONNECTED:-

  The partner has misplaced communication with the alternative partner.