"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.
Can you list the steps you followed when setting up mirroring?
Thanks,
Matt
|||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
|||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.|||
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
|||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.
|||
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
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."|||
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.
|||
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?
|||Just try
drop endpoint [mirroring]
in both server and restart the process
[];s
Marcelo Glauco
No comments:
Post a Comment