Interview Questions on SQL Server Database Mirroring

1) What is default port of Database Mirroring Endpoint?

Ans : 5022

2) Database Mirroring comes with which edition?

Ans: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400

3) When I configure mirroring I’m receiving the below errror,

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

Ans: The fully qualified computer name of each server can be found running the following from the command prompt:

IPCONFIG /ALL

Concatenate the “Host Name” and “Primary DNS Suffix”

Host Name . . . . . . . . . . . . : A

Primary Dns Suffix . . . . . . . : corp.mycompany.com

Then FQDN of your computer name is just A.corp.mycompany.com.

4) How to enable mirroring by Script ?

Ans: – Specify the partner from the mirror server

ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://A.corp.mycompany.com:5022';

– Specify the partner from the principal server

ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://B.corp.mycompany.com:5022';

Note: Replace the dbname before using the above script

5) How to disable mirroring by script?

Ans: ALTER DATABASE [AdventureWorks] SET PARTNER OFF

Note: Replace the dbname before using the above script

6) How to do manual failover to Mirror when principle is working fine?

Ans: ALTER DATABASE <DB Name> SET PARTNER FAILOVER

7) Why I’m getting the below error message while configuring database mirroring?

Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring

Ans : You need to restore the Full backup from principal server using With NoRecovery option and also one transactional log backup from principal server using With NoRecovery option and then start configuring mirroring.

8) Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?

Ans: Nope its not possible, both principal and mirror should have same edition

9) Is it possible to take backup of mirrored database in mirror server?

Ans: No

10) Is it possible to perform readonly operation at mirrored database in mirror server?

Ans: Yes, You can create database snapshot for the same

11) Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint.

Ans: No

12) Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping

Ans: No

13) How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec.

Ans: ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30

Note: Before using the script change the dbname.

14) What status of mirroring has if secondary is down?

Ans: If secondary is down principle or Mirror show status disconnected

15) What status of mirroring has if principle is down?

Ans: If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring

16) What status of mirroring has if mirroring is paused?

Ans: Is mirroring is set to paused from principle then then both principle & mirror in suspending

17) How to bring mirror DB online if Principle is down?

Ans: ALTER DATABASE <DB Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

If you try to do failover like normal situation when principle is online [ALTER DATABASE <DB Name> SET PARTNER FAILOVER] then you will receive below error.

ALTER DATABASE <DB Name> SET PARTNER FAILOVER

Msg 1404, Level 16, State 10, Line 1

The command failed because the database mirror is busy. Reissue the command later.

18) System Store Procedure to monitor Mirroring?

Ans: MSDB.SYS.SP_DBMMONITORRESULTS

19) What are different possible Mirroring Stats?

Ans:

SYNCHRONIZING

SYNCHRONIZED

SUSPENDED

PENDING FAILOVER

DISCONNECTED