Microsoft – SQLServer – Mirroring – “Resume Mirroring”

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

norecovery mode

d] On Partner, Restore differential backup taken on Primary — keep restored DB in

norecovery mode

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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s