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]
Should have also mentioned:
3.5. Enabled the -T 1400 flag in the Configuration Manager on all three machines.
Cheers,
Mike
|||
The problem is security. If you want to solve the problem quickly, I would add the machines to a domain (assuming you have a domain), and make sure that all the SQL processes are running as the same user which is the SA on all machines everything will be fine.
The problem is that although you run as SA on server A, that user is a nobody on server B. Server A tries to login using the account that it runs as; as far as B is concerned, that user is anonymous.
Additional points:
1. You mention that you telnet to 5020, but it looks like you setup the endpoints to listen in on 5022.
2. You could use certificates (search BOL) to setup mirroring for servers that are not in trusted domains.
3. You may also have to backup and restore the transaction log once.
4. The T-SQL is very easy to do. See the examples in BOL.
Thanks,
Mark
|||Thanks for your reply.The telnet port was a typo.
My understanding is now that the mirroring security wizard only works when the machines are on the same domain. If they are on different domains then it is necessary to create certificates using T-SQL to enable trusted endpoint comminication. Is this correct?
I have attempted, so far unsuccessfuly to create certificates in this manner - please see my subsequent post. If there really is no solution to this problem then I guess we'll have to go down the route of putting both servers in the same domain. However, we are trying to write a self-contained application and it would be best if we didn't have to worry about introducing a domain controller into the setup.
Any advice from a guru such as yourself would be much appreciated.
Mike
|||
SP1 Community Technology Preview release
In this version it should be working without setting the flag "-t 1400“.
I have been tested the mirroring since the Release was released, but nothing work
Can anybody help me?
After I agree "Start Mirroring" always the same failure is pop up!
Translation from German "for database XXXX is morroring not configured”.
Can anybody reproduce this ?
|||What do you mean "nothing work"?
Are you saying that you cannot setup database mirroring?
I would recommend looking in BOL in the section on troubleshooting database mirroring setup.
It is more than likely a security issue.
Thanks,
Mark
|||If you can, set mirroring up using a domain admin account on all machines, If that works then it will soon tell you have permissions problems when running under other accounts.
I found it easier to set mirroring up in T-SQL than the GUI, as it provides all the code for you anyway and you get a bit more idea on what's actually going on
|||Ensure that windows services account through which sql server is running is added in "access this computer from network" under security policy.
This should solve the problem
Manohar Kulkarni
SQLDBA
No comments:
Post a Comment