Sometimes our Database fails and we need to reestablish the Database services immediately. Is there a SQL Server solution for high availability?

With Database Mirroring, we can have a replica of the database ready to be used if the main database fails. In this article, we will give a step-by-step tutorial about Database Mirroring. There are other solutions for high availability like high AlwaysOn, Clusters and Log Shipping, but in this chapter, we will start with Mirroring.

Servers used

For Database Mirroring, you require a Principal Server, which will be used as the main database. If it fails, the other server (Mirror Server) will be used to replace the Principal Server. Once the principal is fixed, you can return using the Principal server.

If you want an automatic failover (when the principal server fails, the Mirror Server will be automatically activated to replace the principal server), a third Server is necessary.

Database Mirroring Roles

In Database Mirroring, there are two main operative modes:

1. High-safety mode is a mode secure and safe to switch from the Principal to the Mirror Server. This mode can be automatic (requires 3 servers) or manual (requires 2 servers).
2. High-performance mode is a faster mode to switch from the Principal to the Mirror Server, but some data loss can occur with this mode.

Requirements

1. You need three SQL Servers or at least three SQL Server Instances (three instances can be used for testing purposes only, but it is not recommended for production environments).
2. You can use SQL Server Enterprise, Standard, or Business Intelligence Edition for this article.

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

   
   Figure r1. A Full Backup

   
   Figure r2. A Full Backup option

4. Backup the Transactional Log.

   
   Figure r3. Transactional Log Backup

5. Restore the database in the Mirror Server.

   
   Figure r4. Database Restoration.

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

   
   Figure r5. NORECOVERY state.

Getting Started

1. Right click on the database and select Tasks>Mirror

   
   Figure 1. The Mirror Task

2. Press the Configure Security button.

   
   Figure 2 The button to start the configuration

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

   
   Figure 3. The Database Mirroring Wizard

4. 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

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

   
   Figure 5. Select Server to configure.

6. 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

7. 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

8. 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

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

   
   Figure 9. Service Accounts

10. 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.

11. 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.

And that’s..that’s … that’s that’s all folks !

Error messages

A famous error message is this one:

Figure 14. Typical error message

Error 1418, The network address 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.

If you have this error message, you can verify the following steps.

1. Verify that the Mirror server is in no-recovery mode (check the requirements).
2. Verify that that the firewall and activate the ports used in each server. For more information about ports and firewalls, refer to this link.
3. Make sure that the port is not in use. For more information, refer to this link.

Some useful tips for Database Mirroring

How to monitor the Database Mirroring using the UI

You can monitor your Database Mirroring using the Launch Database Mirroring Monitor

Figure 15. The Database Mirroring Monitor Option

How to check the status of the Endpoint for mirroring

You can also, check the mirror status using the sys.database_mirroring_endpoints view:

The status description show the status of the endpoint.

Figure 16. The status of the endpoints.

How to remove the database from mirroring using T-SQL

If for some reason you can not remove the database from UI, you can use the T-SQL for that purpose.

How to grant connection permissions to the Mirroring Endpoints.

You can also use T-SQL to grant Endpoint permissions.

How to view role, configuration information about Mirroring using T-SQL

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023