SQL mirror ha (High Availability of SQL Server) [reprint]

Perform basic configuration and installation of mirror on 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 paiing_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_mongoing_endpoints

Select * From SYS. database_sorting ing

Drop endpoint endpoint_grouping ing

 

Create mirror session

1. Create a session on the mirror instance and run the script:

Alter database databasename

Set partner = 'tcp: // principalservername: 8080 ';

2. Create a session on the principal instance and run the script:

Alter database databasename

Set partner = 'tcp: // your servername: 808080 ';

3. Specify witness on the principal instance and run the script:

Alter database hb2008_gome

Set witness = 'tcp: // witnessservername: 8080 ';

Note: 1. this error may occur when you create a session on a 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 databaseset partner failover3. query table: SYS. database_processing ing sys. database_mirroring_endpoints sys. database_paiing_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

1. High Security Mode: supports synchronous operations. When a transaction is committed on both the subject and the image, the transaction latency is prolonged.

2. 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.