SQL Server 2008 database mirroring madness

Question

I'm trying to get database mirroring to work on SQL Server 2008 between two computers. I checked connectivity, but here's what I end up with: on the principal machine, the server can connect to the mirror but refuses to set up a mirroring partnership due to it being 'unable to connect' (I checked connectivity, everything works).

The weird thing has happened on the mirror. First, the mirror now thinks it's being mirrored. Second, after I delete and recover the mirrored database, it goes into Restoring... mode and just gets stuck there.

Any ideas you might have on this are appreciated. Thanks!

Answer 1

Recovering is a state that allows extra backup logs to be restored to the database. You can move from recovering to online with this command:

RESTORE DATABASE YourDb WITH RECOVERY

Answer 2

• What authentication did you set up? Windows or certificates?
• Did you open the mirroring endpoint listening port on both machines?

Attach profiles to both machines and watch for these events:

• Audit Database Mirroring Login Event Class
• Database Mirroring State Change Event Class
• Broker:Connection Event Class

Look into the ERRORLOG of both machines for any event traced when you attempt to set up the mirroring session.

What happens is that when you attempt to set up the mirroring session both machines (principal and mirror) will attempt to open two communication channels with one another and they both authenticate each other. If your networkj connectivity is broker in any direction (firewall, dns, ipsec) or if the windows authentication fails (domain trust issue) or if the SSL authentication fails (when using certificates, not correctly deployed) or if the SQL authorization fails (no CONNECT permission on the mirroring endpoint), any of these will prevent the session from succeeding and you get the 'unable to connect' message. To find the root cause, Profiler is your friend.