Help for Database Mirroring

  • Can you script out your endpoints so we can see how they are configured?

    Are you using the wizard or setting it up manualy with T-SQL?

    If there are other instances on any of the servers involved, make sure they don't have endpoints set up using the same port as another endpoint on the server (different instance).

    If need be, check all of the instances on the server and delete all pre-existing endpoints.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • I can able to setup the Prinicipal and Mirror and manual failover is also happening successfully. While adding the witness I am getting the error. Below are the script,

    /* Creating an Endpoint in the Principal Server*/

    CREATE ENDPOINT [MirrorEndPoint]

    STATE=STARTED

    AS TCP (LISTENER_PORT =5022)

    FOR DATA_MIRRORING (ROLE = PARTNER,

    AUTHENTICATION = WINDOWS NEGOTIATE,

    ENCRYPTION = REQUIRED ALGORITHM AES)

    /* Creating an Endpoint in the Mirror Server */

    CREATE ENDPOINT [MirrorEndPoint]

    STATE=STARTED

    AS TCP (LISTENER_PORT =5022)

    FOR DATA_MIRRORING (ROLE = PARTNER,

    AUTHENTICATION = WINDOWS NEGOTIATE,

    ENCRYPTION = REQUIRED ALGORITHM AES)

    /* Creating an Endpoint in the Witness Server */

    CREATE ENDPOINT [MirrorEndPoint]

    STATE=STARTED

    AS TCP (LISTENER_PORT =5023)

    FOR DATA_MIRRORING (ROLE = WITNESS,

    AUTHENTICATION = WINDOWS NEGOTIATE,

    ENCRYPTION = REQUIRED ALGORITHM AES)

    /* Assign Principal Server for Mirror Server Execute From Mirror */

    ALTER DATABASE Mirroring_db1 SET Partner = 'TCP://Principal.domian:5022'

    /* Assign Mirror Server for Principal Server Execute From Principal */

    ALTER DATABASE Mirroring_db1 SET PARTNER = 'TCP://Mirror.domain:5022'

    /* Assign Witness Server for Principal Server Execute From Principal */

    ALTER DATABASE Mirroring_db1 SET Witness = 'TCP://Witness.domain:5023'

    ---------------------------------------------------
    Thanks,
    Satheesh.

  • How many instances are on your mirror/witness server? Did you make sure that there are no instances using the witness port already?

    Also check your hosts file on both machines and make sure one of them doesn't have a bad entry for the other machine. Check for entries with both the FQDN and just the machine name.

    Also, try explicitly granting connect permissions to all endpoints for the SQL Server service accoutn fo the other instances.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • I am thankful to everyone who were trying to help me.

    There are three instance in my mirror/witness server and I ensured that none of the instance is using the witness port already.

    In host file, I had tested with machine name alone and then with FQDN and with both, I am getting the same error on all the cases.

    Providing explicit access also doesn't help me.

    Below are two scenario's which I hope will bring some insight to my problem,

    Scenario 1 : ServerA/Instance01 - Principal, ServerB/Instance01 - Mirror. If I try to add ServerB/Instance02 as witness I am getting the error "The ALTER DATABASE command could not be sent to the remote server instance 'TCP://WitnessServer:5023'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

    ".

    Scenario 2 : ServerB/Instance01 - Principal, ServerA/Instance01 - Mirror, in this case if I add ServerB/Instance02 as witness it's adding sucessfully. I can start the mirroring and manual failover is also happening sucessfully. But if I stop the service of ServerB/Instance01-Principal, the mirror server ServerA/Instance01 is becoming (Principal, Disconnected / In Recovery).

    The problem is with ServerB/Instance02 to speak with ServerA/Instance01.

    ---------------------------------------------------
    Thanks,
    Satheesh.

  • Erode P. Satheesh (5/7/2009)


    But if I stop the service of ServerB/Instance01-Principal, the mirror server ServerA/Instance01 is becoming (Principal, Disconnected / In Recovery).

    The problem is with ServerB/Instance02 to speak with ServerA/Instance01.

    Actually, that's not the case. In scenario 2, if ServerB/Instance02 can not communicate with ServerA/Instance01, then ServerA will not take over as Principal. There has to be quorum in order for a machine to switch its role. So the two servers are able to communicate.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Yes. And also whenever the ServerA/Instance01 is principal and ServerB/Instance02 is witness then the quorum gets lost. I need to identify why the quorum is getting lost in this scenario.

    ---------------------------------------------------
    Thanks,
    Satheesh.

Viewing 6 posts - 31 through 35 (of 35 total)

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