Sql-server – Is SQL Server Mirroring dependent on SQL Server Agent running

mirroringsql-server-2008-r2sql-server-agent

I have SQL Server mirroring set up between two 2008R2 SQL Server instances. I have multiple databases mirrored. Prior to planned failover to the backup server I want to turn off the SQL Server Agent on the primary server so I don't have jobs running and failing while the DB is running from the failover server. (I'm trying to patch my primary database server.)

However I'm not sure how dependent mirroring is on the SQL Server Agent. Will mirroring continue to function with SQL Server Agent off?

I've seen articles that say mirroring is dependent on SQL Server Agent but I'm unsure if that is just for some sort of routine cleanup or if that is a requirement for mirroring to function. I'm assuming just cleanup but I'd like to know more and I'm struggling to find answers to this exact question.

Thanks for any help you can give me (programmer wearing a newb-dba hat).

Best Answer

Mirroring is not dependent on SQL Server Agent running. Communication between primary and secondary happens using TCPIP ports. In database mirroring, principal and mirror server communicate through TCP connection and write data change directly to remote transaction log file.

But if you want to monitor and get alerted you need the agent job running which get created automatically when you set up mirroring. Details below:

Monitoring Database Mirroring (SQL Server)

Related Solutions

Sql-server – In SQL Server 2008R2 Will currently deployed applications still work if I turn on Mirroring without changing connection strings

Yes, it'll be just fine and you'll fail over just fine, provided that the server that was the single server is online. If that server is totally down then without the connection string change the apps won't know how to find the other instance.

In short yes it'll work fine until you can get the connection strings updated.

Sql-server – Manually moving SQL instance to another node causes immediate database mirror failover

One way to prevent the mirror failover is:

Pause Mirroring with ALTER DATABASE XYZ SET PARTNER SUSPEND
Move the SQL instance
Resume mirroring with ALTER DATABASE XYZ SET PARTNER RESUME

The instance is failing over to the mirror because both the witness and the secondary can no longer see the primary instance.

It sounds like you are attempting to recreate SQL Server 2012 Availability Groups by combining mirroring and clustering.

Database mirroring only times-out on so-called "soft" errors. Hard errors, like a cluster failing over are reported to the mirroring session immediately causing the immediate failover. Read more at http://msdn.microsoft.com/en-us/library/ms190913.aspx

Possible causes of hard errors include (but are not limited to) the following conditions:

A broken connection or wire

A bad network card

A router change

Changes in the firewall

Endpoint reconfiguration

Loss of the drive where the transaction log resides

Operating system or process failure

Conditions that might cause mirroring time-outs include (but are not limited to) the following:

Network errors such as TCP link time-outs, dropped or corrupted packets, 
    or packets that are in an incorrect order.

A hanging operating system, server, or database state.

A Windows server timing out.

Insufficient computing resources, such as a CPU or disk overload, the transaction 
    log filling up, or the system is running out of memory or threads. In these 
    cases, you must increase the time-out period, reduce the workload, or change 
    the hardware to handle the workload.

For more information about mirroring and potential issues, you might want to see my question What can cause a mirroring session to timeout then failover? SQL Server 2005

Best Answer

Related Solutions

Sql-server – In SQL Server 2008R2 Will currently deployed applications still work if I turn on Mirroring without changing connection strings

Sql-server – Manually moving SQL instance to another node causes immediate database mirror failover

Related Question