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?
asked
SQL Server 2005 mirroring - connection lost?
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
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.
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.
