none
Database mirroring

    Question

  • Hi, I'm trying to configure database mirroring on database named as test, I have already configured it on database adventureworks using T-SQL and the script which I used is this

    AT Principal:

    create endpoint database_mirroring state=started as tcp(listener_port=5022,listener_ip=(<ipaddress)) for database_mirroring (role=partner)

    AT MIRROR:

    create endpoint database_mirroring state=started as tcp(listener_port=5023,listener_ip=(<ipaddress>)) for database_mirroring (role=partner)

    AT MIRROR:

    Alter database adventureworks set partner='TCP://xpwindows7:5022'

    AT PRINCIPAL:

    Alter database adventureworks set partner='TCP://xpwindows7:5023' and this worked for me.

    when I try to configure mirroring on different database named test database

    AT PRINCIPAL:

    create endpoint database_mirroring2 state=started as tcp(listener_port=5022,listener_ip=(<ipaddress)) for database_mirroring (role=partner)

    I'm getting the below error

    Msg 7874, Level 16, State 1, Line 1
    An endpoint already exists with the bindings specified.  Only one endpoint supported for a specific binding.  Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.
    Msg 7807, Level 16, State 1, Line 1
    An error ('0x800700b7') occurred while attempting to register the endpoint 'database_mirroring2'.










    Thursday, March 15, 2012 2:24 PM

Answers

  • Sounds like a simple problem: You only need one Endpoint per SQL Server instance, and in fact you can only have one. So if you already have one DB configured for mirroring you don't need to run the create endpoint again for the second DB.
    • Proposed as answer by RamJaddu Thursday, March 15, 2012 2:58 PM
    • Marked as answer by karthiksqldba Saturday, March 17, 2012 9:58 AM
    Thursday, March 15, 2012 2:42 PM
  • OK, then it seems that you already have an endpoint defined. Check the sys.endpoints view to see how it looks and if it's started.Also I believe you are using the same endpoint for different instance.

    If you are creating the mirroring on same instance, go ahead without creating endpoint.


    Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India


    Thursday, March 15, 2012 2:43 PM

All replies

  • You cannot use the same endpoint for different database mirroring configuration. Just change the endpoint available and try it.


    Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India

    Thursday, March 15, 2012 2:30 PM
  • Hi, prakash thanks for replying

    I have change the endpoint but I'm getting same error

    AT PRINCIPAL:

    create endpoint database_mirroring2 state=started as tcp(listener_port=5026,listener_ip=(<ipaddress)) for database_mirroring (role=partner)

    I'm getting the below error

    Msg 7874, Level 16, State 1, Line 1
    An endpoint already exists with the bindings specified.  Only one endpoint supported for a specific binding.  Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.
    Msg 7807, Level 16, State 1, Line 1
    An error ('0x800700b7') occurred while attempting to register the endpoint 'database_mirroring2'.

    Thursday, March 15, 2012 2:34 PM
  • Sounds like a simple problem: You only need one Endpoint per SQL Server instance, and in fact you can only have one. So if you already have one DB configured for mirroring you don't need to run the create endpoint again for the second DB.
    • Proposed as answer by RamJaddu Thursday, March 15, 2012 2:58 PM
    • Marked as answer by karthiksqldba Saturday, March 17, 2012 9:58 AM
    Thursday, March 15, 2012 2:42 PM
  • OK, then it seems that you already have an endpoint defined. Check the sys.endpoints view to see how it looks and if it's started.Also I believe you are using the same endpoint for different instance.

    If you are creating the mirroring on same instance, go ahead without creating endpoint.


    Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India


    Thursday, March 15, 2012 2:43 PM
  • SQL Instance allows one endpoint no matter its role either Principal / Mirroring /Witness and you must create it one time, all databases can be used same endpoint for mirror setup or later data communication. If you have multiple instances you must create one endpoints for each SQL instance.

    Just remove the create endpoint statement from your code then it should work -Pls let us know how it goes.


    http://uk.linkedin.com/in/ramjaddu

    Thursday, March 15, 2012 7:56 PM
  • HI, all

    yes All of you guys are right

    I just continued with below steps

    I have taken full and log backup at principal server and restored it in mirror server with norecovery option, after that

    AT MIRROR

    ALTER DATABASE <DATABASENAME> SET PARTNER='TCP://<FQDN OF PRINICIPAL>:<PORTNOOFPRINICPAL>'

    AT PRINCIPAL

    ALTER DATABASE <DATABASENAME> SET PARTNER='TCP://<FQDN OF MIRROR>:<PORTNOOFMIRROR>'

    So the conclusion is you have to create endpoint only once and use the same endpoint to configure mirroring again on a different database.

    Once again  thanks everyone.

    Saturday, March 17, 2012 10:06 AM