Microsoft – SQLServer – Mirroring – “Resume Mirroring”
Had a bit of crash and needed to re-initiate DB Mirroring. As the partner side of a DB
Mirror topology can not be backed-up, it seemed our only hope was to terminate the DB
Mirror relationship, take fresh backups from the main DB, restore those backups on
the partner DB, and re-configure & resume the DB Mirror Session.
The steps seem long and daunting.
And, I ‘ve always wondered how many times I would have to take Transaction DB backup
on Primary Server and corresponding Restore on the Partner.
The Steps to set up DB Mirror are:
a] On Primary Server, take DB Backup (full)
b] On Primary Server, take DB Backup (transaction)
c] On Secondary DB Server, apply DB Restore ( full)
d] On Secondary DB Server, apply DB Restore (transaction)
e] Configure DB Mirror
The steps are quite-straight forward. But, when one is dealing with a relative VLDB all
bets are off.
In our case, we are processing thousands of requests and I was not sure whether we
have to place DB in “read-only” mode and ensure that we were no longer accepting \
updating data, take one last transaction \ log backup, copy the backup files over, and
restore the backup files (keeping the DB in norecovery mode).
Well, it seems we don’t have to suspend user’s interaction with primary DB (upon taking
last DB backup).
In our case, what we did was:
a] On Primary, Suspend all Database Backups (Full \ Differential \ Transaction)
b] On Primary, Take full backup
c] On Partner, Restore full backup taken on Primary — keep restored DB in
d] On Partner, Restore differential backup taken on Primary — keep restored DB in
e] Re-initiate DB Mirror Session
There are a couple of things that might have helped us:
a] Backup to multiple files
b] Find a fast file copier – One that can operate in parallel and backup multiple files
c] Ensure that the Operations that are occurring on Partner & Mirror are a bit controlled
– On Primary, no backups that are outside of the ones that will be carried over
and applied to Mirror
– On Partner, no restores that are outside of the ones that will be carried over
and applied to Mirror
– All restores on Partner should be under-taken with the norecovery option
d] Once the Full Backup is taken on Primary all subsequent backups should be
differential. This ensures that only a single Differential backup need to be taken on
Primary and restored on Partner. If you have the Storage and Network Bandwidth,
Differential backups are preferred. Each new one aggregates all previous ones and they
can be repeatedly taken