locked
Neither the partner nor the witness server instance for data... RRS feed

  • Question

  • I am getting this error while trying to setup mirroring.
    "Neither the partner nor the witness server instance for database "shop" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

    I am just using principal and mirror server, there is no witness server. I have tried fully qualified name and ip for the servers also. I can connect from the principal server to the mirror server using the management studio and it also creates endpoints on prinicipal and witness. But then gives me an error that it cannot find the instance of the mirror server and if I try again it gives the error above. I can also telnet to port 5022.

    I have used -T1400 as the startup parameter.

    I did the backup and recovery based on this link http://msdn2.microsoft.com/en-us/library/ms189053.aspx and the mirror server is in recovery mode now.

    Friday, January 27, 2006 7:52 AM

Answers

  • Don't forget that the syntax is 'tcp://ndiversbk.dds.com:5022'

    If the telnet kicks you out after 1 second, then there is a problem with security and the endpoint.  It should let you type 16 keystrokes and then kick you out.

    Did you specify the 1400 trace flag?

    Thursday, February 23, 2006 12:35 AM

All replies

  •  

    Can you list the steps you followed when setting up mirroring?

    Thanks,

    Matt

    Monday, January 30, 2006 4:55 PM
  • This error message indicates that the partner thinks that its database should be the principal database, but cannot open the database up because the server needs quorum.

    Quorum is required to serve the database if the safety is set to FULL and the witness server has been set. This prevents "split brain". Right now the server doesn't know if the partner and witness have gone ahead and failed over the database to serve it, and so this server leaves its database offline.

    If you are getting the "cannot find that server or the instance isn't started" error, then there is a troubleshooting guide in BOL. Most likely it is a security problem.

    Thanks,

    Mark

    Monday, January 30, 2006 7:20 PM
  • Hi,

    Thanks for the reply. I don't think I need quorum because I am not using a witness server. The first error message I get after I click on start mirroring on the wizard is this
    "The server network address "TCP://NDIVERSBK:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"
    I can configure security without any problem and it creates the end points on both the servers also but I get an error when I click on start mirroring.

    When I click OK and clcik on start mirroing again I get this error message "Neither the partner nor the witness server instance for database "shop" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)".

    Both servers are using the same service account and have the same sa and administrator passwords. They are on the same network and workgroup but not on a a domain.

    The steps I took to setup mirroring was:

    1. Setup the SQL service to run on windows account with administrator permissions and had the same account password on both the servers.
    2. Added -T1400 as starup parameter on both servers and restarted them.
    3. Backed up the database on the principal server and restored the it on the mirror server using no recovery mode.
    4. Backed up a transaction log on primary server and restored it again on mirror in no-recovery mode.
    5. Started mirroing wizard using on prinicipal server and cliked the configure secuirty button.
    6. Selected only principal and mirror server and no witness server.
    7. Set the principal and mirror server instance using the same endpoint name and listener port and select encrypt data.
    8. Left the service accounts principal and mirror blank
    9. It created the end points on both servers and we checked to make sure they are there.
    10 Clicked on start mirroring and that’s when we got the error that it cannot find mirror server first time.

    When we clicked OK and clicked on start mirroring again we got the second error and always shows up and is mentioned above.

    We also tried using the sql queries and got the same error.
    Tuesday, January 31, 2006 7:20 PM
  • Excellent repro steps!

    OK, 1418 was one of the most mentioned errors that database mirroring had, and so I went and created a specific Books OnLine topic for it.  If you search for "troubleshooting database mirroring" in BOL, then you should find the section.

    If you have configured SQL to use the SA accounts (like you state you do), then my next guess is that the port is blocked by a firewall. Basically, the first error message (1418)means the principal cannot access the mirror. Then you connect to the mirror, but since it has never talked with the principal, the mirror does not know what state the session is in, and so it returns the 1431 error.

    To verify that the endpoint is accessable, open up a command windows and type "telnet NDIVERSBK 5022". If the endpoint is working properly, then the screen should clear, and you should be able to make 16 keystrokes before the connection is terminated. If you can't do that, the port is probably blocked. If you can do that, we'll start looking in the errorlog files.

    Thanks,

    Mark

    Tuesday, January 31, 2006 9:00 PM
  • There is no firewall between the servers and I tried opening up a command windows and type "telnet NDIVERSBK 5022". The screen cleared, and I was able to make 16 keystrokes before the connection got terminated.

    Thanks for your help.
    Tuesday, January 31, 2006 9:33 PM
  • OK, the endpoints are waiting for connections...

    Hmmmm...

    If the endpoints are configured properly, and you can telnet into the endpoint from each server to the other server, then my guess is that the problem is security.

    Could you send me the errorlogs from both servers that have the errors recorded in log?

    Normally the errorlogs are in "Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG" by default.

    mwistrom@microsoft.com

    Thanks,

    Mark

    Tuesday, January 31, 2006 9:42 PM
  • I have emailed the log file to the email address you provided. Thanks.
    Tuesday, January 31, 2006 10:35 PM
  • Can this be a problem for mirroring:

    The database owner is sa and I am logging in as sa in Management Studio. But when I click on Database diagrams I get the following error

    "Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."
    Tuesday, January 31, 2006 10:41 PM
  • I also already tried FQDN using this line
    Alter database db set partner = 'NDIVERSBK.dds.com:5022' and this also does not work and I still get the same error.
    When I try to telnet to NDIVERSBK.dds.com at port 5022 the blank black screen stay only for a second and then goes without any keystrokes.
     
    Wednesday, February 1, 2006 3:40 AM
  • Don't forget that the syntax is 'tcp://ndiversbk.dds.com:5022'

    If the telnet kicks you out after 1 second, then there is a problem with security and the endpoint.  It should let you type 16 keystrokes and then kick you out.

    Did you specify the 1400 trace flag?

    Thursday, February 23, 2006 12:35 AM
  • Just try

     

    drop endpoint [mirroring]

     

    in both server and restart the process

     

    [];s

    Marcelo Glauco

     

    Tuesday, December 12, 2006 12:06 PM
  •  

    Hi.

     

    Try it in the Mirror database

     

    ALTER DATABASE <MirrorDatabaseName> SET PARTNER OFF;

     

    After this, you can configure Database Mirroring from SSMS normally.

     

    []´z

     

     

    Monday, October 6, 2008 3:26 PM
  • I had a simular problems and it would appear the mirror server didn't drop the partner when I removed mirroring, setting partner to off, sort it.
    Wednesday, May 27, 2009 2:30 PM
  • I setup sql 2008 mirroring for the first time today and am also getting the 1431 error when I try to establish the mirror. Reading this thread convinces me there is something "subtle" about miroring which makes me mistrust the implementation. (I next plan to look at log shippng and hope it is more transparent.) Barkingdog
    • Proposed as answer by northface Wednesday, August 18, 2010 10:01 PM
    Sunday, August 9, 2009 7:58 AM
  • Hi,

    I have the same problem.

    I am not able to ping the fqn.

    But I am able to ping the short server name.

    This tells me the fqn is not listed on the DNS server.

    You need to use fqn for mirroring.

    Wednesday, August 18, 2010 10:03 PM
  • Hi,

    I have followed exactly the same steps mentioned by you but I am getting the below error. Can any one help me out in this?

    Neither the partner nor the witness server instance for database "DB NAME" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)

    Is there any thing I am missing? I have tried the steps multiple time.

    Thanks,

    Chaks

    Tuesday, September 14, 2010 5:03 PM
  • Neither the partner nor the witness server instance for database "DB NAME" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)

     

    I just ran across this problem today and in my case I was forgetting to backup and restore the transaction log. DOH. 

    I have found that the error messages relating to Mirroring are not useful.

    So in case anyone else runs into this problem hopefully that will help you out.

     

    Tuesday, November 16, 2010 7:13 PM
  • I just experienced this same issue.  I simply closed the wizard, went back in and everything worked fine.
    • Proposed as answer by MattNZKiwi_GER Thursday, October 20, 2011 12:44 PM
    • Unproposed as answer by MattNZKiwi_GER Thursday, October 20, 2011 12:44 PM
    Thursday, October 6, 2011 8:52 PM
  • Hi

    For those of you who have no problems when issuing the TELNET command but still get the 1431 error, ensure you have actually STARTED the End Points.

      e.g.

          ALTER ENDPOINT [myendpoint] STATE = STARTED

    Hope this helps someone :-)

    Cheers

    Matt

     

     


    Thursday, October 20, 2011 12:50 PM