Help with Mirroring 2 databases (no witness)

  • 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

  • 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.

  • 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

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

  • 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

  • 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

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

  • 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.

  • 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" 😉

  • 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 ?

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

    The Windows version should not be an issue.

  • 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" 😉

  • 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

  • Correct syntax is

    telnet servername 5022

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

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

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

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