Simple Mirror Scenario - Can't get it to work

Question

Hello all,

I am trying to prove out a concept involving a reporting database that is a snapshot of a mirror.  The first step in this process is to get a database up and mirroring, but I can't even get that to work.  Here are the steps I am taking (with the SQL code for each step listed below:(

STEP 1 - Create a blank database on the principal server (in-rca-sql)

STEP 2 - Create a blank database on the mirror server (ob-rca-sql\installs)

STEP 3 - Backup the database and transaction log on the principal database (in-rca-sql)

STEP 4 - Restore the database using Step 3's .bak and .trn files onto the mirror server (ob-rca-sql\installs)

STEP 5 - Create ENDPOINTs on the principal and mirror

STEP 6 - Enable the mirroring

 

STEP 1 CODE ------------------------------------------------------------------------------------------------------

USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Craig_Mirror')
DROP DATABASE [Craig_Mirror]
GO
CREATE DATABASE [Craig_Mirror] ON  PRIMARY 
( NAME = N'Craig_Mirror', FILENAME = N'E:\Craig_Mirror.mdf' ,
SIZE = 4000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Craig_Mirror_log', FILENAME = N'E:\Craig_Mirror.ldf' , 
SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO 

STEP 2 CODE ------------------------------------------------------------------------------------------------------

Identical to step 1

STEP 3 CODE  ------------------------------------------------------------------------------------------------------ 

BACKUP DATABASE Craig_Mirror TO DISK ='E:\Craig_Mirror.bak' WITH INIT
GO
BACKUP LOG Craig_Mirror TO DISK ='E:\Craig_Mirror.trn' WITH INIT
GO

STEP 4 CODE ------------------------------------------------------------------------------------------------------

restore database Craig_Mirror from disk ='E:\Craig_Mirror.bak' with norecovery, 
replace,
move 'Craig_Mirror' to 'E:\Craig_Mirror.mdf',
move 'Craig_Mirror_log' to 'E:\Craig_Mirror_log.ldf'
go
restore log Craig_Mirror from disk ='E:\Craig_Mirror.trn' with norecovery, replace,
move 'Craig_Mirror' to 'E:\Craig_Mirror.mdf',
move 'Craig_Mirror_log' to 'E:\Craig_Mirror_log.ldf'
go

STEP 5 CODE ------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Mirroring') 
DROP ENDPOINT [Mirroring]
GO
CREATE ENDPOINT [Mirroring] 
AUTHORIZATION [Domain\UserName]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

STEP 6 CODE ------------------------------------------------------------------------------------------------------

I did this in the GUI

-Clicked Configure Security

-Selected NO Witness Server

-Selected my principal server instance as in-rca-sql

-Selected my mirror server instance as ob-rca-sql\installs (logged into it with windows credentials)

-Did not setup any service accounts (left them blank)

 

After all of the above steps were complete, I get the following error:

The server network address "TCP://OB-RCA-SQL.DOMAIN.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

 

The endpoints are both in the STARTED status

The SQL Service is running under a domain account called 'Network Service' (same account, same domain for both servers)

The SQL Log has this line in it:

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://OB-RCA-SQL.DOMAIN.com:5022'.

My network team has assured me that port 5022 is open and accessible on both servers, they can "telnet" them from each server to the other.. whatever that means.

 

I have spent almost two days on this now, if anyone can help I would REALLY appreciate it!

Answer 1

Hi,

Is it possible for you to run this to see if we can see the permissions on the endpoint? This will give you the list of endpoints and what principals have been granted connect to the endpoints.

SELECT perm.class_desc, perm.permission_name, endpoint_name = e.name, perm.state_desc,
 grantor = prin1.name, grantee = prin2.name
FROM master.sys.server_permissions perm
 INNER JOIN master.sys.server_principals prin1 ON perm.grantor_principal_id = prin1.principal_id
 INNER JOIN master.sys.server_principals prin2 ON perm.grantee_principal_id = prin2.principal_id
 LEFT JOIN master.sys.endpoints e ON perm.major_id = e.endpoint_id
WHERE  perm.class_desc = 'ENDPOINT'

 

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog or Twitter.
Please click the Mark as Answer button if a post solves your problem!

Answer 2

just replace this name OB-RCA-SQL.DOMAIN.com   with the IP,

leave the settings of the primary server same ,but change the mirror server name with its IP, You can see the ip of the server with ipconfig /all in your mirror server

Answer 3

Thank you for the suggestion, unfortunately it did not work.  I got the same error as before.

Answer 4

Here are the results:   http://i.imgur.com/yyp9s.png

I get the same results when I run it on the mirror and the principal.

Answer 5

http://msdn.microsoft.com/en-us/library/aa337361.aspx

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

Those are the default system endpoints. You need to create an endpoint for mirroring and grant the user that you will use for mirroring connect permissions on the endpoints.

http://www.simple-talk.com/sql/database-administration/sql-server-endpoints-soup-to-nuts/

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog or Twitter.
Please click the Mark as Answer button if a post solves your problem!