x

SQL Server 2005 mirroring - connection lost?

If a connection is lost in SQL Server 2005, how long can the connection be down before the miirror can no longer recover? is it based on transactions, amount of time? or can it always recover?

more ▼

asked Jun 01, 2010 at 02:06 PM in Default

Mark Horninger gravatar image

Mark Horninger
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

A snippets from MSDN

Loss of the mirror and the witness

Assume you have configured database mirroring with a witness. When the mirror is unavailable, the principal runs exposed. While the mirror is unavailable, if the witness is also lost, the principal becomes isolated and can’t service the clients. Even though the principal database is running, it is not available to the clients. If you attempt to connect to the database, you get the message “Database is enabled for database mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.”

If the mirror or the witness cannot be brought back online quickly, then the only way to resume database service is to terminate the database mirroring session. To do this, you execute the following command after connecting to the master database of the principal server:

ALTER DATABASE SET PARTNER OFF

Note that you need to execute a SET PARTNER OFF command, and not a SET WITNESS OFF. SET WITNESS OFF will not work in this situation.

Once the mirror becomes available, you can re-establish the database mirroring session. The principal will start sending the log information to the mirror, and the mirror will eventually catch up. If you backed up the transaction log after terminating the database mirroring session, you need to restore the transaction log backup on the mirror before you can re-establish the database mirroring session. There is no need to do a full database backup / restore. Once the witness becomes available, you can join in the witness as well, but you have to establish the mirroring session with the mirror before the witness can join in.

Also, TransLogs will not be truncated during this "state", therefore plan for log growth.

See link

more ▼

answered Jun 01, 2010 at 04:53 PM

sp_lock gravatar image

sp_lock
9.2k 25 28 31

(comments are locked)
10|1200 characters needed characters left

What type of mirroring do you have? My experience with high performance is that as long as the principal has enough diskspace for logfiles, the principal is running. When the mirror is back, just resume the mirror connection. Note: depending on the amount of data to transfer, this will take some time. If you have a snapshot on the mirror and it's possible to drop this snapshot, do it! This will speed-up the synchronization. (create it afterwards)

Check the logfile on the principal after the databases are in sync. If the mirror was broken for some time, the logfile may be exploded.

more ▼

answered Jun 02, 2010 at 07:45 AM

Apeman gravatar image

Apeman
311 13 13 15

(comments are locked)
10|1200 characters needed characters left
In my experience this is bound by hardware limits - ie disk space to keep all transactions locally while the mirror is offline. If you have the space then it is pretty robust and will restart the synch process when the mirror is back online. Obviously this will flood your network connection while its catching up so you might see some system performance drop - depending on how much 'slack' there is in your network when under normal loads.
more ▼

answered Jun 08, 2011 at 01:49 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x57

asked: Jun 01, 2010 at 02:06 PM

Seen: 2451 times

Last Updated: Jun 01, 2010 at 02:06 PM