Two test "go" to simulate mirrored server disk issues

Database mirroring is currently available in two configurations: high-safety mode and high-performance mode.

We know that in high-safety mode, transactions committed on the primary server must be committed on the mirror server at the same time, otherwise the transaction cannot be committed on the primary database.

 

In the diagram above, the steps that a transaction submits on the primary database include:

1. Client programs send transactions to the primary database server
2. Primary database server SQL server writes log files for this transaction

2.1 The primary database server passes the log content of this transaction to the mirror server's SQL Server

1. The mirrored database server SQL Server writes the received log content to the log file.
2. The mirror server replies to the master server's write log action completion.
3. The primary server SQL Server reply client program successfully submitted the transaction.

>2. The primary database server SQL Server writes the data page modified by the transaction to the data file from memory at the checkpoint.

>3. Mirror database server SQL Server makes changes to the data page of the log for the file.

from this order, we present a question if the mirror server's SQL Server Unable to complete the steps at that time 3 , is the primary database server unable to commit the transaction properly for the database? The two tests described in this article are test results when the simulation log files and the database are unavailable

Test One: The database has multiple data files and log files, distributed on different disks, delete a disk when the mirror database is online, that is, a data file and log file is not possible test :

1. 1. Create a database testand add four files. Note Set a smaller size for the first log file and disable autogrow :

Test C:\Program Files\Microsoft SQL Server\mssql.2\mssql\data\test.mdf

Test_log C:\Program Files\Microsoft SQL Server\mssql.2\mssql\data\test_log.ldf

Test1 E:\TEST1.NDF

Testlog1 E:\testlog1.ldf

1. 2. Configure database Mirroring High Security mode, the primary server is SQL1, and the mirror database is SQL2.

1. 3. Create two test tables tests and test1, insert more than 1,000,000 row data to Test in the table  . because the first log file is small and does not grow automatically, SQL Server starts using the second log file , testlog1. Check the status of database mirroring:

Result: The primary database displays principal/sychronized

1. 4. Execute the following script to start the transaction, which will execute for more than ten minutes:

BEGIN Tran

INSERT INTO Test select * from Test

INSERT INTO Test select * from Test

Commit

1. when a transaction starts executing 1 minutes after database mirroring is paused 1 minutes, and then manually recover the synchronization of the database mirror :

Result: The primary database status displays principal/synchronizing.

You can return data by accessing the Test1 table.

6. manually unplug the E - disk from the mirror database service:.

Result: The primary database state becomes immediately more (Principal/suspend).

Test access to the primary database and query the Test1 table

Mirror database state changed to: (Mirrored, suspend/restoring)

7. Check the execution of the transaction :

After a few minutes, the transaction appears to be committed successfully on the primary database.

.

8. Check the SQL Server error log for the primary and mirrored databases :

Primary database:

error:1453, Severity:16, State:1.

' Tcp://ldua2481460-2. dom248146.com:5023 ', the remote mirroring partner for database ' test ', encountered error 5159, status 1, severity 24.  Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server Instan Ce.

Mirror database:

' Tcp://ldua2481460-2. dom248146.com:5023 ', the remote mirroring partner for database ' test ', encountered error 5159, status 1, severity 24.  Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server Instan Ce.

Operating System error 2 (the system cannot find the file specified.) on file "E:\test_3.ldf" during RESTOREFILEHDR.

Test 2: Delete the mirrored database when executing and synchronizing a large transaction :

1. Create a database testthat contains a log file for a data file.

Test1 E:\test1.mdf

Test1_log E:\test1_log.ldf

2. Configure database Mirroring High Security mode, the primary server is SQL1, and the mirror database is SQL2.

3. Create two test tables tests and test1, insert more than 1,000,000   The row data into the test table . because the first log file is small and does not grow automatically, SQL Server starts using the second log file , testlog1. Check the status of database mirroring:

Result: The primary database displays principal/sychronized

4. Execute the following script to start the transaction, which will execute for more than ten minutes :

BEGIN Tran

INSERT INTO Test select * from Test

INSERT INTO Test select * from Test

Commit

5. After the transaction has started executing for 1 minutes, pause the database mirroring for 1 minutes, and then manually restore the database mirroring synchronization :

Result: The primary database status displays principal/synchronizing.

You can return data by accessing the Test1 table.

6. manually unplug the E - drive from the mirror database service:

Result: The primary database state becomes immediately more (principal/disconnected).

Test access to the primary database and query the Test1 table

The mirror database does not exist.

7. Check the execution of the transaction :

After a few minutes, the transaction appears to be committed successfully on the primary database.

8. Check the SQL Server error log for the primary and mirrored databases :

Primary database:

1453, Severity:16, state:1

' Tcp://ldua2481460-2. dom248146.com:5023 ', the remote mirroring partner for database ' Test1 ', encountered error 5149, status 1, severity 16.  Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server Instan Ce..

Mirror database:

Error:17053<c/> severity:16<c/> state:1

Restorefilehdr:operating System error 2 (the system cannot find the file specified.) encountered.

Error:5159<c/> severity:24<c/> state:1

Operating System error 2 (the system cannot find the file specified.) on file "E:\test1_1.ldf" during RESTOREFILEHDR.

Error:823<c/> severity:24<c/> State:3.

The operating system returned error (the device is isn't ready.) to SQL Server during a write at offset 0x00000000012000 I n file ' E:\test1.mdf '. Additional messages in the SQL Server error log and system event log could provide more detail. This is a severe system-level error condition the threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can is caused by many factors; For more information<c/> see SQL Server Books Online.

Error:1454<c/> severity:16<c/> state:1.

While acting as a mirroring partner for database ' test1 ' <c/> server instance ' Ldua2481460-1\mssqlserver1 ' Encountere D Error 823<c/> status 3<c/> severity 24.  Database mirroring would be suspended. Try to resolve the error and resume mirroring.

Test Results :

The primary database can still be accessed in both cases and the transaction is committed successfully

Transferred from: http://blogs.msdn.com/b/apgcdsd/archive/2012/03/09/10280123.aspx

Two test "go" to simulate mirrored server disk issues