How to Configure Database Mirroring?

Problem: How to Configure Database Mirroring?

Solution: Setting Up Database Mirroring (SQL Server)

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Prerequisites:

      1.     Make Sure the SQL Servers of principal, Mirror and witness are on the same version and additionally principal and mirror should be having the same edition of SQL Server.
2.     Make Sure the Principal database is in full recovery mode.
3.     Take the backup of the database you want to mirror and restore it to the mirroring server you want to use as target with NORECOVERY OPTION.

Step 1: Taking backups of the principal and restoring that on to the mirror as below.

Backup the database On Principal:

BACKUP DATABASE TestUser TO DISK='C:\Testuser.bak'

BACKUP DATABASE TestUser TO DISK='C:\Diff.bak'

BACKUP DATABASE TestUser TO DISK='C:\Transactional.trn'

Restore the database On Mirror:

RESTORE DATABASE TestUser FROM DISK='C:\Testuser.bak' WITH NORECOVERY

RESTORE DATABASE TestUser FROM DISK='C:\Diff' WITH NORECOVERY

RESTORE DATABASE TestUser FROM DISK='C:\Transactional.trn' WITH NORECOVERY

Step 2: Launch the Mirroring Wizard from the SQL Server Management studio and point to the highlighted option selected as below.

Step 3: Once the Mirror option is selected from the previous step, there should a window popped out from the click saying Database properties. In this step Configuring Security to connect through the Server sets (Principal, Mirror and Witness)

Step 4: Once you have selected security in the Configuration dialog box this will open a wizard configuration for the principal, mirror and witness.

Step 5: Include a witness to check the health of the mirroring synchronization, this will help when to switch-over in the high availability fail over.

Step 6: Choose a witness server to configure.

Step 7: Configure the port for creating an endpoint to Principal Server Instance.

Step 8: Configure the port for creating an endpoint to Mirror Server Instance.

Step 9: Configure the port for creating an endpoint to Witness Server Instance.

Step 10: Which services or domain accounts should be used in connecting endpoints through each server.

Step 11: Review the information from connections and configurations for all the server sets.

Step 12: Success: Final step for Configuration of End points.

 

Step 13: Start Mirroring to start the synchronization between servers. 

This step might be giving an error if you have not backed up the databases as specified in the step 1

.

Step 14: after the mirroring is started there should be status of the synchronizing/ed.

Step 15: Launch the mirroring monitor. To see the progress of principal and mirror.  

Step 16: viewing the principal server to see the database properties.

Step 17: viewing the Mirror server to see the database properties.