August 23, 2010 at 12:53 pm
After I finished my morning duties I figured I take some time to learn about something I've been wanting to learn for a while. That is, database mirroring. After hitting a few snags with the wizard I got it up and running. I was able to failover using the UI and also using script. So far so good. I added a witness using the UI and it didn't throw an errors so I'm assuming everything is fine. It created an endpoint on the witness server and no issues reported.
My problem is that I cannot get it to failover automatically. I shut off the SQL Server database service on the principal. I would have expected a short downtime and then be able to query the mirror. No such luck. On the mirrored (what should now be principal) I get the following error:
Msg 954, Level 14, State 1, Line 1
The database "mirrorTest" cannot be opened. It is acting as a mirror database.
The principal is inaccessible since the DB is now down.
Any thoughts as to what may be going on? Any advice on this would be greatly appreciated.
August 24, 2010 at 12:58 pm
Ok folks... So I finally cracked the code on this. There were two issues. The first was:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://server1.mydomain.com:5022'
This was a hard one to track. I've determined that a good way to test out your endpoints is if you can telnet to them. For example, telnet server1.mydomain.com 5022. If it connects, you're set up correctly. In my case I kept getting an error saying it couldn't connect. Similar to what you get when you type the wrong address. I was able to connect to the other endpoints on the mirror and witness so I knew (or assumed) that it was a network or firewall problem. Luckily for me it was! Because this was a test box it had windows firewall enabled. The fix was to add an exception for TCP traffic on the port I was using. In my case, it was port 5022.
The second problem I had dealt with permissions. I was receiving the following error:
Database Mirroring login attempt by user 'mydomain\sqllogin.' failed with error: 'Connection handshake failed. The login 'mydomain\sqllogin' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 192.168.209.14]
Once I gave the login the appropriate permissions we were up and running. I have to say... Mirroring is pretty cool. I'm curious as to how it operates in real life.
J.D.
August 24, 2010 at 1:04 pm
Thanks for posting back with your own solution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply