How to Configure MS SQL Server Database Mirroring?

Read this blog to understand the configuration procedure of SQL Server high availability solution – Database Mirroring.

Microsoft SQL Server provides its users the ultimate solution or service to restore database with the help of the Database Mirroring feature. It is a helpful aid when the main database gets failed as users can then make use of the replica of the database created via the Database mirroring.

Have a look at the main components of the Database Mirroring system

• Principal database server or the source database used for mirroring configuration
• Mirror database server or the destination server on a separate server to be restored with NORECOVERY only
• Endpoints are the communications between Principal and Mirror database servers
• Port Number is by default 5022 but can be modified during configuring the database

There are two operation modes in Database mirroring known as High-safety mode (manual or automatic) which is a secured mode used to switch from Principal Server to the Mirror Server while the other one the High-performance mode delivers quick switch from Principal Server to Mirror Server but on account of risk of data loss.

How to configure database mirroring in SQL Server?

All SQL Server users should know the concept and method to configure the database mirroring in their SQL Server account to make use of the database replica in the situations where the original SQL database gets fail.

But before that, let us go through some important and not to be ignored pre-configuration steps for convenient process ahead.

Know the pre-requirements

Here is the good-to-go list for the pre-configuration tasks for the database mirroring.

• Confirm that both the servers (Principal Server and Mirror Server) are on SQL Server 2005 SP1 and later versions only. Also, the chosen SQL Server version or edition is same for both servers.
• Principal and Mirror Servers must be running in FULL recovery model and holds the same database name.
• On the Mirror database, restore with NORECOVERY mode from the full backup and a transaction log backup is must.
• To configure Automatic Failover, there is a need to set up a witness server (choice of any SQL Server version)

Once, all the above conditions or tasks are fulfilled, the SQL Server administrator can head to the configuration of the database mirroring in SQL Server.

Procedure to configure database mirroring in SQL Server

Follow the provided steps in the given sequence to complete the configuration of database mirroring in SQL Server.

1. First, connect to the Principal Server and take full backup of the database along with the transaction log backup of it.
   The script to generate the backup is
   USE [master]
   BACKUP DATABASE [database name] TO DISK = N’\\DC.Local\Backup\database name.bak’ WITH COMPRESSION, STATS = 5
   GO
   USE [master]
   BACKUP Log [database name] TO DISK = N’\\DC.Local\Backup\ Adv_Logbackup.trn’ WITH COMPRESSION, STATS = 5
   GO
2. Now, restore the taken backup to the Mirror Server with NORECOVERY mode using this script.
   USE [master]
   RESTORE DATABASE [database name] FROM DISK = N’\\DC.Local\Backup\database name.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5
   GO

   RESTORE LOG [database name] FROM DISK = N’\\DC.Local\Backups\Adv_Logbackup.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
   GO

3. Connect to the Principal Server. Here, launch the SQL Server Management Studio.
4. Now, follow click on the SQL Server instance>expand Databases>right-click on the database to be mirrored>select Tasks>select Mirror.
5. The Database Properties page will get opened. Click on the Configure Security option.
6. Next, the Configure Database Mirroring Security Wizard will get open. Click Next.
7. On the next page, click Yes under the option Do you want to configure security to include a witness server?. And, click Next.
8. On the Choose Servers to Configure section, select the Witness server instance checkbox and click Next.
9. Next the Principal Server Instance screen will be opened. Here, confirm the instance name, Listener port number as 5022, add any endpoint name and select the checkbox to encrypt data sent through this endpoint. After that, click Next.
10. On the opened Mirror Server Instance screen, specify the exact details as in the previous step and click Next.
11. Now, the Witness Server Instance screen will be opened. Here, select the Witness server instance, add same Listener port number and endpoint name as done previously and click Next.
12. On the opened Service Accounts screen, provide service accounts name for Principal Server, Mirror Server and Witness Server. Then, click Next.
13. On Complete the Wizard screen, ensure all the provided details for the three servers and click Finish.
14. Endpoints configuration for Principal, Mirror and Witness servers will get start.
15. After the configuration is completed, it will show the status as Success. You can export the log report using the Report option.
16. Now, on the Database Properties page, a dialogue box with information of network address for all the servers along with the Operating mode information. Here, click on the Start Mirroring option to start the database mirroring configuration.
17. The database mirroring will be started and the status of the synchronization of database can be seen on the SQL Server Management Studio Databases section.
18. When the status shows synchronized, then the database mirroring in SQL Server is completed.

Hence, this is the long but feasible process to configure database mirroring in SQL Server if all the above steps described above are attempted correctly.

We recommend all an alternative solution to recover data from their corrupt SQL Server database files via the most trustable and powerful SQL Recovery. It repairs, recovers SQL data, preview the database objects and lets users to choose from it to save to their use. Users can recover data from any SQL Server version database file easily. Reach to the software website quickly to know in detail about the tool.

Download Now

Conclusion

“Database Mirroring” solution is an unparalleled advantage provided by SQL Server to its administrators and users. With basic introduction and pre-required tasks, the full process of configuration of database mirroring in SQL Server is explained in detail with all necessary steps and towards the end, a full-proof solution for data recovery from the corrupt SQL Server database from any version is also proposed.