Configuring Database Mirroring in SQL Server

Database mirroring is a solution for increasing database availability. Here’s a step-by-step guide to configure mirroring in SQL Server:

Step 1: Take a Full Backup of the Principal Database

sql
BACKUP DATABASE [Database_Name] TO DISK = 'Path\Backup.bak';

Step 2: Take a Transaction Log Backup of the Principal Database

sql
BACKUP LOG [Database_Name] TO DISK = 'Path\Backup.trn';

Step 3: Remove the Recovering State of the Mirror Database (if applicable)

sql
RESTORE DATABASE [Database_Name] WITH RECOVERY;

Step 4: Ensure the Backup Folder on the Principal Server is Shared and Accessible from the Mirror Server

Step 5: Restore the Full Backup to the Mirror Server with the NORECOVERY Option

sql
RESTORE DATABASE [Database_Name] FROM DISK = N'\\Principal\Backup\Backup.bak' WITH NORECOVERY;

Step 6: Restore the Transaction Log Backup to the Mirror Server with the NORECOVERY Option

sql
RESTORE LOG [Database_Name] FROM DISK = N'\\Principal\Backup\Backup.trn' WITH FILE = 1, 'NORECOVERY, NOUNLOAD, STATS = 10;

Step 7: Configure Database Mirroring from the Principal Server

1. Right-click the database.
2. Choose "Tasks" > "Mirror" or "Properties" > "Mirroring".

Step 8: Launch the Configure Database Mirroring Security Wizard

• Click the "Configure Security" button.
• Click "Next" if the intro screen appears.

Step 9: Skip Witness Server Configuration

• Select "No" for "Do you want to configure security to include a witness server instance".
• Click "Next" to continue.

Step 10: Configure the Principal Server Instance

• Create an endpoint named "Mirroring" with a Listener Port of 5022.
• Click "Next" to continue.

Step 11: Configure the Mirror Server Instance

• Click "Connect..." and select the mirror server.
• Click "Next" to continue.

Step 12: Service Accounts Configuration

• Leave the fields blank if all server instances use the same domain account.
• Click "Next".

Step 13: Complete the Wizard

• Review the summary of the configuration.
• Click "Finish".

Step 14: Start Mirroring

• Click "Start Mirroring".
• If everything is set up correctly, database mirroring will start successfully and will be fully synchronized.

Backout Plan

1. Remove the mirror configuration.
2. Set the mirror database with RECOVERY.

Test Plan

Verify that Both Servers are Listening on the Same Port

sql
SELECT type_desc, port FROM sys.tcp_endpoints;

Verify Database Mirroring Status on Both Servers

sql
SELECT state_desc FROM sys.database_mirroring_endpoints;

• The state_desc column on both the Principal and Mirror server should be STARTED.

Verify that Roles are the Same on Both the Principal and Mirror Server

sql
SELECT role FROM sys.database_mirroring_endpoints;

By following these steps, you can configure database mirroring in SQL Server to ensure high availability and disaster recovery.