unable to add witness to DB mirror error 1456

Question

I can establish the mirror with the principal and mirror but cannot add the witness (error 1456) all machines are SQL Server 2005 standard edition service pack 2 principal and mirror are windows server 2003 and witness is windows xp

Answer 1

You cannot use a witness with Standard Edition, you need to have Enterprise Edition to use one.

Answer 2

Not according to the SQL server 2005 web site, and we have had it working before we installed the service packs

Answer 3

But automatic failover and automatic client redirect are only available in Enterprise Edition, so there is no point in having a witness in Std Edition.

 

http://www.awprofessional.com/articles/article.asp?p=457500&seqNum=8&rl=1

Answer 4

I believe that is wrong. Standard edition can provide full safety

 

High Availability “Always On” Comparison

Feature	SQL Server 2005 Standard Edition	SQL Server 2005 Enterprise Edition
Failover clustering	2-nodes Only	Up to 9 nodes
Database mirroring	Safety FULL Only	All Modes

http://www.microsoft.com/sql/editions/enterprise/comparison.mspx.

 

Database Mirroring Feature	Enterprise Edition	Developer Edition	Standard Edition	Workgroup Edition	SQL Express
Partner (Principal or Mirror)	ü	ü	ü
Witness	ü	ü	ü	ü	ü
Safety = FULL	ü	ü	ü
Safety = OFF	ü	ü
Available during UNDO after failover	ü	ü	ü
Parallel REDO	ü	ü

Answer 5

Now this is what I have been led to beleive all along but since we have upgraded to service pack 2 we have not been able to get the witness to work successfully. 

Answer 6

I believe that the table markboyle posted means that Std Ed can be used as a witness, but not that it supports the use of a witness. High availability mode refers to the fact that the principal and the mirror are kept in sync (like 2-phase commit), but it doesn't necessarily include automatic failover. I still believe the use of a witness is EE only, which is why you're getting the errors you are.

 

I'd like to be proved wrong!

Answer 7

Ok I have got it working with MS SQL Server 2005 standard edition service pack 1 going to install service pack 2 on all instances and check again.

Answer 8

Updated to MS SQL 2005 service pack 2 all still working, may have just proved you wrong??

Answer 9

You definitely can use Standard edition with automatic failover, as per the features lists you can't run it with safety off though which means you can't really sue it except where you have two servers next to each other because they must two-phase commit rather than log-ship and assume all is according to plan.  This is so you can't do multi-site DR with Standard edition it would appear.

I'm having the same problem though, I've got a principal and mirror running Standard SP2 and another one running SQL 2K5 Express SP2 as the witness.  I can connect fine to the Express server but I can't stop this error.  Without it the mirroring is a bit scary as the principal DB will go offline if we lose the mirror which is rather the opposite of what we're trying to achieve!  Replication would be a seemingly better solution at the moment

Any ideas how to get it fixed?
thanks
Pete

Answer 10

I also have this error continue to come up everytime I try to invoke the (SQL 2K5 Express) witness to my (SQL 2K5 Standard) mirrored servers.  Has there been a fix for this yet, Pete?

Answer 11

We decided to give up on using express edition as the witness, and elected to use standard.

We had to do a clean install of the operating systems and SQL 2005 and manually install the service packs instead of using automatic updates, this resolved the issue we where having. we may try a test with express in the future but for now this is working well.

Answer 12

I just want to be the second person to let you guys know that use of witness for automatic failover is available in Stanadard edition as well.

 

Answer 13

I can't exactly remember how we got it fixed - I think we basically re-installed the witness and all was well.  It was all to do with the users I think and it not creating the endpoints properly.  Basically if you bin off the whole mirroring setup a re-set it up from scratch then it works fine.
People only find it works with Standard because really it works when they re-set it up - it would have worked fine on Express to (as it does for us).
Pete

Answer 14

 

http://support.microsoft.com/kb/930283

Answer 15

I'm actually having a similar problem - we've been able to establish the mirror (SQL 2005 Enterprise Edition) but are unable to add the witness (SQL 2005 Standard).  We had this working before but due to a database upgrade we had to break the mirror.  We keep getting error 1456:

 

Error: 1456, Severity: 16, State: 3.
2008-07-01 08:35:44.07 spid55      The ALTER DATABASE command could not be sent to the remote server instance 'tcp://witnessserver.ourcompany:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

 

I'm able to telnet to the "witness" server from the principal on port 5022 so the endpoints appear to work.  Any suggestions?  I know this may be a dead thread, but thought I'd try anyway.  Thanks.

Answer 16

Try trouble shooting at the end of this post

 

http://forums.microsoft.com/forums/ShowPost.aspx?PostID=3504051&SiteID=1

Answer 17

Also try restarting SQL Server on the Witness, I have seen many:

 

The server network address "TCP://X:X" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

 

Errors, which have worked after a restart.

Answer 18

I also had trouble creating a mirror, and received error 1418 numerous times,I solved it using the same local user (ie sqlusr) on the principal, host and wittnes, and configure the SQL Service and the SQL agent service to run under this account.

---

CCA!

Answer 19

I've encountered both 1418 and 1456 and fixed both the same way. 
First I added the sql server service account as a login on the witness.
Then I issued the grant to connect manually like the following example.

 

GRANT CONNECT ON ENDPOINT::Mirroring TO [SomeDomain\SomeLogin];

Finally I was able to mirror as expected.  I still haven't figured out why I had to do this though.

---

Joe Moyle

Answer 20

I found I had to set the witness SQL server service to run under the same domain account as the two mirroring database service accounts and it worked fine