Database Mirroring Interview Questions & Answers
Q. What is default port of Database Mirroring Endpoint?
A: 5022
Q. Database Mirroring comes with which edition?
A: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400
Q. How to enable mirroring by Script ?
A: – Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://MirrorServer.mycompany.com:5022';
– Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://PrincipalServer.mycompany.com:5022';
Q. How to disable mirroring by script?
A: ALTER DATABASE [AdventureWorks] SET PARTNER OFF
Q.How to do manual failover to Mirror when principle is working fine?
A: ALTER DATABASE SET PARTNER FAILOVER
Q. You are getting the below error message while configuring database mirroring? Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
A: 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
Q. Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?
A: Nope its not possible, both principal and mirror should have same edition.
Refer the KB article
Q. How to bring mirror DB online if Principle is down?
A: ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
If you try to do failover like normal situation when principle is online [ALTER DATABASE SET PARTNER FAILOVER] then you will receive below error.
ALTER DATABASE SET PARTNER FAILOVER
Msg 1404, Level 16, State 10, Line 1
The command failed because the database mirror is busy. Reissue the command later.
Q. System Store Procedure to monitor Mirroring?
A: MSDB.SYS.SP_DBMMONITORRESULTS
Q. In Database mirroring, Let suppose Primary DB log file is not getting truncated even after taking log backup. What is the issue?
A: In this scenario it seems there is some communication issue between the Primary & Mirror database, due to following reasons:
- Network issue between Primary & Mirror Servers.
- Endpoints are not able to communicate, or could be port issue.
- Mirroring got paused or got interrupted due to unexpected reboot.
Q. In Database mirroring, Let suppose Primary DB log file is not getting truncated even after taking log backup and Mirror DB is showing as Suspended state. What action will you take to re-sync the mirroring?
A: To fix this issue, will identify cause by checking the following things:
- Check for the Network connectivity.
- Check both DB Server endpoints are working successfully.
- Check for the Mirror DB status and if it is suspended then have to re-configure the mirroring.
Q. In Database mirroring, If we Add new FileGroup and create new file under the FileGroup. Will that change also reflect on Mirror database?
A: Yes it will reflect the changes to mirror database as well. Just have to make sure the file path and drive of the new file we are creating, same path should be available on the mirror DB Server as well.
Q. What is default port of Database Mirroring Endpoint?
A: 5022
Q. Database Mirroring comes with which edition?
A: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400
Q. How to enable mirroring by Script ?
A: – Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://MirrorServer.mycompany.com:5022';
– Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://PrincipalServer.mycompany.com:5022';
Q. How to disable mirroring by script?
A: ALTER DATABASE [AdventureWorks] SET PARTNER OFF
Q.How to do manual failover to Mirror when principle is working fine?
A: ALTER DATABASE
Q. You are getting the below error message while configuring database mirroring? Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
A: 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
Q. Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?
A: Nope its not possible, both principal and mirror should have same edition.
Refer the KB article
Q. How to bring mirror DB online if Principle is down?
A: ALTER DATABASE
If you try to do failover like normal situation when principle is online [ALTER DATABASE
ALTER DATABASE
Msg 1404, Level 16, State 10, Line 1
The command failed because the database mirror is busy. Reissue the command later.
Q. System Store Procedure to monitor Mirroring?
A: MSDB.SYS.SP_DBMMONITORRESULTS
Q. In Database mirroring, Let suppose Primary DB log file is not getting truncated even after taking log backup. What is the issue?
A: In this scenario it seems there is some communication issue between the Primary & Mirror database, due to following reasons:
- Network issue between Primary & Mirror Servers.
- Endpoints are not able to communicate, or could be port issue.
- Mirroring got paused or got interrupted due to unexpected reboot.
Q. In Database mirroring, Let suppose Primary DB log file is not getting truncated even after taking log backup and Mirror DB is showing as Suspended state. What action will you take to re-sync the mirroring?
A: To fix this issue, will identify cause by checking the following things:
- Check for the Network connectivity.
- Check both DB Server endpoints are working successfully.
- Check for the Mirror DB status and if it is suspended then have to re-configure the mirroring.
Q. In Database mirroring, If we Add new FileGroup and create new file under the FileGroup. Will that change also reflect on Mirror database?
A: Yes it will reflect the changes to mirror database as well. Just have to make sure the file path and drive of the new file we are creating, same path should be available on the mirror DB Server as well.
No comments:
Post a Comment