I wrote a piece last week which described out mirroring could be disabled automatically on failure (http://www.electricwaffle.co.uk/2013/04/disabling-mirroring-automatically/). Part of the inspiration for this was to allow replication to continue in an environment where replication and mirroring were  implemented on the same 2005 instance. By default, when mirroring breaks, any unmirrored changes will not be grabbed by the Log Agent. Therefore a mirror failure stops replication :-(.

However  <<drum roll>> I found a trace flag which when enabled (with hotfixes) allow unmirrored changed to be grabbed for use in replication by the Log Agent. 1448

See the following KB for more information http://support.microsoft.com/kb/937041

Glorious 😀

 

***HELLO! IF YOUR LOOKING AT THIS TO FIND A WAY OF KEEPING REPLICATION GOING WHEN MIRRORING FAILS IN 2005 INSTANCE SEE http://www.electricwaffle.co.uk/2013/04/trace-flag-to-allow-log-agent-to-read-unmirrored-changes-in-ms-sql-2005/ ***

Mirroring and replication on the same instance can be an issue when mirroring fails. Lets say you notice your replication stops and you find in the Replication Monitor the error “Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.”  If you’ve got mirroring in place check the status of the mirroring this might be the issue. You can use the T-SQL below to check the status

select db_name(database_id ), mirroring_state_desc
from sys .database_mirroring
where mirroring_guid is not null and mirroring_state_desc = 'SUSPENDED'

Drop the mirroring for any mirroring which are in a suspended state using

alter database <<database>> set partner off

Check replication and you’ll find it’ll most likely start up again.

This is all very well and good but if mirroring pokes at 2am and its an system used across many regions and people can’t use replication there’s an issue!

What can be useful is to have something in place to drop the mirror in the event that it suspends to save the replication. The script below could be added to a job running periodically to test the mirroring status and take action.

declare @commands nvarchar (4000)
declare @database_names varchar (1000)

--building command for turning mirroring off
select @commands = coalesce (@commands + '', '')+'ALTER DATABASE ' + db_name(database_id ) + ' SET PARTNER OFF ; '
from sys .database_mirroring
where mirroring_guid is not null and mirroring_state_desc = 'SUSPENDED'

--building list of servers which are in a suspended state
select @database_names = coalesce (@database_names + '', '')+ db_name(database_id )
from sys .database_mirroring
where mirroring_guid is not null and mirroring_state_desc = 'SUSPENDED'

--if the commands variable is not empty  . . . .
if(@commands <> '' )
begin

--send mail to inform people what is going to happen
exec msdb. dbo.sp_send_dbmail
    @profile_name = '<<NAME>>' ,
    @recipients = '<<EMAIL>>' ,
    @subject = 'MIRRORS IN SUSPENDED STATE' ,
    @query = 'select db_name(database_id) from sys.database_mirroring where mirroring_guid is not null and mirroring_state_desc = ''SUSPENDED''',
    @body = 'Databases were in a suspended state - mirroring to be removed',
    @importance = high,
    @attach_query_result_as_file = 1

       begin try

             --attempt to run the query to turn off mirroring
             exec sp_executesql @stmt = @commands

             --tell people the mirroring was turned off successfully
             exec msdb. dbo.sp_send_dbmail
             @profile_name = 'SQLServer' ,
            @recipients = '<<email>>' ,
            @subject = 'SUSPENDED MIRRORS PARTNER REMOVED SUCCESSFULLY' ,
            @importance = high,
            @body = @database_names

       end try

             begin catch

             -- tell people attempt to turn off mirroring failed
             exec msdb. dbo.sp_send_dbmail
             @profile_name = '<<NAME>>' ,
            @recipients = '<<EMAIL>>' ,
            @subject = 'SUSPENDED MIRRORS PARTNER REMOVAL FAILED' ,
            @importance = high,
            @body = @database_names

             end catch

end

Tailor the try/catch to meet your error to meet your needs :-)