How SQL Server creates mirrored graphics tutorials

Because the work needs to do a mirrored offsite backup of SQL, has not been studied before, Baidu has an article record, convenient later query

Reprint Address: http://jingyan.baidu.com/article/d5c4b52b20843fda560dc581.html

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.

How SQL Server creates mirrored graphics tutorials (GO)