Why are my SQL Server 2008R2 databases not mirroring?

Question

I'm trying to set up database mirroring in SQL Server 2008R2. I've taken a full backup and a transactional backup from my principal database and I've restored both WITH NORECOVERY.

However, my mirror database is now stuck in RECOVERING mode and when I hit "start mirroring" on my principal, it says it can't connect.

What am I doing wrong?

(削除) Edit: I should probably state that my database is rather large (the mdf file is about 4.8GB) so that could be why. (削除ここまで)

Edit2: I've also tried doing this with both firewalls turned off so I know it's not a firewall issue.

Edit3: I've run the SQL that Mark suggested. The principal results are here: http://piersonthe.net/principal.xls and mirror are here: http://piersonthe.net/mirror.xls

It's worth noting that I got the following error when I ran the query on the mirror: Msg 927, Level 14, State 2, Line 1 Database 'RHSCMSSites' cannot be opened. It is in the middle of a restore.

• Martin S. Stoller: Just for clarity, did you also restore the transaction backups? And 5GB is not large :) The free, "Express SQL 2008" version supports 10GB per DB, to put things into perspective...
• Mark Storey-Smith: Can you add the full error you get when tryng to initiate the mirror?
• Piers Karsenbarg: @Martin: Yes I restored the transaction backups (and this was done WITH NORECOVERY as well)
• Piers Karsenbarg: @Mark: Error message here: piersonthe.net/mirroring.png I've tried telnetting both ways on port 5022 so the firewall is allowing me through. According to this: bit.ly/nezl0T this might be caused by it not being in WITH NORECOVERY, which technically it's not becuse it says its RECOVERING
• Mark Storey-Smith: Forgot to ask... are these servers in the same domain or are you authenticating with certificates?

Answer 1

After all that one of the servers didn't have the SQL Server service running under the same user. I'm an idiot.

• Nick Chammas: Aren't we all? :)
• Thomas Stringer: Honest mistake, and one you'll never make again. :)
• Piers Karsenbarg: Definitely a face-palm moment

Answer 2

Recovering is the correct state for the database to be in at the stage you've got to.

First, script the endpoints on both the mirror and principle and check that the ports are those you expect and that you can telnet to both ports from both servers. Double check that the endpoint state is 'Started'.

Second, if the endpoints are as expected and you've confirmed connectivity is ok, try initiating the mirror from TSQL. The sequence (after database and log restore) should be:

-- On MIRROR
ALTER DATABASE MyDatabase
 SET PARTNER = 'TCP://PrincipalServerX.MyCompany.com:5022’
GO
ALTER DATABASE MyDatabase
 SET PARTNER SAFETY OFF
GO
-- On PRINCIPAL
ALTER DATABASE MyDatabase
 SET PARTNER = 'TCP://MirrorServerX.MyCompany.com:5023’
GO
ALTER DATABASE MyDatabase
 SET PARTNER SAFETY OFF
GO

Edit: Steps above not helping, next up...

Can you run the following script on both servers and post the output:

SELECT
 e.name
 , e.endpoint_id
 , e.principal_id
 , e.protocol
 , e.protocol_desc
 , ec.local_net_address
 , ec.local_tcp_port
 , e.[type]
 , e.type_desc
 , e.[state]
 , e.state_desc
 , e.is_admin_endpoint
FROM
 sys.endpoints e
LEFT OUTER JOIN 
 sys.dm_exec_connections ec
ON ec.endpoint_id = e.endpoint_id
GROUP BY
 e.name
 , e.endpoint_id
 , e.principal_id
 , e.protocol
 , e.protocol_desc
 , ec.local_net_address
 , ec.local_tcp_port
 , e.[type]
 , e.type_desc
 , e.[state]
 , e.state_desc
 , e.is_admin_endpoint

• Piers Karsenbarg: Ok, I did this. On the mirror I got three message: one telling me that the database is in the middle of a restore and cannot be opened, one telling me that the database is already enabled for mirroring and one telling me that the database is not configured for database mirroring.
• Piers Karsenbarg: On the principal, I got the following: one telling me that the mirror cannot be reached and one telling me that the database is not configured for database mirroring.
• Piers Karsenbarg: Before doing this, I did a complete restore on the principal, then took a full and transaction log backup of it and moved it over to the mirror, so it's completely "from scratch" again.
• Mark Storey-Smith: Added a query above, can you run and add to your post.
• Piers Karsenbarg: Bounty given as that stuff really helped (even though it wasn't the answer)

Answer 3

You shouldn't restore it with recovery, that leaves an active db. To apply transaction logs you need to use with norecovery (restoring) or with standby (read only).

• Piers Karsenbarg: Sorry, I meant with norecovery. I've updated my question.
• Thomas Stringer: I don't believe you can use with standby in database mirroring.
• JamesRyan: I was talking about the modes where you can still restore logs but I think it can be in standby before you set up mirroring but will be taken out of that mode. You are right that the recommendation is to use norecovery though.