This article describes the basic configuration and installation of Mirror for SQL Server 2008.
Preparations
1. The Principal, Mirror, and Witness instances are installed in the same version. Currently, they are in SQL Server 2008 SP1.
2. you need to add a domain account to the Administrators group and SQL Server-related groups of the servers where the three database instances are located. The specific group functions are unclear, therefore, we recommend that you add this domain account to all groups to ensure that the permission runs properly.
3. Set the domain account in step 2 to the SQL Server service, the SQL Server Agent Startup account, and restart the service.
4. Ensure that the data and logs on the Principal and Miroor instances are the same. The backup mode is full backup, and the recovery mode is full. You must select the NoRecovery mode when restoring the database to the Mirror instance.
Create an EndPoint
1. Create an EndPoint on the Principal and Mirror instances and run the script:
- Create EndPoint On Principal and Mirror Instance
- Create endpoint paiing_ep
- STATE = STARTED
- As tcp (LISTENER_PORT = 5022)
- FOR database_grouping ing
- (ROLE = PARTNER, ENCRYPTION = SUPPORTED)
- -- Or create endpoint cmding_ep
- STATE = STARTED
- As tcp (LISTENER_PORT = 5022)
- FOR database_grouping ing
- (AUTHENTICATION = windows ntlm, ROLE = PARTNER)
2. Create an EndPoint on the Witness instance and run the script:
- Create EndPoint On Witness Instance
- Create endpoint paiing_ep
- STATE = STARTED
- As tcp (LISTENER_PORT = 5022)
- FOR database_grouping ing
- (ENCRYPTION = SUPPORTED, ROLE = WITNESS );
- -- Or
- Create endpoint paiing_ep
- STATE = STARTED
- As tcp (LISTENER_PORT = 5022)
- FOR database_grouping ing
- (AUTHENTICATION = windows ntlm, ROLE = WITNESS)
View the EndPoint and delete the EndPoint script:
- Select * From Sys.database_mirroring_endpoints
- Select * From sys.database_mirroring
- Drop Endpoint endpoint_mirroring
Create Mirror Session
1. Create a Session on the Mirror instance and run the script:
- ALTER DATABASE DataBaseName
- SET PARTNER = 'TCP://PrincipalServerName:5022';
2. Create a Session on the Principal instance and run the script:
- ALTER DATABASE DataBaseName
- SET PARTNER = 'TCP://MirrorServerName:5022';
3. Specify Witness on the Principal instance and run the script:
- ALTER DATABASE HB2008_Gome
- SET WITNESS = 'TCP://WitnessServerName:5022';
Note:
1. This error may occur when you create a Session on the Principal instance. The error message is as follows:
The transaction log data contained in the Image Database "dbName" is insufficient, and the log backup chain of the main database cannot be retained. This situation may occur if log backup is not performed from the primary database or the backup is not restored from the backup database.
The solution is to back up the log on the Principal instance and restore it to the Mirror instance in NoRecovery mode.
The reason is that logs are interrupted and logs may be generated during full backup.
2. Test FailOver
- ALTER DATABASE DataBase
- SET PARTNER FAILOVER
3. Related query tables:
- Sys.database_mirroring
- Sys.database_mirroring_endpoints
- Sys.database_mirroring_witnesses
4. scheduled task backup in advance:
You can generate scripts for scheduled tasks on Principal in advance and execute the scripts on the Mirror instance after the Mirror configuration is complete.
5. Image Running Mode
High Security Mode: supports synchronous operations. When a transaction is committed on both the subject and the image, the transaction latency is prolonged.
High-performance mode: supports asynchronous operation, and the backup storage may lag behind the master database.
It is worth noting that, in the high security mode, because the subject and the image are synchronous operations, the image is not submitted, causing the subject to not submit, which may have a great impact.
For example, DBServerA and DBServerB databases are mutually mirrored, DBServerA is the subject, and DBServerB is the image;
At the same time, DBServerA is the Publishing Server, and DBServerC subscribes from DBServerA. The application system queries on DBServerC.
If DBServerB fails, DBServerA cannot be submitted. Therefore, DBServerC cannot query data in a timely manner.