SQL Server 2012 Database Mirroring configuration Full article

Database mirroring is a solution that improves the availability of SQL Server databases. Mirroring is implemented on a per-database basis and applies only to databases that use the full recovery model. Database mirroring maintains two copies of a database that must reside on a different instance of the SQL Server Database engine server. Typically, these server instances reside on computers in different locations. When you start a database mirroring operation on a database, a relationship is formed between these server instances, called a database mirroring session. One of the server instances makes the database service to the client (the "principal server"), and the other server instance acts as a hot standby or warm standby server ("mirror server") based on the configuration and state of the mirroring session. High-safety mode with automatic failover requires the use of a third server instance, known as a witness server.

• Principal server (principal server)

In database mirroring, it refers to the partner that the database currently belongs to as the principal database.

• mirror server (mirror server)

In a database mirroring configuration, the server instance on which the mirrored database resides.

• Witness server (Witness)

Used only in high-safety mode, an optional instance of SQL Server that enables the mirror server to identify when an automatic failover is to be initiated. Unlike these two failover partners, the witness server is not available for the database. The unique role of the witness server is to support automatic failover.

All database mirroring sessions support only one principal server and one mirror server. Shows the configuration:

Shows the configuration that contains the witness server:

For more information on the concept of SQL database mirroring, please refer to the TechNet website below:

https://technet.microsoft.com/zh-cn/library/ms189852 (v=sql.110). aspx

************************************************************************************* **** * ***********

The configuration of the entire SQL Server 2012 database image is divided into the following 6 small chapters

• Environment preparation

• Installing database Features

• Enable TCP/IP protocol and REMOTEDAC

• Database Preparation Work

• Database Mirroring Configuration

• Functional verification

*****************************************************************************************

1. Environmental preparedness

1.1 The server list used for this demo is as follows:

No.	Host Name	Server role	Operating system	IP Address
1	DC01.huangjh.com	Ad/dns	Windows Server R2	192.168.8.8/24
2	SQL01.huangjh.com	Database server Subject	Windows Server R2 SQL Server Enterprise SP1	192.168.8.10/24
3	SQL02.huangjh.com	Database server Mirror	Windows Server R2 SQL Server Enterprise SP1	192.168.8.20/24
4	SQL03.huangjh.com	Database server Witness	Windows Server R2 SQL Server Enterprise SP1	192.168.8.30/24

1.2 Prepare a Domain control server and three database servers, configure the network and join the domain huangjh.com

1.3 Three SQL Servers need to open 1433 and 5022 ports, in order to facilitate the experiment this demo direct manually shut down the firewall

2. Install database Features

2.1 In SQL01, SQL02, and SQL03 three database servers, open Server Manager and follow the wizard to complete the installation of the. Net Framework 3.5

2.2 In SQL01, SQL02, and SQL03 three database servers insert and run SQL Server respectively Enterprise SP1 Setup, click "Install"-"New SQL Server standalone installation or add features to an existing installation", complete the installation of SQL features according to the wizard

2.3 On the "Feature Selection" page, tick the required features according to the actual application needs, next

2.4 On the "Server Configuration" page, modify the service account to be a domain account (ensure that the account password never expires), the startup type is "Automatic"

2.5 Successful installation of database functions based on wizards in SQL01, SQL02, SQL03

3. Enable the TCP/IP protocol and REMOTEDAC

3.1 Open SQL Server Configuration Manager in SQL01, SQL02, SQL03, respectively, enabling the TCP/IP protocol

3.2 Open SQL Server Management Studio in SQL01, SQL02, SQL03, and right-click instances to select "Facets"

3.3 Select surface Area Configurator-Change the value of the property "remotedacenabled" to "True"

4. Database preparation (creating, backing up, and restoring databases)

4.1 In SQL01 (principal server) right-click "Database"-"New Database"

4.2 Enter on the "General" page Database name

4.3 on the "Options" page, ensure that the recovery mode is "full"

4.4 Right-click "DB01" to select "Tasks"-"Backup"

4.5 Backup Type Select "Full" and click "OK"

4.6 Click "OK" when the backup is completed successfully

4.7 Select the backup type "transaction log" and click "OK" to complete the backup of the transaction log.

4.8 Copy the backup files to the corresponding path of the mirror server SQL02.huangjh.com, the default backup path is

"C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\Backup "

4.9 On the mirror server SQL02.huangjh.com open SQL Server Management Studio, right-click Database to select "CREATE DATABASE"

4.10 Enter the database name and click "OK" (this database name must be consistent with the database name created in SQL01)

4.11 Right-click the newly created database "DB01"--Select "Tasks"-"restore"-"database"

4.12 Select "Device" and click on the right "..."

4.13 Click "Add"

4.14 Select the backup file you just copied from SQL01 and click "OK"

4.15 Click "OK"

4.16 Select the backup set to restore

4.17 on the "Options" page, tick "Overwrite existing database", restore status Select "Restore with NORECOVERY", uncheck "make tail log backup before Restore", click "OK"

4.18 Click "OK"

4.19 Database DB01 display "Restoring ..."

5. Database Mirroring Configuration

5.1 In the principal server SQL01.huangjh.com right-click "DB01" select "Task"-"Mirror"

5.2 Click "Configure Security"

5.3 Next

5.4 Select "Yes" and next

5.5 Tick "Witness server instance", next

5.6 Next

5.7 Click "Browse More"

5.8 Enter the mirror server name SQL02, click "Connect"

5.9 Select the mirror server instance SQL02, next

5.10 Also select the witness server instance SQL03, next

5.11 Enter the instance service account Huangjh\administrator, next

5.12 Click "Done"

5.13 After successful configuration, click "Close"

5.14 Click "Start Mirroring"

5.15 The status is synchronized: The database is fully synchronized, click OK

5.16 The database state that you see in the principal server SQL01 is "principal, synchronizing"

5.17 The database status that you see in the mirror server SQL02 is "restoring ..."

6. Functional verification

6.1 In the principal server SQL01 right-click Database "DB01" select "Tasks"-"Start Database Mirroring Monitor"

6.2 The synchronization status of database mirroring can be seen on this page

6.3 In the principal server SQL01 right-click Database "DB01" select "Properties"

6.4 On the Select page "mirroring" click "Failover"

6.5 Click "Yes"

6.6 When the principal server is SQL02, the mirror server SQL01, indicating that the manual failover succeeded

6.7 Shut down the SQL02 (the principal server at this time) and automatically failover the principal to SQL01

6.8 At this point back to the original principal server SQL01, mirror server SQL02, the status of "no connection: Cannot connect to the mirror server instance or witness server instance"

6.9 After the SQL02 power on, the status is "synchronized: the database is fully synchronized", indicating that the automatic failover was successful.

~ ~ ~ The above is the entire SQL Server 2012 database Mirroring configuration Sharing ~ ~ ~

Transferred from: http://blog.51cto.com/stephen1991/1706657

SQL Server 2012 Database Mirroring configuration Full article