I recently gave two webcasts on database mirroring – Mirror Mirror on the Server, Who is the Principal of Us All? and Mirror Mirror on the Server, Who is the Mirror of Us All? – which generated a lot of great discussions!
There are a few topics that have come up over and over again, so here are my answers to your database mirroring frequently asked questions!
- What versions of SQL Server is database mirroring available in?
- SQL Server 2005, 2008, 2008R2, and 2012
- What editions of SQL Server is high safety (synchronous) available in?
- What editions of SQL Server is high performance (asynchronous) available in?
- Do the principal and the mirror need to be on the same version?
- You can have the principal be one version – say 2008R2 – and the mirror another – say 2012. This is how an upgrade with minimal downtime can be accomplished using mirroring! However, once you fail over to the mirror, you can no longer fail back.
- Do the principal and the mirror need to be on the same edition?
- To be fully supported by Microsoft, yes.
- Can multiple databases on the same instance be in mirroring sessions?
- How do I mirror the system database (master, model, msdb, tempdb)?
- You can’t! This isn’t supported.
- If I set up mirroring, do I still have to take full, differential, or transaction log backups?
- Yes! Mirroring increases the availability of your databases. It is not a substitute for regular backups, however.
- How does index maintenance (rebuilds) affect the mirror?
- Transactions that fill up the log can affect the performance of mirroring. When the amount of information in the log increases, the amount of information that needs to be sent to and committed to the mirror increases also. If performance is crucial, you may want to do index maintenance more frequently, so it takes less time. If large transactions such as batch inserts are affecting performance, break those into smaller transactions.
What other questions do you have?