For the last week or so I have struggled to get database mirroring with failover (failover being a witness machine) working as advertised from Microsoft. After struggling with error messages like this for the past few days:
2007-07-06 15:50:44.62 spid31s Error: 1474, Severity: 16, State: 1.
2007-07-06 15:50:44.62 spid31s Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://192.168.0.8:7042'.
2007-07-06 15:55:00.07 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.0.7]
2007-07-06 15:55:00.09 Logon Error: 17806, Severity: 20, State: 2.
2007-07-06 15:55:00.09 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.0.7]
2007-07-06 15:55:00.09 Logon Error: 18452, Severity: 14, State: 1.
2007-07-06 15:57:24.26 spid26s Error: 1474, Severity: 16, State: 1.
2007-07-06 15:57:24.26 spid26s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://192.168.0.8:7024'.
I finally broke down and called Microsoft and opened up a new case. After 10 hours (yes 10) and 4 levels of Microsoft tech support, there was still no resolution (or even a clue) from Microsoft and we gave up for the day. We both decided that we would recreate this environment in respective testing scenarios. Since our environment was hosting production databases the changes we could make were limited and setting up a testing environment seemed like the logical thing to do.
After a few more hours of rebuilding and service packing I had a mirror of our production instance up and receiving the same errors. On a hunch and a post by this guy we decided to setup DNS entries for all of our machines and setup mirroring using fully qualified domain names. Long story short, IP’s or any kind of aliases do not work for SQL mirroring with a witness, though it does work for just mirroring. You must use <machinename>.<dnssuffix> in order for mirroring with failover to work.
Here is the writeup and soon to be knowledge base article (will be KB940254) from Microsoft:
ACTION: Trying to add a witness to and existing database mirroring configuration that was configured with certificates in a workgroup that uses host files and dns.
ALTER DATABASE [database_name] SET witness = ‘TCP://www02:7024’;
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://www02:7024’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
CAUSE: SQL Server database mirroring with a witness requires a FQDN inorder to resolve the machine name.
Create DNS entries for all internal IP addresses. In the IP address configuration under the DNS tab change “Append primary and connection specific DNS suffixes” to “Append these DNS suffixes (in order):” and enter the appropriate DNS suffix. Click “OK” to the remaining dialogs.
After <machinename>.<suffix> begins to resolve from your DNS server execute the “ALTER DATABASE” commands with the appropriate FQDN of each machine.