locked
SQL Mirroring Suspended RRS feed

  • Question

  • I have 2 SQL servers that are mirrored but unfortunately the mirroring was suspeneded over a week ago (unknown reason) trouble is I can't resume the mirror.

    when I do resume the mirror it trys to synchronize but fails and suspends itself after around 30 seconds always with the same size of unrestored log - does this mean there is a bad part of the log?

    screenshot here.
    https://picasaweb.google.com/lh/photo/wM5d4WqFI2X3bZHWzFcyraRLFtpGBL1LkGBqxZcgAUE?feat=directlink

    I don't want to break the mirror as I have a logshipping set up to another server which I can't break and believe this will happen if I take a full backup of the live database and restore it to the mirror....... am I in trouble?

    any help would be greatly appreciated.
    Thanks
    Friday, February 4, 2011 12:56 PM

Answers

  • First of all, you need to figure out why mirroring keeps getting suspended. Check the SQL Log on both servers to see if there are any messages stating why. Until this is fixed, mirroring can't be resumed.

    Secondly, if you need to re-setup mirroring, it won't affect log shipping. You should already be taking full backups on a regular basis. Taking another one won't affect log shipping. And you can restore the log backups from log shipping onto the mirror database. In fact, if I had to resetup mirroring, I would use a differential backup rather than a full. This is how I would do it:

    1. Drop mirroring - don't recover the mirror database
    2. Disable the log shipping backup job
    3. Create a differential backup
    4. Restore the differential backup on the mirror using NoRecovery
    5. Manually run the log shipping backup job one time
    6. Restore the log backup on the mirror using NoRecovery
    7. Configure database mirroring
    8. Enable the log shipping backup job


    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
    Friday, February 4, 2011 5:36 PM

All replies

  • First of all, you need to figure out why mirroring keeps getting suspended. Check the SQL Log on both servers to see if there are any messages stating why. Until this is fixed, mirroring can't be resumed.

    Secondly, if you need to re-setup mirroring, it won't affect log shipping. You should already be taking full backups on a regular basis. Taking another one won't affect log shipping. And you can restore the log backups from log shipping onto the mirror database. In fact, if I had to resetup mirroring, I would use a differential backup rather than a full. This is how I would do it:

    1. Drop mirroring - don't recover the mirror database
    2. Disable the log shipping backup job
    3. Create a differential backup
    4. Restore the differential backup on the mirror using NoRecovery
    5. Manually run the log shipping backup job one time
    6. Restore the log backup on the mirror using NoRecovery
    7. Configure database mirroring
    8. Enable the log shipping backup job


    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
    Friday, February 4, 2011 5:36 PM
  • You have to figure out why the mirror keeps getting suspended otherwise all other efforts will be futile. is there any information in the error log? Can you telnet the endpoint ports from the opposite servers?
    Monday, February 7, 2011 9:25 AM
  • have u changed the ports or do some restoration on database?
    "SQLSERVER DBA" "INDIA"
    Tuesday, February 8, 2011 12:03 PM