SQL Server R2 Database Mirroring Deployment Graphics Tutorial

Database mirroring is a software-based solution for database high availability. It maintains two identical copies of a database, each of which is placed in a different instance of the SQL Server database

Overview

Database mirroring is a software-based solution for database high availability. It maintains two identical copies of a database, each of which is placed in a different instance of the SQL Server database. It is recommended to use two servers in different locations for hosting. At the same time, one of the databases is used for client access, acting as the principal server role, while the other is acting as a hot standby server, the mirror server role, based on the configuration and status of the mirroring session, which are not absolute.

Advantages

• Enhanced data protection capabilities

Improved availability of the database

L Increased availability of production databases during upgrade

Working style

In a database mirroring session, the principal and mirror servers communicate and collaborate with each other and complement each other. The database copy on the principal server role is a production database. Database mirroring will be re-executed in the mirrored database as soon as possible for each operation performed in the principal database, such as INSERT, UPDATE, and delete. This process is done by sending a stream of active transaction log records to the mirror server, which can be applied sequentially to the mirror database as soon as possible. and database mirroring is performing this redo operation at the physical logging level. SQL Server R2 (hereinafter referred to as: SQL08R2), in order to reduce the load on the network, the principal server compresses transaction log records and sends them.

Operating mode

L High Performance mode (asynchronous run): Transactions do not need to wait for the mirror server to write logs to disk for submission, which maximizes performance. This means that the transaction does not need to wait for the mirror server to write the log to disk for submission, and this operation allows the principal server to run under the conditions with the least transaction latency, but some data may be lost.

L High Security Mode (synchronous operation): When the session starts, the mirror server synchronizes the mirrored database with the principal database as soon as possible. Once the database is synchronized, the transaction is committed on both sides, which increases transaction latency.

Figure 1

L High-safety mode with failover (witness server): The greatest advantage of this mode is that when the principal server is disconnected, the database copy on the mirror server is automatically enabled and served as a production database for the client. In this architecture, the witness server is not available for the database, and its primary role is to support automatic failover by verifying that the principal server is enabled and running.

NOTE: The mirror server initiates an automatic failover only when the mirror server remains connected to the witness server after disconnecting from the principal server.

The purpose of the witness server is to verify that the specified partner server is up and running correctly. If the mirror server is disconnected from the principal server, but the witness remains connected to the principal server, the mirror server cannot start the failover. Therefore, the condition of failover is that the principal server is disconnected from the mirror server, and the witness server is disconnected, and the mirror server is connected with the witness server.

Figure 2

Role switching

Automatic switchover: In the case of a witness server, the database must already be synchronized and the witness must be connected to the mirror server properly.

Manual switchover: In high-safety mode, the principal and mirror servers must remain interconnected and the database must be synchronized.

Forced service: In high-performance mode and in highly secure mode without automatic failover, you can force the service to run if the principal server fails and the mirror server is available. This approach can cause some databases to be lost.

Realize

First, conditions

Database mirroring for SQL08R2 must be based on each database that uses the full recovery model. Database mirroring for SQL08R2 does not support the simple recovery model and the bulk-logged recovery model. In addition, system databases such as "Master", "msdb", "model", and "tempdb" cannot be mirrored.

Second, the environment

The test environment is within a LAN, the IP address is 192.168.0.0/24 segment, and the domain name is punwar.cn.

dc:192.168.0.110/24--domain controllers and DNS servers;

SQL-1:192.168.0.111/24--SQL08R2 principal server;

SQL-1:192.168.0.112/24--SQL08R2 mirror server;

SQL-1:192.168.0.113/24--SQL08R2 witness server.

The topology (3) shows:

Figure 3

III. preparation of the basic environment

Because the system platform uses WIN08R2 and firewall settings are enabled, it is necessary to open the corresponding port on the firewall for SQL08R2 to work properly. The ports that are described in this article that require database mirroring to be open are TCP-1433 and TCP-5022 ports. There are many open methods, but here you can combine domain-based Group Policy for the overall configuration of three SQL08R2 servers at the same time.

Place three servers in the same OU (4)

Figure 4

With the Group Policy manager for DCs, create a separate GPO for its OU and locate its computer configuration-Administrative Templates-Network/network connection/windows Firewall/Domain profile, enable define inbound port exceptions (5), and define two respectively:

1433:tcp:192.168.0.0/24:enabled:sql Server

5022:tcp:192.168.0.0/24:enabled:sql Server DBM

Figure 5

In addition, for SQL08R2, TCP/IP access is disabled by default for SQL services, so it needs to be enabled by SQL Server Configuration Manager (6).

Figure 6

Iv. Preparation of the database

On the SQL-1 server (7) that is logged on as the principal server, locate the database that needs to be mirrored (this is the MyDB database in this article), and right-click to select its properties (8).

Figure 7

Figure 8

In its Properties window, select the Options page to confirm that its recovery mode is full (9).

Figure 9

Close the window, and then make a full backup of the data and a transaction log backup. Select the MyDB database node and right-click Select Task-Backup (10).

Select the backup type "full" in the Backup window and specify the path to the target backup package (11), and click "OK" to make a backup.

The same steps, open the Backup window, select the backup type is "full", and specify the path of the target backup package (12), click "OK" to make a backup. Here, for convenience, you can choose the same backup package as the full backup.

Figure 12

Copy the backup package to the mirror server SQL-2 (13) and log in to SQL-2 (14) from the SQL Management console.

Figure 13

Figure 14

Create a database--mydb (15) with the same name as the database that needs to be mirrored on the principal server. At the same time, the database files should be placed in the same location, and the properties should be configured identically.

Figure 15

The new database is restored on SQL-2, and the backup of the database from the SQL-1 is restored for the newly created mydb on SQL-2. Right-click the MyDB Database Selection task--Restore--database (16) to open the Restore Database window.

Figure 16

Select "Original Device" and browse to add a backup package (17) copied from SQL-1.

Figure 17

Select "Options" in the page, tick "Overwrite existing database" and select "Do not perform any operations on the database, do not roll back uncommitted transactions." You can restore other transaction logs. (RESTORE with NORECOVERY) "(18).

Figure 18

Click "OK" to complete the database restore operation (19)

Figure 19

V. Configuring the principal/mirror server

On SQL-1, right-click MyDB, select Task-Mirror (20), open the Mirror Database Configuration window, click the "Configure Security" button (21).

Figure 20

Figure 21

Select Yes (22) on the include witness page so that automatic failover can be implemented later. Click Next.

Figure 22

On the Select server to configure page, you can uncheck the witness server instance (23), because the witness can be configured later.

Figure 23

Click Next to specify the principal server listening port and endpoint name (24), the default port is TCP-5022 port, and the endpoint name is customized.

Figure 24

Click Next, specify the mirror server, select "Browse More" (25) in the drop-down menu, and specify Log on to SQL-2 (26) in the Connect to Server dialog box.

Figure 25

Figure 26

You still need to specify the listener port and endpoint name for the mirror server, where the port still uses the default TCP-5022 (27).

Figure 27

Click Next to specify the service account for the server instance, which uses the Administrator account uniformly in this test environment, but it is recommended that an account be created specifically for it in the production environment (28).

Figure 28

Click Next, display the summary, confirm the error, and click "Finish" (29) to configure the endpoint.

Figure 29

When the configuration is completed successfully, you are prompted to start mirroring immediately (30) and click Start Mirroring.

Figure 30

When the database synchronization is complete, the mirroring status is displayed as synchronized: The database is fully synchronized (31). As a result, the deployment of the principal and mirror servers is complete.

Figure 31

Note: In the last step, when mirroring is synchronized, you need to enable "Remote DAC" on each SQL08R2 server, or you may receive an error message (32)

Figure 32

The methods for enabling remote administrator connections in SQL08R2 are as follows:

Right-click on the server node and select "Facet" (33)

Figure 33

Open the View Facet window, select surface area configurator (34) in the Facet drop-down menu, and specify the remotedacenabled value for facet properties as True (35).

Figure 34

Figure 35

VI. Implementation of manual failover

First, make sure that the current principal and mirror servers are working properly and that the connection is normal. Currently on SQL-1, the status of MyDB is "subject, synchronized" (36)

Figure 36

On SQL-1, open the MyDB "mirroring" configuration window, confirm that the operating mode is "High security" mode, and then click the "Fail Over" button (37). If you are prompted to transfer (38), click Yes to transfer.

Figure 37

Figure 38

After the operation has completed successfully, the status of the database MyDB on SQL-1 becomes "mirrored, synchronized/restoring ..." (39);

Figure 39

On SQL-2, the state of MyDB becomes "subject, synchronized" (40). This shows that the result of failover is to swap the principal/mirror roles.

Figure 40

Vii. Implement automatic failover

Automatic failover requires the configuration of the witness, which is now required to reconfigure the witness manually, since the configuration of the witness was skipped during the implementation of the principal/mirror server.

On the principal server SQL-1, right-click on the database mydb, select the task-mirror (41), open the "Mirroring" Configuration window, select "Configure Security" button (42)

Figure 41

Figure 42

To configure security to include a witness server instance (43)

Figure 43

Click Next and tick "witness server Instance" (44).

Figure 44

Click Next to confirm the principal server configuration (45),

Click Next, configure the witness, and select "Browse More" (46) from the drop-down menu. Connect and sign in to SQL-3 (47)

Figure 46

Figure 47

Specify "Listener port" as the default TCP-502 and endpoint name (48)

Figure 48

Click Next to specify the domain administrator as the principal/mirror/Witness Service account (49)

Figure 49

Click Next to confirm that the summary information is correct and click "Finish" (50).

Figure 50

When the configuration is completed successfully, the witness information is displayed in the Mirroring Configuration window and the High security (synchronous) with automatic failover feature is automatically selected (51).

Figure 51

Note: "High security without automatic failover (sync)" will not be available at this time

Disconnect the SQL-1 network connection, simulating the principal server, which is the production database server failure. such as: Unplug the SQL-1 network cable. The MyDB status on SQL-1 automatically changes to "principal, disconnected/recovering" (52)

Figure 52

At this point, viewing the MyDB status on SQL-2 will automatically change to "principal, disconnected" (53).

Figure 53

Eight, verify the data synchronization of the mirror database.

In the case where the roles are working correctly, locate the MyDB database on the principal server SQL-1 and create the table T1. and insert the data in it (54).

Figure 54

After you have finished building the table and inserting the data, unplug the SQL-1 network cable and simulate the failure. Then, log in to the mirror server SQL-2, navigate to the MyDB database, expand to see the mirror Synchronized T1 table, open the table can see the data in the table is also synchronized (55).

Figure 55

It can be seen that the "Database mirroring" technology can be successfully hot-prepared data, so that the availability of sql08r2 greatly improved. Also, it is important to note that server access targeting after a failure needs to be designed at the SQL statement level and will no longer be described here.

Reference article:

Microsoft technology Resource Library SQL Server R2 database mirroring http://technet.microsoft.com/zh-cn/library/bb934127.aspx

SQL Server R2 Database Mirroring Deployment Graphics Tutorial