13.1 Preparing Database Mirroring

Source: Internet
Author: User

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 "/>


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.