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