June 12, 2012 at 3:00 pm
sonia.burns (6/12/2012)
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 😉
I haven't heard from my coworker yet. I'll go through other notebooks I have at home this evening and see what I can find.
June 12, 2012 at 3:15 pm
Thank you - if I find out anything I'll post here
June 13, 2012 at 4:05 pm
For the community, sonia.burns will be posting an update shortly. We were able to figure out what was going on after numberous emails back and forth during the day.
We got the database mirroring to work, and I will leave it to her to provide the full update.
June 13, 2012 at 4:23 pm
First of All a Massive KUDOS to Lynn to whom I am eternally grateful. Without his help I would probably be smashing up the server room with a lump hammer by now.
So because I was using a live database originally I set up a little test environment with two servers on the same domain. I copied over a small database to the principal server, backed it up and then restored it and the latest transaction log to the mirror server ensuring that the No Recovery option was set.
Instead of using the wizard I used code supplied by Lynn
Firstly to create the endpoint on both servers I ran the following.
CREATE ENDPOINT [DBMirror]
AUTHORIZATION [sa]
STATE=STARTED
AS TCP (LISTENER_PORT= 7022,
LISTENER_IP=ALL)
FOR DATA_MIRRORING (ROLE=ALL,
AUTHENTICATION=WINDOWS NEGOTIATE,
ENCRYPTION=SUPPORTEDALGORITHM AES RC4)
GO
No problems on either server.
Then I Grant permissions on both servers to my domain administrator (I know but at this point i was desparate). Using this code
GRANT CONNECT ON ENDPOINT::DBMirror TO [Domain\Administrator];
GO
No Problems there on either server
OK now to establish the connection starting first with the mirror server
ALTER DATABASE DatabaseName
SET PARTNER = ‘TCP://PRIMARY1:7022’;
GO
No problem and the code executed successfully, so am thinking that things are going my way.
Now to establish the connection on the principal server
ALTER DATABASE DatabaseName
SET PARTNER = ‘TCP://SECONDARY1:7022’;
GO
Nope I get that nuisance 1418 error. I don't get it, netstat says it is listening on 7022 and I can telnet - I'm using the domain administrator so what is going on.
At this point Lynn advised me to check my logs. On the principal server I see
10054(an existing connection was forcibly closed by the remote host)
Spurred on I check the error log on the mirror which tells me:
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint.
Now this is news to me, I thought I was connecting using the domain administrator.
Lynn told me to check my configuration using the SQL server configuration manager and what do you know, the sql server instance was running under the local account. So because this is a test I changed SQLServer Service to run as the domain administrator and it WORKED when I re ran, On my principal database.
ALTER DATABASE DatabaseName
SET PARTNER = ‘TCP://SECONDARY1:7022’;
GO
So now I will create an AD account for SQL which is less powerful than the domain administrator, make sure that my live environments SQL servers are running using the domain account and I will set up the mirroring using the steps outlined above.
I honestly can't thank Lynn enough for his patience and willingness to stick by me through this, he really is a superstar
June 13, 2012 at 4:24 pm
Just one little thing, I am not a her. 🙂
June 13, 2012 at 4:34 pm
:w00t: Sorry about that (I'm an idiot) - have fixed it in the forum. You are a superstar
June 13, 2012 at 5:41 pm
sonia.burns (6/13/2012)
:w00t: Sorry about that (I'm an idiot) - have fixed it in the forum. You are a superstar
Don't worry about, you aren't an idiot. I get it often.
I want to start a Men Bowling League Team, we'll call it Ladies Night Out. The men on the team would be Kim, Stacey, Shannon, and myself. :w00t:
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply