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.
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:
- 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).
- 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.
- 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).
- You can use SQL Server Enterprise, Standard, or Business Intelligence Edition for this article.
Create a Full backup of the Database to Mirror in the principal server.
Backup the Transactional Log.
Restore the database in the Mirror Server.
Make sure to restore with the NORECOVERY State in the Mirror Server.
Right click on the database and select Tasks>Mirror
Figure 1. The Mirror Task
Press the Configure Security button.
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.
And that’s..that’s … that’s that’s all folks !
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.
- Verify that the Mirror server is in no-recovery mode (check the requirements).
- Verify that that the firewall and activate the ports used in each server. For more information about ports and firewalls, refer to this link.
- 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:
select * from sys.database_mirroring_endpoints
The status description show the status of the endpoint.
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.
ALTER DATABASE db_name SET PARTNER OFF
How to grant connection permissions to the Mirroring Endpoints.
You can also use T-SQL to grant Endpoint permissions.
GRANT CONNECT ON ENDPOINT::Mirroring TO [username]
How to view role, configuration information about Mirroring using T-SQL
select * from sys.database_mirroring
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