dataplatform.blog

Database Mirroring to offsite server

Using SQL Server Database Mirroring to offsite servers, can at times be tricky, as latency becomes an issue.
At a recent engagement, they where experiencing that their mirror got suspended several times a day.

They have a SQL Server which is the Principal and a target that is offsite.

They were mostly able to resolve it, by doing a resume of said mirror.

ALTER DATABASE [DB-in-question] SET PARTNER RESUME

Trying to get somewhat of an idea as to how often this happened, I setup a small job, that simply checked if the mirror was suspended and if so, resumed.

IF((SELECT mirroring_state_desc FROM sys.database_mirroring WHERE database_id = DB_ID('DB-in-question')) IN (N'SUSPENDED')) BEGIN PRINT 'Resuming mirror' ALTER DATABASE [DB-in-question] SET PARTNER RESUME END

This gave me two things.

  1. An idea of how oftens it was
  2. The mirror got resumed

In this case, it showed me that there was no pattern in the stalls.
After walking through the situation with a colleague, I ended up raising the default timeout from 10 to 120 seconds, as it looked like it was intermittent timeouts that caused this.

ALTER DATABASE [DB-in-question] SET PARTNER TIMEOUT 120

Sure enough, we haven't seen this issue since.