I plan to update this as I run across questions; I really like mirroring. Comparing to Clustering it is cheaper to setup also. I like clustering too together they can make a great solution for availability and reliability by building on each others strength. Anyhow this is just quick Q & A guide and reference links for Database Mirroring only.
Do the databases require to be on the same physical path on both the principle and mirror server?
No. Database mirroring is on database level with in SQL Server not on the file level.
Is it possible to set up mirroring on one computer?
Yes. For production system not recommend because you are defeating the purpose of mirroring; but for testing and development it is possible. You’ll have to make sure to give each end point different port on the same computer.
Is it possible to set up database mirroring on servers that are across domain?
Yes. Please reference link.
What changed for mirroring between SQL 2008 and SQL 2005?
SQL 2008 supports log compressor for Mirroring, Ref Link.
More to come later …
How do I force a manual failover?
ALTER DATABASE Test
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
But please be aware doing this will cause data loss; only do this if getting the services up is more important then losing some data. There are some conditions that much match and this can only be done on the mirror server.
- The principal server is down.
- WITNESS is set to OFF or witness is connected to the mirror server.
I don’t have a domain can I still use mirroring?
Yes. You can use certificates to authenticate between servers; same technique must be used when setting up mirroring across domain. Please reference link to understand how to use certificates with mirroring.
The Transaction Log files keeping getting last on the mirrored server? I can’t back it up how do I deal with it? (Link)
You should be backing up the transaction log on your principal server; any changes taking place there will be replicated over to mirrored partner. If your transaction file grew on both principal and mirrored server because you forgot to set up Transaction log backups you can backup transaction log and use DBCC shrinkfile on Principal server it will be replicated on to Mirror server.
Is it possible to setup mirroring with instances running on SQL 2005 and SQL 2008?
No. Database mirroring between partners and witness servers must be of same version. In addition the mirror partners must also be of the same edition. (Ref Links 2005 and 2008).
Mirroring Known Issues?
KB926900, The SQL Trace entries may be incorrect when you trace an instance of SQL 2005 that is configure to use database mirroring, Link.
KB937531, The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005, Link.
KB940254, Error message when you try to set a witness in a database mirroring session in SQL Server 2005: “The ALTER DATABASE command could not be sent to remote server instance ‘TCP://<servername>:<port>”, Link.
KB946036, FIX: Error message when you run a maintenance plan that contains a Back Up Database task in SQL Server 2005: “Failed:(-1073548784) Executing the query ‘BACKUP DATABASE”, Link.
This is not an exclusive list; just articles I ran into.
Database Mirroring References?
Paul S. Randal, MVP, Link.
Using Database Mirroring with Share Point, Link.
MSDN SQL 2005 Database Mirroring, Link.
MSDN SQL 2008 Database Mirroring, Link.
Cluster or Mirroring, Link.
Database Mirroring (2005) White Paper, Link.
Robert Davis, Pro SQL Server 2008 Mirroring; anther FAQ on Mirroring, Link.
Troubleshooting Database Mirroring Deployment (SQL 2005), Link.
Troubleshooting Database Mirroring Deployment (SQL 2008), Link.