Configure SQL Server Database Mirroring: Step By Step

Database Mirroring in SQL Server is deployed as a method to increase the availability of a SQL Server database in the event of a disaster. It can only be implemented in databases that have full recovery model and works on per-database basis. In database mirroring, two copies of a single SQL Server database are stored on different servers that are separated physically. While the Primary Server provides database to the clients, the Mirror Server acts as a standby server that takes over the place of physical server in case any accident occurs.

In this write-up, we will discuss the procedure to configure SQL Server Database Mirroring

Procedure to Configure Database Mirroring in SQL Server

The procedure of configuring SQL Server Database Mirroring is mainly divided into two sections:

I. Preparation of server instances to take part in Database Mirroring in SQL Server

For every database mirroring session, following requirements are need to be fulfilled:

1. Separate server instances, residing on separate host systems, should host the primary server, the mirror server, and the witness server.
2. All the server instances need a database mirroring endpoint. If you need to do so, make sure that the endpoint is accessible by other server instances as well.
3. Two types of transport security exist for database mirroring:
• Windows Authentication
• Certificate-based Authentication
4. The network access requirement depends on the form of authentication that is being used:
• If Windows Authentication Is Used

In case the server instances run in different domain user accounts, all the accounts require a login into the master database of the other accounts. In case the login does not exist, the users must create it.

• Is Certificates Are Used

For enabling certificate authentication on a server instance, the administrator must configure all the server instances to use both outbound and inbound connections certificates. It is to be noted that outbound server needs to be configures before inbound connections.

5. Make sure that the logon for the all the database exists on the mirror server.

II. Establishing Database Mirroring in SQL Server

Pre-requisites:

1. Restore all the below-mentioned backups for creating a mirror database by RESTORE WITH NORECOVERY command on all the restore operations:
• Restore the recent full database backup of principal server. Make sure that the principal server used full recovery model at the time of the backup. Moreover, the name of the mirror database must be same as the primary server.
• In case any differential backup of the data has been taken after the restoration of full backup, restore the recent differential backup.
• Do the restoration of all the log backups done since the differential or full backup of the database.
2. Mirroring can be setup by either using Windows Authentication i.e. Transact-SQL or by Database Mirroring Wizard i.e. SQL Server Management Studio. Here, we are going to use SQL Server Management Studio.

Procedure for Configuring SQL Server Database Mirroring Via SQL Server Management Studio

Follow the below-mentioned steps for establishing database mirroring in SQL Server:

1. Open SQL Server Management Studio.
2. Connect to the Primary server.
3. Click on View option and select Object Explorer option from the drop-down menu.

4. Click on the Server Name and expand the server tree.

5. Click on Databases and expand it.

6. Select the database that has to be mirrored.

7. Right click on the database and select Tasks option.

8. Click on Mirror option. Database Properties dialog box with Mirroring page will open.

9. Click on Configure Security option to start the configuration.

10. The Configure Security option will automatically create the database mirroring endpoint on all the server instance and will enter the server network address.
11. Make sure that the below-mentioned conditions exist.
• You are connected to principal server instance.
• Security is configured in a correct manner.
• The TCP address of the primary and mirror servers are specified.
• If the operating mode is High Safety with automatic failover, then make sure the TCP address of the witness server is also specified.
12. Click on Start Monitoring to start the process.
13. In case you want to change the operating mode, you can do so and save the changes by Ok.

Conclusion

With the help of the above-mentioned procedure, you can easily configure SQL Server Database Mirroring.