We have had very good luck with SQL Server 2005 Database Mirroring over the last 18 months. It is much easier to configure and maintain than database clustering (which we also use), and it provides some key advantages (such as extremely fast fail-over) that are extremely useful for our environment. Having said that, here are a few observations and troubleshooting tips that I have learned over that time.
First, never rely on the database status indicator in SQL Server Management Studio (SSMS). When you have database mirroring enabled, you will normally see (Principal, Synchronized) as a status next to each mirrored database in SSMS. Unfortunately, if there is a problem with database mirroring (which is pretty rare), this status is not automatically refreshed to show the changed status. Instead, I recommend running this query periodically (wrapped in a stored procedure, perhaps called by a web page or desktop client application). It will alert you to problems with database mirroring, replication, etc.
-- Find the problem with the transaction log SELECT name, database_id, log_reuse_wait_desc FROM sys.databases
Second, if you do detect a problem, the first place to look is the SQL Error Log and the Windows Application Event Log. After looking there, these queries can help troubleshoot connectivity problems:
-- Check the endpoints SELECT name, type_desc, port FROM sys.tcp_endpoints; -- Check the role and whether the endpoint is started SELECT name, role, role_desc, state_desc FROM sys.database_mirroring_endpoints; -- Check permissions for login SELECT EP.name, SP.STATE, CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) AS GRANTEE FROM sys.server_permissions SP , sys.endpoints EP WHERE SP.major_id = EP.endpoint_id ORDER BY Permission,grantor, grantee;
It is also a good idea to confirm that you can ping and TELNET from the mirror to the principal and vice/versa.
Third, you need to be more aware of when you do common database maintenance tasks (like index rebuilds, index reorganizations, etc.) Remember, with database mirroring, every action that goes into the transaction log on the Principal has to be sent over the network to the Mirror side, and then replayed there. This means that you may overload your network or the I/O subsystem on the Mirror during maintenance, so be careful. The mirror database does not get the benefit of the SQL Server Lazy Writer, so you will see more I/O on the mirror than you expect. SQL Server 2008 will compress the transaction log data (log stream compression) that is sent over the network (at the cost of some CPU), which will help with the network portion of this issue.
This portion of Books Online has more troubleshooting tips.