I recently set up SQL 2005 database mirroring (synchronous with witness) on a couple of environments and enjoyed the experience but have a few questions:
- Does the transaction log chain get broken when a fail-over occurs? Do I need to schedule a full backup to continue transaction log backups on the secondary server?
- I've noticed that DBCC CHECKDB is executed during a fail-over, does anyone know what options are specified? will this impact VLDBs?
- The mirroring endpoints seem to be tied into my domain account I was using whilst configuring mirroring through the wizard. Will mirroring fail if my domain account is ever removed? Should I alter the endpoints to use the server's domain service account?
1) No the log chain is not broken - the mirror database is an exact byte-for-byte copy of the principal. It will be in the FULL recovery model and knows what state the backups are in from the various LSNs (log-sequence numbers) in the boot page of the database. (See Search Engine Q&A #20: Boot pages, and boot page corruption.) Note that any in-flight transactions will be rolled-back though - transactions never survive any kind of failover.
2) No it's not. It's just reporting the last-known-good time when the database starts up (the time that CHECKDB last run without finding corruptions). See this blog post for details: CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?. In SQL 2005, a manual failover will force-restart the database. In 2008 this no longer happens and recovery is just completed (it can be a little faster). Coincidentally, this is also stored in the database boot page.
3) When endpoints are created they're only accessible to members of the sysadmin role. There shouldn't be any issues in the scenario you describe.
Hope this helps!