SQL Server 2008 R2 Database Mirroring Deployment Tutorial _mssql2008

Overview

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

Advantages

• Enhanced data protection capabilities

• Increased availability of the database

• Increased availability of production databases during upgrades

Working mode

In database mirroring sessions, the principal and mirror servers communicate and collaborate with each other and complement each other. The copy of the database on the principal server role is the production database. Database mirroring performs the execution of each operation in the principal database as soon as possible, such as inserts, updates, and deletes, in the mirrored database. This process is accomplished by sending the 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 performs this "redo" operation at the physical logging level. SQL Server 2008 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.

Run mode

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

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

Figure 1

L High-security mode with failover (witness): The greatest advantage of this pattern is that when the principal server is disconnected, the copy of the database on the mirror server is automatically enabled and serves the client as a production database. In this structure, the witness server is not available to 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 automatic failover only after disconnecting from the principal server and the mirror server remains interconnected with the witness.

The role of the witness is to verify that the specified partner server is started and running correctly. If the mirror server is disconnected from the principal server, but the witness server remains connected to the principal server, the mirror server cannot initiate failover. So the condition for failover is that the principal server is disconnected from the mirror server, and the witness is disconnected, and the mirror server is connected to the witness server.

Figure 2

Role switching

Automatic switching: When using the witness server, the database must be synchronized and the witness must be connected to the mirror server properly.

Manual switching: In high security mode, the principal and mirror servers must remain interconnected and the database must be synchronized.

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

Realize

One, conditions

SQL08R2 database mirroring 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

Test environment for a LAN, IP address is 192.168.0.0/24 segment, 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.

Topology (as shown in Figure 3):

Figure 3

III. preparation of the basic environment

Because the system platform uses WIN08R2 and firewall settings are enabled, the appropriate ports need to be opened on the firewall for SQL08R2 to work properly. The ports you need to open in this article for database mirroring are the TCP-1433 and TCP-5022 ports. There are many ways to open it, but 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 (Figure 4)

Figure 4

Through the DC's Group Policy Manager, 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" (Figure 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 for SQL Services is disabled by default, so it needs to be enabled via SQL Server Configuration Manager (Figure 6).

Figure 6

Iv. Preparation of the database

When the login will be the principal server's SQL-1 server (Figure 7), locate the database that needs to be mirrored (the MyDB database in this article) and right-click to select its properties (Figure 8).

Figure 7

Figure 8

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

Figure 9

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

In the Backup window, select the backup type is complete and specify the path to the target backup package (Figure 11), and click OK to make a backup.

In the same step, open the Backup window, select the backup type as complete, and specify the path to the target backup package (Figure 12), and 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 (Figure 13) and log on to SQL-2 from the SQL Management Console (Figure 14).

Figure 13

Figure 14

Create a database--mydb with the same name as the database on the principal server that requires mirroring (Figure 15). At the same time, the database files should be placed in the same location and each property should be configured identically.

Figure 15

The restore operation on the newly created database on SQL-2 is about to restore from the SQL-1 backup of the database against the newly created mydb on SQL-2. Right-click the MYDB Database Select task-Restore-database (Figure 16) to open the Restore Database window.

Figure 16

Select original device and browse to add the backup package copied from SQL-1 (Figure 17).

Figure 17

Select options on the page, check overwrite existing database, and select Do not perform any operations on the database and do not rollback uncommitted transactions. You can restore additional transaction logs. (RESTORE with NORECOVERY) "(as shown in Figure 18).

Figure 18

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

Figure 19

V. Configuring the principal/mirror server

On the SQL-1, right-click MyDB, select a task-mirror (Figure 20), open the Mirrored Database Configuration window, and click the "Configure Security" button (Figure 21).

Figure 20

Figure 21

On the Include witness page, select Yes (Figure 22), which enables automatic failover later. Click Next.

Figure 22

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

Figure 23

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

Figure 24

Click Next to specify the mirror server, select Browse More in the Drop-down menu (Figure 25), and specify the login to SQL-2 (Figure 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 (Figure 27).

Figure 27

Click Next to specify the service account for the server instance and to use the Administrator account uniformly in this test environment, but it is recommended that you create an account specifically for it in the production environment (Figure 28).

Figure 28

Click Next, show the summary, confirm the error, and click "Finish" (figure 29) for the endpoint configuration.

Figure 29

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

Figure 30

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

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 (Figure 32)

Figure 32

The method for enabling remote administrator connections in SQL08R2 is as follows:

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

Figure 33

Open the View side window, select the perimeter configuration (Figure 34) in the aspects Drop-down menu, and specify "True" for the "remotedacenabled" value of the aspect properties (Figure 35).

Figure 34

Figure 35

VI. Implementation of manual failover

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

Figure 36

On SQL-1, open the MyDB Mirroring Configuration window, confirm that the run mode is "High security" mode, and then tap the "Failover" button (Figure 37). The system prompts for a transfer (figure 38) and clicks Yes to transfer.

Figure 37

Figure 38

After the operation was completed successfully, the state of the database mydb on SQL-1 changed to "mirrored, synchronized/restoring ..." (figure 39);

Figure 39

On the SQL-2, the MyDB state becomes "subject, synchronized" (see Figure 40). This shows that the result of a failover is that the principal/mirror role is interchanged.

Figure 40

VII. implementation of automatic failover

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

On the principal server SQL-1, right-click the database mydb, select Tasks-mirroring (Figure 41), open the Mirroring Configuration window, and select the Configure Security button (Figure 42)

Figure 41

Figure 42

Configure security to include a witness server instance (Figure 43)

Figure 43

Click Next to check the witness server instance (Figure 44).

Figure 44

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

Click Next to configure the witness server and choose Browse More in the Drop-down menu (Figure 46). Connect and log on to SQL-3 (Figure 47)

Figure 46

Figure 47

Specifies that the listener port is the default TCP-502 and endpoint name (Figure 48)

Figure 48

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

Figure 49

Click Next, confirm the summary information correctly, and click Finish (Figure 50).

Figure 50

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

Figure 51

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

Disconnect the SQL-1 network connection, impersonate the principal server, the production database server is faulted. such as: Unplug the SQL-1 network cable. The MyDB state on the SQL-1 automatically becomes "principal, disconnected/recovering" (Figure 52)

Figure 52

At this point, the view of the MyDB state on the SQL-2 automatically becomes "Principal, disconnected" (Figure 53).

Figure 53

Verify the data synchronization of the mirrored database.

When each role is working correctly, navigate to the MyDB database on the principal server SQL-1 and create a table T1. and insert the data in it (Figure 54).

Figure 54

After completing the table and inserting data, unplug the SQL-1 cable and simulate the failure. Then, log on to the mirror server SQL-2, navigate to the MyDB database, expand the T1 table that you can see mirroring synchronized, and open the table to see that the data in the table is synchronized (Figure 55).

Figure 55

It can be seen that the "Database mirroring" technology can successfully hot-standby data, so that the availability of sql08r2 greatly improved. In addition, it should be noted that a failed server access location needs to be designed at the level of the SQL statement, which will no longer be described here.

Reference articles:

Microsoft Technical Resource Library SQL Server 2008 R2 Database Mirroring
Http://technet.microsoft.com/zh-cn/library/bb934127.aspx

This article from the "Fat Brother Technology Hall" blog