-1

I have a problem in the SQL Server Mirroring. Current system:
Server: S1 (Principal) & S2 (Secondary)
Databases: D1 (2.5TB) & D2 (20GB)

Situation 1: without witness
1. D1 & D2 were setup mirroring ok, after a week, the transactions are coming from users, I try to restart SQL Server on S1.
2. After S1 restart, mirroring of D2 is still ok, but D1 is not.
Status D1 on S1: Synchronizing, Principal
Status D1 on S2: Nothing, look like database in recovery mode, but cannot use anything, i try to restore to recovery again, or restore transaction to Non recovery but fail all.

Situation 2: with witness
I test failover and switch between 2 servers, then i got:

D2: failover OK, mirroring work OK.

D1: Get the same error situation 1 Status D1 on S1: Mirror, Recovery
Status D1 on S2: Nothing.

I detect system and got something:
- S1, S2 using two difference SAN >> I try change S2 use same SAN with S1, but i also get same error.
- D1 has 2 logical file in defunct status, cannot delete or using, and not have physical files.

I can guess exactly of the problem and how to fix it. Please help me, thank you.

0

Situation 1

So when you restart SQL server while users are connectd & D1 database is 2.5TB so it take's more time to failover then D2 database.Also I assume there will be more activity on D1 then D2 database.

As you did unplanned restart of SQL server on bussy database (D1) so mirroring get distructed may be packet loss to mirror server or fail to match the LSN between principal & secondary database so mirroring break.

In future you like to do the restart of server take a down time from users is best & safe way to do. Also keep am eye on Database Mirroring Monitor to see unsend transaction to mirror server.

Situation 2

As you said 2 logical file in defunct status thats SAN issue i will suggest involve SAN team & ask for new storage you dont want to take a risk as you data is 2.5TB database in time of disaster its even take to restor form backup.

Below is just the high level of explanation of how to rebuild Defunct drive.

Drive Replacement (Rebuilding a Defunct Drive) When a hard disk drive goes defunct (DDD), a Rebuild operation is required to reconstruct the data for the device in its respective disk array. The ServeRAID adapters and controllers can reconstruct RAID level-1 and RAID level-5 logical drives, but they cannot reconstruct data stored in RAID level-0 logical drives.

To prevent data integrity problems, the ServeRAID adapters and controllers set the RAID level-0 logical drives to Blocked during a Rebuild operation. After the Rebuild operation completes, you can unblock the RAID level-0 logical drives, and access them once again. But remember, the logical drive might contain damaged data.

For both senarion I will suggest once you once your SAN issue get resslolved, Reconfigure Mirroring from scratch.

Below is high level steps as you have already mirroring confugured so i have added few steps to remove that:-

1.Connect to either partner. 2.Issue the following Transact-SQL statement: ALTER DATABASE SET PARTNER OFF where is the mirrored database whose session you want to remove. This statement removes the database mirroring session and removes mirroring from the database. You can specify OFF on either partner. 3.--Drop Endpoint on Mirror if it exists IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Mirroring') DROP ENDPOINT [Mirroring] GO 4.Delete mirror database. 5.Reconfigure database mirroring as you did previously.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.