13.1 Preparing Database Mirroring

13.1 Preparing Database Mirroring

The following experiment illustrates the process of configuring database mirroring.

The existing environment is as follows:

Server name	Role
SQLSVR1	Principal server
SQLSVR2	mirror server
SQLSVR3	Witness server

The database that needs to be configured for database mirroring is SQLDB01.

13.1.1 Prerequisites

Because the principal server needs to transfer transaction log records to the mirror server, the database must be the full recovery model.

Database mirroring does not support FILESTREAM. You cannot create a FILESTREAM filegroup on the principal server. Database mirroring cannot be configured for databases that contain FILESTREAM filegroups.

On a 32-bit operating system, database mirroring supports a maximum of 10 databases per server instance due to the number of worker threads that are consumed by each database mirroring session.

For best performance, we recommend that you use a private network for database mirroring.

13.1.2 opening the Database Mirroring Configuration window

There are two ways to open the configuration window for database mirroring.

The first option is to select the Mirroring tab directly in the Database Properties window.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6D/AE/wKiom1VpT4qC-hK8AAK77zzD6KU527.jpg "title=" Database properties. png "alt=" wkiom1vpt4qc-hk8aak77zzd6ku527.jpg "/>

In the second way, you can jump to the Mirroring tab of the database properties by selecting a principal database First, then selecting Tasks, then mirroring in the context menu of the primary database.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/AE/wKiom1VpSnTg56gcAAQ-RJIqSlg766.jpg "title=" task Mirroring. png "alt=" wkiom1vpsntg56gcaaq-rjiqslg766.jpg "/>

13.1.3 Initializing a mirrored database

When creating a mirrored database on the mirror server, be sure to specify the same database name with NORECOVERY to restore the principal database backup.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6D/AA/wKioL1VpTPegOKAAAAMZiQ3qz2c802.jpg "title=" Restore options. png "alt=" wkiol1vptpegokaaaamziq3qz2c802.jpg "/>

If the principal database creates a series of transaction log backups after performing a full backup, the principal database cannot pass this part of the transaction log to the mirror database because the transaction logs have been truncated in the log file of the principal database, which causes the database mirroring initialization to fail.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6D/AE/wKiom1VpUJDg-zkNAAEiwYA0Pvs322.jpg "title=" Transaction log backups. png "alt=" wkiom1vpujdg-zknaaeiwya0pvs322.jpg "/>

Therefore, this part of the transaction log must be restored to the mirror database through with NORECOVERY.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/AE/wKiom1VpVHnAkeU7AARWP0wXJWQ288.jpg "style=" float: none; "title=" Transaction log restore 1.png "alt=" Wkiom1vpvhnakeu7aarwp0wxjwq288.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/AA/wKioL1VpVg-yLHt3AAJQ0Ty96kc458.jpg "style=" float: none; "title=" Transaction log restore 2.png "alt=" Wkiol1vpvg-ylht3aajq0ty96kc458.jpg "/>

Tips:

If possible, the path to the mirror database (including the drive letter) is as far as the path to the principal database. If the file layout must be different, you must include the MOVE option in the RESTORE statement. For example, if the principal database is on an "F:" Drive, the mirror system does not have an "F:" Drive.

This article from "SQLServer2014 series" blog, declined reprint!

13.1 Preparing Database Mirroring