High Availability of SQL server SQL Mirror HA

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.