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.
- An idea of how oftens it was
- 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.
Sure enough, we haven't seen this issue since.