Help with Mirroring 2 databases (no witness)

Question

Post reply

Help with Mirroring 2 databases (no witness)

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

Use this to check:-

SELECT

e.name AS [Endpoint Name],

p.STATE AS [State],

CONVERT(SYSNAME, SUSER_NAME(p.grantor_principal_id)) AS [Grantor],

p.TYPE AS [Permission],

CONVERT(SYSNAME, SUSER_NAME(p.grantee_principal_id)) AS [Grantee]

FROM

sys.server_permissions p

INNER JOIN

sys.endpoints e ON p.major_id = e.endpoint_id

WHERE

e.name = 'Mirroring'

GO

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

Can you try with IP and port# instead of serverne, port

I had similar issue and using IP worked.... There was some issue with FQDN.

Regards,
SQLisAwe5oMe.

oskargunn SSCommitted Points: 1951 More actions · Answer 3

Ok, this might be the problem. If I take away the where clause I get this:

Endpoint NameStateGrantorPermissionGrantee

TSQL Local MachineGsaCO public

TSQL Named PipesGsaCO public

TSQL Default TCPGsaCO public

TSQL Default VIAGsaCO public

oskargunn SSCommitted Points: 1951 More actions · Answer 4

SQLisAwE5OmE: Yes I began using only ip address and port number. Then I tried the FQDN to see if that was the problem.

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 5

So there's not an endpoint setup. Here's the code to create one:-

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 5023 )

FOR DATABASE_MIRRORING (

AUTHENTICATION = WINDOWS KERBEROS,

ENCRYPTION = SUPPORTED,

ROLE=ALL);

GO

oskargunn SSCommitted Points: 1951 More actions · Answer 6

There should be an endpoint installed.

SELECT * FROM sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'

This query returns this set:

nameendpoint_idprincipal_idprotocolprotocol_desctypetype_descstatestate_descis_admin_endpointportis_dynamic_portip_address

Mirroring655412622TCP4DATABASE_MIRRORING0STARTED050230NULL

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 7

OK, so the endpoint is there but the user doesn't have permissions to connect

oskargunn SSCommitted Points: 1951 More actions · Answer 8

This command: GRANT CONNECT ON ENDPOINT....

results in the following error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

Should I use a domain user or local sql server user?

I've tried both, and always get the same error.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

oskargunn (9/26/2014)
This command: GRANT CONNECT ON ENDPOINT....
results in the following error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Should I use a domain user or local sql server user?
I've tried both, and always get the same error.

You must use a domain account for the sql server services, grant the service account connect to the endpoint.

At this point i would recommend dropping and re creating the endpoiint from the beginning

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

oskargunn SSCommitted Points: 1951 More actions · Answer 10

The primary site is running on Windows 2008 and the Mirror site is running on Windows 2012.

Could it be that that is an issue ?

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 11

Try Perry's recommendation and see if that allows the mirroring to be setup.

The Windows version should not be an issue.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

On the Principal drop the endpoint

--drop endpoint

DROP ENDPOINT PrincipalEndpointName

On the principal, create the windows login for the mirror instances svc account

--Create login

CREATE LOGIN [yourdomain\themirrorsvcuser] FROM WINDOWS

Create the endpoint

CREATE ENDPOINT PrincipalEndPoint

STATE=STARTED

AS TCP ( LISTENER_PORT = 5023 )

FOR DATABASE_MIRRORING

(AUTHENTICATION = WINDOWS NEGOTIATE,

ENCRYPTION = REQUIRED ALGORITHM AES,

ROLE = PARTNER) -- or ALL )

Now grant connect to the mirror svc account you created above

GRANT CONNECT on ENDPOINT::PrincipalEndPoint TO [UKTRADING\Mirrorsvc_User]

Once done, do the reverse.

Create the endpoint on the mirror, add the principals svc account as a sql server login and grant i connect on the endpoint

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

nesreen Grasshopper Points: 11 More actions · Answer 13

I got the same error ,I run on sql server 2012

I acn't telnet with

telnet servername:5022

it give me an error that couldn't reach server on port 23

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

Correct syntax is

telnet servername 5022

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉