Can't get database mirror witness to connect

Question

I have successfully configured a principal and mirror with SQL Server 2008 and Windows Server 2008 R2 64bit. However, when I attempt to add a witness (SQL Server 2005 and Windows Server 2003 32bit) to the mix, I get the following errors in the event log of the primary: EventID 1456 "The ALTER DATABASE command could not be sent to the remote server instance 'TCP://SERVERNAME.DOMAIN:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again." and EventID 1474 "Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://SERVERNAME.DOMAIN:5022'."

I can see a temporary established connection on port 5022 via netstat and SQL Profiler and then these errors.

Does anyone know why this is happening?

Answer 1

Does your partner name match exactly the FQDN of the mirror? The behavior you describe can happen when the mirror (or principal for the matter) knows himself (the return of GetComputerNameEx)by a different name than the name used partner in the ALTER DATABASE ... SET PARTNER = 'tcp://partnername:...';. Consider this scenario:

• the principal uses the name Foo for the mirror. Foo is resolved through the hosts file to the correct IP of the mirror
• the mirror knows himself as Bar.
• when a witness is added the principal will as 'what is the status of Foo, do you see him?'. The mirror will report to witness 'hello, I'm Bar'. So the witness will report back to the principal 'I don't know where Foo is' and this can end up breaking the mirroring session.

There are more details and nuances. The gist of the story is: always use FQDN names properly resolved through DNS when setting up mirroring.