June 11, 2012 at 5:33 pm
I apologize if this does exist somewhere, I did look....... honest and didn't find an answer. It seem as though a lot of people have this problem but I haven't found a fix for me.
So basically I have two SQL 2008 R2 servers running on server 2008. They are both part of the same domain (most complainants of this issue are using workgroups).
My Mirror database was restored using no recovery, TCP is enable on both servers in the SQL configuration. I can telnet both servers from a remote on both the SQl and mirror ports. I granted the domain administrator to be the manager of the mirror endpoints on both servers. I ran sys.database_mirroring_endpoints and they are both saying that they started, oh and I turned the firewall off on both servers too.
I still get that 1418 server doesn't exit error.
Any ideas what I might be missing here? Any help or ideas very much appreciated
June 11, 2012 at 7:18 pm
Have you tried following steps:
Fix/WorkAround/Solution: Try all the suggestions one by one.
Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).
Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).
Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023?.
Suggestion 4: Make sure your firewall is turned off.
Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.
Suggestion 6: Try the following command as one of the last options.
GRANT CONNECT ON ENDPOINT::Mirroring TO ALL
Suggestion 7: Delete the end points and recreate them.
June 12, 2012 at 8:18 am
Thanks for answering, unfortunately I have tried this and I am always restoring new transaction logs (with no recovery) because it is taking so long to set up. Hmm, if I find out what the problem is I will post it here. But in the mean time if anybody has any ideas I would be very grateful
June 12, 2012 at 8:45 am
sonia.burns (6/11/2012)
I apologize if this does exist somewhere, I did look....... honest and didn't find an answer. It seem as though a lot of people have this problem but I haven't found a fix for me.So basically I have two SQL 2008 R2 servers running on server 2008. They are both part of the same domain (most complainants of this issue are using workgroups).
My Mirror database was restored using no recovery, TCP is enable on both servers in the SQL configuration. I can telnet both servers from a remote on both the SQl and mirror ports. I granted the domain administrator to be the manager of the mirror endpoints on both servers. I ran sys.database_mirroring_endpoints and they are both saying that they started, oh and I turned the firewall off on both servers too.
I still get that 1418 server doesn't exit error.
Any ideas what I might be missing here? Any help or ideas very much appreciated
When you say you granted the domain administrator to be the manager of the mirror endpoints, what exactly do you mean?
June 12, 2012 at 8:59 am
Hi Lynn.
I guess I didn't explain that properly, what I mean't was the service accounts for the instances I specified the domain administrator to be the service account on both the prinicple and the mirror. I don't have the firewall enabled on either server and both servers are members of the same domain. The mirror is definately still in recovery and I can definately telnet. Grrrrr, I think maybe threatening the servers with a hammer might be my only recourse 🙂
Your interest in helping me is much appreciated.
Sonia
June 12, 2012 at 9:09 am
Have you done this:
GRANT CONNECT ON ENDPOINT::endpoint_name
TO < domainaccount >
where enpoint_name is the mirror endpoint and domainaccount is the account that owns the endpoint.
June 12, 2012 at 9:20 am
Thanks Lynne, I am just restoring last nights logs to the mirror and then I will probably delete the endpoints and start again (3rd time lucky maybe), I will also follow your advise on the grant script and post back what happens.
June 12, 2012 at 9:23 am
sonia.burns (6/12/2012)
Thanks Lynne, I am just restoring last nights logs to the mirror and then I will probably delete the endpoints and start again (3rd time lucky maybe), I will also follow your advise on the grant script and post back what happens.
One, no 'e' at the end of my name. Second, I wish I could find my notebook where I have notes from setting up database mirroring at a previous employer. The GRANT CONNECT was the one thing I figured out myself when I was having problems getting it to work. I stumbled on some vague information during that time, but other than that I am working of 3+ year old memories.
June 12, 2012 at 9:26 am
I am so sorry about mis-spelling your name and I do really appreciate your help
June 12, 2012 at 10:52 am
No joy unfortunately, I started over recreated the security and the endpoints (there is no encryption btw). Ensured that the endpoints had started, ran the grant on both servers as recommended. Telnet from the principle to the mirror using the port 5022 and the FQDN with no issues and I still get that 1418 error. I wonder what it is...
June 12, 2012 at 11:01 am
sonia.burns (6/12/2012)
No joy unfortunately, I started over recreated the security and the endpoints (there is no encryption btw). Ensured that the endpoints had started, ran the grant on both servers as recommended. Telnet from the principle to the mirror using the port 5022 and the FQDN with no issues and I still get that 1418 error. I wonder what it is...
I am checking with a former coworker to see if I can get a sanitized version of any documentation I should have left at my former employer. They did implement DB Mirroring on another system after I left, so hopefully they didn't have to relearn what I had done initially.
June 12, 2012 at 11:11 am
Thank you so much Lynn, you're a star. I tried granting Public rights to the endpoint and it still gives me that error.
June 12, 2012 at 11:17 am
It is going to be something simple, pretty sure of that.
Can you post the steps (code included) that you used. Sanitize it as necessary, but it should still be what you did.
June 12, 2012 at 11:59 am
OK, tried to do with a much smaller database in case it was a problem with size but no joy 🙁
Anyway....
Firstly I logged into SQL on both servers using the domain administrator
Secondly I right clicked the database, chose tasks and then mirror, then configure security which lauches the wizard . Then...
I said no to including the witness server
Then I chose the principle server FQDN, cleared the encrypt data and gave the endpoint a name. I checked whether the port was already in use using the netstat command.
Then I connected to the mirror server using the FQDN and using the domain administrator to connect, cleared the encrypt data checkbox and then double check whether the port was in use already and gave the enpoint a name. (In trying to fix I have changed the listener port but to no effect).
For the service accounts I have put in the domain administrator with the domain in caps\administrator for both the principal and the mirror as both servers are a member of the same domain.
Click Finish
Both endpoints register a success status and I close the wizard.
I don't start mirroring but do the following.
Run query select * from sys.database_mirroring_endpoints on both systems to check that the enpoint has started
Checked that the domain administrator is a member of sysadmin on both systems
Run telnet to the FQDN and port from the principle to the mirror using the command prompt and code telnet dbmsi 5022 and it connects (where dbmsi is the name of the mirror)
I then go back and Start Mirroring and I get that 1418 error server does not exist, despite the fact that I have them both in my object explorer and there was no connection issue, no telnet issue and the principle server did create the endpoint on the mirror.
If you would rather I can send screenshots. I really can't express how grateful I am for you trying to help me.
June 12, 2012 at 2:56 pm
No further forward, On my test server I installed two instances of SQL server and repeated my previous mirroring steps, using the same ports and the same domain admin account and it works like it did in the classes I took. I wanted to have a mirror to protect us from hardware failure so therefore a need for a separate box but it looks like the problem I have is network related, but what can that be? I am using the domain administrator, I can telnet between the two so am completely lost.
I suppose at least mirroring between two instances on one box has proved to myself that I am not a compelte idiot 😉
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply