How to configure SQL server database mirroring

If the primary database fails, we can have a backup replica available with database mirroring. A step-by-step tutorial on database mirroring will be provided in this article.

Servers used

A Principal Server, which will serve as the main database, is required for Database Mirroring. If it fails, the other server (Mirror Server) will be used to replace it. You can use the Principal server to return once the principal has been fixed.

A third server is required if you want automatic failover (when the primary server fails, the Mirror Server is automatically activated to replace the primary server).

Database Mirroring Roles

There are two main operational modes in Database Mirroring:

High-safety mode is a secure and safe mode for switching from the Principal to the Mirror Server. This mode can be automatic (three servers required) or manual (requires 2 servers).

High-performance mode is a faster mode for switching from the Principal to the Mirror Server, but it may result in some data loss.

Requirements

Three SQL Servers or at least three SQL Server Instances are required (three instances can be used for testing purposes only, but it is not recommended for production environments).

For this article, you can use SQL Server Enterprise, Standard, or Business Intelligence Edition.

Create a Full backup of the Database to Mirror in the principal server.

Figure r1. A Full Backup

Figure r2. A Full Backup option

Backup the Transactional Log.

Figure r3. Transactional Log Backup

Restore the database in the Mirror Server.

Figure r4. Database Restoration.

Make sure to restore with the NORECOVERY State in the Mirror Server.

Figure r5. NORECOVERY state.

Getting Started

Right click on the database and select Tasks>Mirror

Figure 1. The Mirror Task

Press the Configure Security button.

Figure 2 The button to start the configuration

The Configure Database Mirroring Security Wizard will be displayed. Press next.

Figure 3. The Database Mirroring Wizard

The first wizard will ask if you want a Witness. If you want the manual failover or a high-performance mirroring, the Witness is not necessary. If you want a high availability mirroring with automatic failover, select the yes option. In this demo, we will use a Witness.

Figure 4. The option to include the Witness

The next option will let you select where to save the security configuration.

Figure 5. Select Server to configure.

In the next option, you will select the ports used. We also have an option to Encrypt the data send from one Server to Another. The Principal Server and the Mirroring Server will be synchronizing data constantly. The Endpoint name will be created here. If you are using the same server with different instances, a different port should be used for each server.

Figure 6. The Endpoint information

For the Mirror server, press connect and specify the connection properties. Also, select the listener port and the Endpoint name. If you are using the same server with different instances, a different port should be used for each server.

Figure 7. Mirror information

If you selected the option to use a Witness. You will be required to press the connect button and select the credentials to connect to the witness Server. Also, select the listener port and the Endpoint name. If you are using the same server with different instances, a different port should be used for each server.

Figure 8. Witness configuration

In the Service Account Window, specify a domain account for the Principal, Witness or Mirror Server.

Figure 9. Service Accounts

The Compete Wizard contains a list of all the configurations used. If you agree, press finish if not, press Back and change the configurations.

Figure 10. Configurations made.

If everything is OK, a Success message will be display. Otherwise, you will receive error messages.

Figure 11. The Success Window.

You will receive a message to start the Mirroring. If you are ready, press the Start Mirroring button.

Figure 12. The Start Mirroring message.

Once you are done, the rest is easy. You can simulate that your Service is down (by stopping the SQL Server Database Engine Service in the Principal Server).

Figure 13. Restarting the Sql service.

You will find that the Mirror Database is active now. If you start the SQL Server service again and stop the Mirror SQL Server Service, the Principal Server will be active now.