Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Sep 1 '11 at 13:58

This question came from our site for professional system and network administrators.

3  
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... –  Martin S. Stoller Sep 1 '11 at 14:36
3  
Can you add the full error you get when tryng to initiate the mirror? –  Mark Storey-Smith Sep 1 '11 at 14:40
    
@Martin: Yes I restored the transaction backups (and this was done WITH NORECOVERY as well) –  Piers Karsenbarg Sep 1 '11 at 14:46
    
@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 –  Piers Karsenbarg Sep 1 '11 at 14:52
    
Forgot to ask... are these servers in the same domain or are you authenticating with certificates? –  Mark Storey-Smith Sep 2 '11 at 0:15

3 Answers 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).

share|improve this answer
    
Sorry, I meant with norecovery. I've updated my question. –  Piers Karsenbarg Sep 1 '11 at 14:17
1  
I don't believe you can use with standby in database mirroring. –  Thomas Stringer Sep 5 '11 at 15:41
    
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. –  JamesRyan Sep 6 '11 at 10:45

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
share|improve this answer
    
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 Sep 2 '11 at 8:42
    
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 Sep 2 '11 at 8:44
    
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. –  Piers Karsenbarg Sep 2 '11 at 8:45
    
Added a query above, can you run and add to your post. –  Mark Storey-Smith Sep 2 '11 at 15:02
1  
Bounty given as that stuff really helped (even though it wasn't the answer) –  Piers Karsenbarg Sep 7 '11 at 9:48
up vote 5 down vote accepted

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

share|improve this answer
2  
Aren't we all? :) –  Nick Chammas Sep 6 '11 at 13:37
1  
Honest mistake, and one you'll never make again. :) –  Thomas Stringer Sep 7 '11 at 2:58
1  
Definitely a face-palm moment –  Piers Karsenbarg Sep 7 '11 at 9:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.