Database Mirroring using Named Instances RRS feed

  • Question

  • Hello,

    I am trying to configure Database Mirroring between two servers running named instances.  When I run through the Database Security for the mirroring I select the Named instance e.g. invdbdr\lux but when that process has completed it shows as TCP://INVDBDR.jersey.mourant.local:5022 (no named instance details)

    This is preventing me from configuring mirroring as when I start mirroring I get an error stating that the remote copy of the database does not exist.  Which is the case as it's going to the wrong place. 

    I have setup client and server aliases but it's still not working

    Please help :-(
    Tuesday, July 7, 2009 11:29 AM

All replies

  • Its the port that matters here.  The tcp/ip address will never have the instance name in it since that instance name is only an address for pointing to the correct port for that instance in browser services.(or the default 1433 install if SQL 2000) 

    My advice is set up the end point on both server sides then try to to make sure that your not trying to use a port that was already set up. 

    After that go down the usual mirror cant connect problem list

    port being used already
    named pipes not enabled
    remote connections not allowed
    Trying to set up with a user that hasn't sys and windows admin access on other side of mirror
    UAC being enabled

    If that does not help look in windows and SQL error logs and see if there is a second error number.  Generally, its on a entry by itself with no text explanation.
    Tuesday, July 7, 2009 2:35 PM
  • Usually, a PING and TELNET test will tell you if the port is open. Are you using domain accounts for your SQL Server service on both the principal and the mirror? If not, then you have to implement this using certificates.  Check out this article for a detailed description
    bass_player http://bassplayerdoc.blogspot.com/
    Tuesday, July 7, 2009 11:39 PM
  • Remember that just because a port is open does not mean that what you want is on that port.  SQL does no checking when setting up remote endpoints for mirroring as to what ports are available.  This can be a problem if you have multiple SQL instances with mirroring.
    Wednesday, July 8, 2009 4:42 PM
  • I totally agree. I'd first query the sys.endpoints and sys.tcp_endpoints and check the port number from there. This is what I use for troubleshooting database mirroring
    bass_player http://bassplayerdoc.blogspot.com/
    Saturday, July 11, 2009 4:49 PM
  • Hi
    I have following problem: my witness is a named instance and i cant add it to existing mirroring session.
    Master key, certificates,logins,users,endpoints are created when needed, but following command fails:
    ALTER DATABASE A3WTrack  SET WITNESS = 'TCP://xx.xxxxxx.com\sqlexpress:5022'
    with this error message :
    Msg 1449, Level 16, State 5, Line 1
    ALTER DATABASE command failed due to an invalid server connection string.

    Please help!!!!!!!!!
    Friday, July 17, 2009 5:44 PM
  • I had this same exact problem. I fixed my issue by doing a few things. But I think one of these could possible help you. Turn on the SQL Browser service on all three machines, principle, mirror and witness. In SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQL Server - TCP/IP -> Configure the TCP/IP Protocol "Listen ALL", Make sure that on the IP Address Tab you have the correct IP and Port and that it is Active and enabled.


    Wednesday, August 22, 2012 8:34 PM
  • If you add the Witness by specifying the Listener Endpoint you don't specify an instance name. It is just ALTER DATABASE xxx SET WITNESS='TCP://xx.xx.com:5022

    Mirroring uses TCP endpoints, not instance names to connect. And other than instances the endpoints have fixed ports.

    Thursday, August 23, 2012 5:47 AM