Comprehensive data protection for all workloads
Post Reply
Techserv
Novice
Posts: 3
Liked: never
Joined: Feb 11, 2010 7:09 pm
Full Name: Technology Services
Contact:

Backing up SQL2005 in mirrored mode

Post by Techserv » Feb 24, 2010 4:55 pm

Good morning

I have two sites that our department runs from. We have a SQL server at each site running 2005. We are doing Synchronous mirroring between the two DBs. For MS SQL 2005 mirroring you need two DB servers and a witness that coordinates who is primary and who is secondary. Our secondary DB server runs 2 ancillary databases in primary mode for functions at that site.

When I try to run the backup of the secondary DB server I am using the Veeam VSS writer I can get a backup of it. However, when I run the backup the 2 databases that are in primary mode on the secondary DB server fail over to the primary, as if it loses its network connection to either the witness or the primary db server.

Does anyone here have any experience backing up mirrored SQL2005 servers, is this supported?

Thanks

donikatz
Expert
Posts: 124
Liked: never
Joined: Jan 01, 2006 1:01 am
Contact:

Re: Backing up SQL2005 in mirrored mode

Post by donikatz » Feb 24, 2010 8:41 pm

Do you notice any connectivity loss to those 2 DBs during the snapshot removal process? DBs with heavy I/O can suffer [short] connectivity loss during snapshot removal (esp when encountering suboptimal disk I/O) which could trigger failover. We have a similar issue with a couple of our heaviest Oracle DBs. In one case, isolating the DB to less contentious disks solved the problem.

Vitaliy S.
Product Manager
Posts: 22517
Liked: 1469 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Backing up SQL2005 in mirrored mode

Post by Vitaliy S. » Feb 24, 2010 8:53 pm

Hello,

I believe this is an expected behavior for your setup. As far as I know when a database mirroring session is synchronized, it provides a hot standby server that supports rapid failover with no loss of data from committed transactions.

But what happens when you do the backup/replica job of your SQL server either using VSS or VMware Tools Quiescence? Your SQL server gets freezed and stops processing any queries coming from an outside, they simply time out. The same might happen while snapshot is being removed as donikatz has correctly pointed out.

And when you backup your primary server it also gets freezed, that's why either secondary server loses the connection to it or witness server reports that primary is probably down, however it is not, it is just freezed for some reason. I believe you will see the same situation when a production SQL server fails, with client applications quickly recovery by reconnecting to the standby server, which becomes a primary one.
I guess the secondary server will always take the place of the primary one, if it is no longer accessible.

I'm not sure whether it will be possible or not, but you could try to adjust the timeout values to make it less sensitive.

Hope it makes sense.

Thank you!

Post Reply

Who is online

Users browsing this forum: No registered users and 15 guests