Daniel Calbimonte

How to create a Database Mirroring

March 6, 2015 by

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

See more

For SQL Server recovery, consider ApexSQL Recover, a tool that recovers deleted and truncated data, objects and data lost due to drop operations, restores deleted and reads online BLOBs as files

References


Daniel Calbimonte

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 is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
SQL Recovery

About 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 is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

14,558 Views