SQL Server Database Mirroring

Original: SQL Server database Mirroring

Tags: SQL server/mssql server/Database/dba/Mirroring

Overview

This chapter covers the installation of database mirroring, which is installed in a domain environment.

Environment: SQL Server R2 Enterprise Edition

Domain Server ip:192.168.2.10

Principal database server: Machine name (CLU13); IP (192.168.2.13)

Mirror database server: Machine name (CLU12); IP (192.168.2.12)

Witness server: IP (192.168.2.10)

Directory

• Overview
• Install database Mirroring
  • Backup RESTORE Database
  • Configure Database Mirroring
• Summary

Install database MirroringBackup RESTORE Database

Databases that require database mirroring are backed up by a full database, followed by a log backup that copies the backed up database files and log files to the mirror server disk.

1. Restore the database you just backed up in the mirror server, restore the full backup, restore the data name adventureworks2008r2, restore state Select Restore with NORECOVERY

2. Restore the log file, restore state Select Restore with NORECOVERY

Configure Database Mirroring

3. Log in to the CLU13 server as a domain user, open SQL Server, right-adventureworks2008r2-task-mirroring, click Configure Security

4. This step is to choose whether to configure the witness server, select "Yes" here, you can also select No, you can add a witness after you configure the mirror later

5. The Default check box

6. The principal server is connected by default, click Next

7. Click Connect, because both the principal and the mirror server are joined to the domain, so you can connect as a Windows identity.

8. The witness server is also connected as a Windows identity

9. Create the service account, as this is the test Add Domain administrator user, finally will automatically create the user in the login name

10. Click Finish

11. Start Mirroring

12. The database mirroring configuration with failover is complete

13. Open the Database Mirroring Monitor

Note: The start user of the database service must be a domain user

How to build a domain environment reference:http://www.cnblogs.com/chenmh/p/4444168.html

Application-side Connection Database Mirroring reference:https://technet.microsoft.com/zh-cn/library/ms175484.aspx

Summary

The lack of database mirroring is that the database that is mirrored is inaccessible and cannot share the stress of the subject, so Microsoft added Alwayson,alwayson in SQL Server version 2012 to share the pressure of the primary replica server by configuring read-only routing to access the secondary replica. It is easy to configure database mirroring in a domain environment; Configuring database mirroring in a non-domain environment requires credentials to be encrypted, which is a bit more complex than the domain environment; because it is a test, it is created as a domain administrator and requires control permissions in the actual production environment. If the mirroring error is turned on, check that the 1433,5022 port of the three servers is blocked by the firewall.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

SQL Server Database mirroring