Help getting mirroring working
I've having some trouble getting DB mirroring set up in Sql Server 2005. Perhaps someone can help, as I've read everything I can find on the web and Google groups about this but still can't solve it.
Here are the steps I have taken:
1. Installed a fresh Windows 2003 on box A (the primary) and a fresh Windows XP on box B (the mirror).
2. Run Windows update on both.
3. Installed Sql Server Enterprise on box A and box B, and also on my own machine box W (the witness). All good so far, I can connect to all machines, all machines can ping each other, all machines can telnet to each other on port 5020 (the 16 keystroke thing), and I can create databases on all machines. No boxes are on a domain so all are using the sa login.
4. Created my test database on box A - just a single table for now.
5. Backed up the DB (recovery set to FULL) and restored on box B - all working.
6. Ran the Configure Security wizard on box A.
7. Elected to configure a witness, selected defaults for everything (port 5022, encryption on, endpoint names "Mirroring", sa login for all three machines).
8. Left blank the Service Accounts for all three boxes (it says: "Leave the textboxes empty if all instances use the same account [they do], the accounts are non-domain accounts [they are non-domain accounts], or the accounts are in untusted domains [not on a domain, so this should be correct, right?]").
9. Finished the wizard, everything successful.
10. Ensured the server network addresses are correct - TCP://bill-primary.ourdomainname.com:5022 etc.
11. Clicked the Start Mirroring button.
12. Receive the dreaded 1418 error: "The server network address "TCP://bill-primary.ourdomainname.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"
13. AGH!
The latest entries from the primary's error log are shown below (IP
10.152.58.243 is the mirror).The only thing I haven't tried is setting up manually through SQL statements, but this looks extremely messy and would basically just be trial and error (so what's new, you ask?).
Any help appreciated.
--
Mike
Error log from primary machine (10.152.58.242):
2006-03-16 13:57:00.26 spid53 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 13:57:02.28 spid53 Server is listening on [ 'any' <ipv4> 5022].
2006-03-16 13:57:02.28 spid53 The Database Mirroring protocol transport is now listening for connections.
2006-03-16 13:57:29.14 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:29.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:29.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:30.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:32.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:35.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:37.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:40.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:42.84 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:57:45.34 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:11.25 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:13.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:14.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:15.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:17.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:19.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:22.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:24.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:27.23 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 13:59:29.73 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:00:51.12 spid21s Error: 9642, Severity: 16, State: 3.
2006-03-16 14:00:51.12 spid21s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 14:09:00.04 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:00.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:00.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:01.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:03.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:06.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:08.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:11.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:13.81 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 14:09:16.31 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:14:51.70 spid25s Error: 1474, Severity: 16, State: 1.
2006-03-16 15:14:51.70 spid25s Database mirroring connection error 4 'An error occurred while receiving data: '10054(error not found)'.' for 'TCP://bill-secondary.globalgossip.net:5022'.
2006-03-16 15:15:09.42 spid23s Error: 1443, Severity: 16, State: 2.
2006-03-16 15:15:09.42 spid23s Database mirroring has been terminated for database 'failover_test'. This is an informational message only. No user action is required.
2006-03-16 15:27:56.39 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:56.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:57.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:27:58.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:00.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:02.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:05.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:07.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:10.12 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:28:12.62 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:31:26.46 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:26.46 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:31:37.18 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:37.18 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:31:56.76 spid19s Error: 9642, Severity: 16, State: 3.
2006-03-16 15:31:56.76 spid19s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474 State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-03-16 15:48:52.42 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:52.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:53.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:54.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:56.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:48:58.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:01.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:03.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:06.17 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 15:49:08.67 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:08:41.64 spid56 The Database Mirroring protocol transport has stopped listening for connections.
2006-03-16 16:08:43.64 spid56 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 16:31:05.75 spid51 The Database Mirroring protocol transport is disabled or not configured.
2006-03-16 16:31:07.75 spid51 Server is listening on [ 'any' <ipv4> 5022].
2006-03-16 16:31:07.75 spid51 The Database Mirroring protocol transport is now listening for connections.
2006-03-16 16:31:14.14 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:14.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:14.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:15.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:17.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:20.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:22.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:25.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:27.90 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:31:30.40 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:17.00 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:19.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:19.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:20.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:22.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:25.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:27.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:30.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:32.98 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]
2006-03-16 16:34:35.48 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(error not found). State 67.'. [CLIENT: 10.152.58.243]

