May 5, 2010 at 1:39 pm
The Primary and Mirror nodes are in different physical locations (in different states), on different subnets (192.168.1.x and 10.5.1.x), and in the SAME domain. I have created the endpoints via script using the SQL Server Service account. (i know, i know... bad dba :w00t:)
CREATE ENDPOINT [PrincipalMirroring]
AUTHORIZATION [DOMAIN\SQLService]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO
CREATE ENDPOINT [SecondaryMirroring]
AUTHORIZATION [DOMAIN\SQLService]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO
I restored the database and transaction log, and left the mirror database in the restoring state.
Next I configured the partner on the Primary server.
ALTER DATABASE [LancelotMirrorTest] SET PARTNER = N'TCP://Server2.domain:5022';
Then the wonderfull 1418 error appears.
I have verified that the servers are alive and accessible via ping and telnet.
This happens if I attempt to configure and start via script or wizard.
Any assistance is appreciated.
Thanks,
Greg
Greg Roberts
May 6, 2010 at 7:14 am
Digging a little deeper I have found the servers are running on different Service Packs. The Primary is x86 SP2. The Mirror is x64 SP3. Would the difference in Service Packs cause this issue?
There is a scheduled window to upgrade the principal to SP3 in the coming week.
Greg Roberts
May 6, 2010 at 1:35 pm
Don't think so.
http://msdn.microsoft.com/en-us/library/ms188712.aspx
The principal and mirror server instances must be running the same edition of SQL Server—either Standard or Enterprise.
May 6, 2010 at 2:38 pm
Here are the details from the SERVERPROPERTIES
Principal:
Version9.00.3042.00
LevelSP2
Edition Enterprise Edition
Mirror:
Version9.00.4053.00
LevelSP3
EditionEnterprise Edition (64-bit)
Greg Roberts
May 7, 2010 at 2:43 am
Are you able to run this from partner server?
ALTER DATABASE [LancelotMirrorTest] SET PARTNER = N'TCP://Server1.domain:5022';
Anything in the errorlog any service broker messages in the errorlog
Make sure the service accounts of the sql services have connect privileges on the relevant endpoints and make sure the endpoint is started.
May 7, 2010 at 7:22 am
The following message appears in the error log.
Database Mirroring login attempt by user 'DOMAIN\User'. failed with error: 'Connection handshake failed. The login 'DOMAIN\User' does not have CONNECT permission on the endpoint. State 84.'.
The odd thing is I am using the SQL Server Service account to connect. Where I try to grant connect permissions to the account i receive the 'Cannot grant CONNACT to dbo' error.
Do I need a dedicated Domain account for SQL mirroring when on different subnets? Like I said previously, when I configure the mirror on the same subnet I do not have a problem.
[edit]
The login that is recorded in the log is NOT the login I specified. It looks like my predecessor did not correctly document the service account. The 2 servers are using 2 different service accounts. the account in the log is the service account on the Principal. But it does not have permissions on the Mirror.
[/edit]
Greg Roberts
May 7, 2010 at 7:35 am
That solved it!
The 2 servers were running under different service accounts. Once i correctly supplied the account names the mirror established.
Thank you all for your assistance.
Greg
Greg Roberts
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply