SQL Server: Database Mirroring Interview Questions and Answers (Day-4)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

How can we create an end point using SQL script?

What is the default of end points (port numbers) of principal, mirror and witness servers?

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

Can we use a mirror database as read-only database for reporting purposes?

You can take advantage of a mirror database that you are maintaining for availability purposes to offload reporting. To use a mirror database for reporting, you can create a database snapshot on the mirror database and direct client connection requests to the most recent snapshot.

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

No, It is not possible.

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

No:

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

No:

How to know the database mirroring connection time out in Mirroring?

What is Automatic Page Repairing?

Automatic page repair is supported by database mirroring. After certain types of errors corrupt a page, making it unreadable, a database mirroring partner (principal or mirror) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error, this whole process is called Automatic Page Repair.

What are the page types which are not repairable using Automatic Page repair option?

File header page (page ID 0).

Page 9 (the database boot page).

Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.

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

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.

How you fix the below error?

Mirroring Error 1418: The server network address “%.*ls” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

• Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).

• Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).

• Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023”.

• Suggestion 4: Make sure your firewall is turned off.

• Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.

• Suggestion 6: Try the following command as one of the last options. GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

• Suggestion 7: Delete the end points and recreate them.

Generally, when you are getting errors during the Database Mirroring session?

• When there is a network then mirroring endp piont needs to be restarted.
• Installing Service Packs while installing SQL Server or windows patching.
• Adding a ndf file to the primary Server.
• Moving databases between the Drives.
• Shrinking the log(ldf) files.

Can multiple databases on the same instance be in mirroring sessions?

Yes, but there are limitations, based on the hardware of your servers.

If I set up mirroring, do I still have to take full, differential, or transaction log backups?

Yes! Mirroring increases the availability of your databases. It is not a substitute for regular backups, however.

How does index maintenance (rebuilds) affect the mirror?

Transactions that fill up the log can affect the performance of mirroring. When the amount of information in the log increases, the amount of information that needs to be sent to and committed to the mirror increases also. If performance is crucial, you may want to do index maintenance more frequently, so it takes less time. If large transactions such as batch inserts are affecting performance, break those into smaller transactions.

T-SQL Script to manual failover for Mirror database.

T-SQL Script to bring mirror DB online if Principle is down.