Interview Questions.

Q1. What Is Database Mirroring? What Are The Benefits Of That?

Database mirroring is an option to improve the availability of a database which supports computerized fail over and not using a loss of statistics.

Benefits:-

Increases data safety.

Increases availability of a database.

Improves the supply of the manufacturing database all through upgrades.

Q2. What Is Role-switching?

Inter converting of roles like most important and replicate are referred to as role switching.

Q3. Which Trace Flag Is Used In Mirroring?

Trace flags are used to quickly set particular server traits or to exchange off/on a particular conduct. 1400 Trace flag is utilized in mirroring.

To set hint flag for Database mirroring:- Configuration Manager > Right click on server instance > Properties > Advanced tab > Startup parameters > -t1400 (upload)

Q4. In Which Recovery Model We Can Use In Mirroring?

In mirroring the primary and mirror databases are used best full recovery model.

Q5. What Is Hardening?

As fast as possible, the log buffer is written to the traction log on disk, a procedure referred to as hardening.

Q6. What Are The Restrictions For Database Mirroring?

Maximum 10 databases per instance can support on a 32-bit machine.

Database mirroring isn't always supported with both move-database tractions or distributed tractions.

Q7. What Are The Operating Modes And Explain Them?

High Availability (precept+replicate+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 to be had the witness and mirror will determine automatic failover .Replicate becomes online.

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

High Performance: - High-overall performance mode, runs asynchronously and the traction safety set to off. The Principal server does not look ahead to a response from the mirror server after sending the log buffer. The predominant server running fine and rapid, however should lose facts on the mirror server.

Q8. What Is End Point? How U Create End Point?

An endpoint is a network protocol that is used to speak Principal, Mirror and Witness servers over the community.

Creation of an quit point:-

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

as tcp (listener port=5022/5023) for database mirroring (role=associate/witness)

Q9. How To Monitoring Mirroring?

There are six techniques are available for monitoring the Database Mirroring:

Database Mirroring Monitor:- Database Mirroring Monitor is a GUI device that indicates update fame and to configure warning thresholds.

To open DM Monitor:- Right click on on Principal Database > Tasks > Select Launch Database Mirroring Monitor.

SQL Server Management Studio:- A inexperienced arrow at the replicate server is indicates running well. A purple arrow shows troubles that want to investigate.

SQL Server Log:- It presents records of Mirroring established order and standing. If any errors happens it is going to be logged to SQL Server log and Windows occasion log.

Performance Monitor:- It can provide actual-time records about Database mirroring. We can use performance counters to get reputation of the database mirroring such as Bytes obtained/sec, Bytes sent/sec, Traction postpone and so forth.

Profiler:- Profiler many activities are supplying the repute of the Database mirroring

System Stored Procedures:-

sp_dbmmonitorupdate tracking

sp_dbmmonitorchange tracking

sp_dbmmonitorhelpmonitoring

sp_dbmmonitordropmonitoring

Q10. What Are The Prerequisites For Database Mirroring?

Database base must be in full restoration model.

Database call same on both servers.

Server must be on equal domain name.

Mirror database must be initialized with most important server.

Q11. In Which Operations Are Running The Operating Modes?

Asynchronous:- Under asynchronous the precept server does not watch 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 after which waits for an acknowledgement from the replicate server.

Q12. Explain About Principal, Mirror And Witness Servers?

Principal Server: - One Server serves the database to client is referred to as Principal server and it having original statistics. Can have best one Principal Server and it must be on a separate server.

Mirror Server: - Other server example acts as a warm or warm standby server is known as Mirror server and it having replica of database.

Witness Server: - The witness server is an non-compulsory server and it controls automatic failover to the replicate if the essential becomes unavailable. To assist computerized failover, a database mirroring consultation have to be configured in excessive-availability.

Q13. How To Remove A Witness Server From Database Mirroring?

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

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

Q14. What Is Log Buffer?

A log buffer is a special region in memory (RAM). SQL Server stores the modifications inside the database log buffer.

Q15. What Are The Database Mirroring States?

SYNCHRONIZING:-

The contents of the reflect database are lagging behind the contents of the predominant database. The predominant server is sending log facts to the replicate server, that 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 kingdom. The important server is serving the database, and the replicate is attempting to trap up.

SYNCHRONIZED:-

When the mirror server turns into sufficiently caught up to the most important server, the mirroring nation changes to SYNCHRONIZED. The database stays in this kingdom so long as the essential server maintains to send adjustments to the replicate server and the mirror server continues to apply modifications to the reflect database.

If traction protection is set to FULL, computerized failover and guide failover are each supported in the SYNCHRONIZED nation; there's no statistics loss after a failover.

If traction safety is off, a few statistics loss is always possible, even within the SYNCHRONIZED state.

SUSPENDED:-

The replicate copy of the database isn't always to be had. The important database is walking with out sending any logs to the replicate server, a situation known as jogging exposed. This is the state after a failover.

A session also can emerge as SUSPENDED due to redo mistakes or if the administrator pauses the consultation

SUSPENDED is a persistent nation that survives partner shutdowns and startups.

PENDING_FAILOVER:-

This state is determined simplest at the principal server after a failover has began, but the server has not tritioned into the mirror role.

When the failover is initiated, the primary database goes into the PENDING_FAILOVER kingdom, fast terminates any person connections, and takes over the replicate position soon thereafter.

DISCONNECTED:-

  The partner has lost communique with the opposite associate.

Q16. How To Setup Fully Qualified Names For Database Mirroring?

FQDN Error:

One or more of the server community addresses lacks a fully certified area call (FQDN).  Specify the FQDN for every server, and click on Start Mirroring again.

The syntax for a completely-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 current all Endpoints from Principal, Mirror and Witness servers:- DROP ENDPOINT [ENDPOINT_NAME]

Adding "neighborhood" because the primary DNS suffix as follows:-

Right-click on My Computer, after which click on Properties. The System Properties conversation field will appear.

Click the Computer Name tab.

Click Change. The Computer Name Changes conversation box will appear.

Click More. The DNS Suffix and NetBIOS Computer Name conversation container will appear.

Enter the correct DNS suffix for the area.

Select the Change primary DNS suffix whilst area club changes take a look at container.

Click OK to shop the modifications, after which click OK to exit the Computer Name Changes conversation box.

Click OK to close the System Properties conversation container, and then restart the computer for the exchange to take impact.

Reconfigure the Database mirroring both GUI or T-SQL.

Q17. What Is The Syntax To Stop The Database Mirroring?

Alter database <database name> set associate off.

Q18. How To Set A Witness Server To Database Mirroring?

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.Local:5024' (Do this from the Principal Server)

Q19. What Is The Default Of End Points (port Numbers) Of Principal, Mirror And Witness Servers? How To Find The Port Numbers?

The default port numbers of principal, reflect and Witness servers are 5022, 5023 and 5024.

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

Q20. How To Configure Mirroring?

Choose Principal Server, Mirror Server, and non-compulsory Witness server.

The important and mirror server instances have to be strolling the same edition 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 more T.Log backups of the primary database (with No recovery).