March 10, 2015 at 5:28 pm
OK, I know SQL Mirror will be going away in favor of Always On.
But we have had a DB mirror for years on SQL 2005 and 2008 and R2.
Now, I setup a test set of 2 SQL 2012 servers that are NOT joined to a domain (there is no DC/GC in that secured segment), used local accounts on each server and service for the SQL service and SQL Agent and the mirror worked fine.
Next I setup 2 SQL servers for production, same build as test, but for some really goofy reason, the mirror will not establish itself. I get the error...
"The server network address "TCP://<servername>:<portnumber>" can not be reached or does not exist." with the error: 1418
The SQL Logs show this...
"Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.'. [CLIENT: 192.168.xxx.xxx]"
Review of my setup:
sql1:
Is not in a domain and is in a WORKGROUP.
SQL Service runs as user sql1\sqlservice (passwords are the same)
SQL Agent service runs as user sql1\sqlserviceagent (passwords are the same)
Both accounts are in the local Administrators group.
Both accounts are sysadmin in the running SQL default instance.
Local hosts file has the principle and mirrors IP and NetBIOS name and fqdn.
sql2:
Is not in a domain and is in a WORKGROUP.
SQL Service runs as user sql2\sqlservice (passwords are the same)
SQL Agent service runs as user sql2\sqlserviceagent (passwords are the same)
Both accounts are in the local Administrators group.
Both accounts are sysadmin in the running SQL default instance.
Local hosts file has the principle and mirrors IP and NetBIOS name and fqdn.
NOTE:
If you haven't noticed, all the accounts passwords are all the same.
I Ran the below t-sql on each SQL server...
create endpoint [LookingGlass]
State = Started
as tcp (listener_port = 7028, listener_ip = all)
For database_mirroring (role=partner, authentication=windows negotiate,encryption=required algorithm rc4);
GO
running this...
Select * from sys.tcp_endpoints
shows that the endpoints are up "STARTED".
Both servers appear to be listening on port 7028 because when I telnet to them it seems to connect and my cursor in the command prompt stays at the top left corner? Is that a proper test???
Both servers have the windows firewall turned off.
No Antivirus.
Both servers are on the same segment and no firewall port blocking.
I've tried establishing mirror via IP only but I get the same problem.
I even dropped the endpoints and recreated without encryption, same problem.
I also tried using another port 6144, still no go.
I've followed the below instructions troubleshooting but still no go:
Anyone have any ideas?
March 11, 2015 at 4:20 am
Well, the correct way is to use certificated logins and not pass through authentication.
Having said that, if you wish to continue as you are, try creating FQDNs in the hosts file for each node and use those in the mirror setup instead of the IP address
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 11, 2015 at 6:10 am
I can GUARANTEE you that I will still be supporting clients using database mirroring 10 years from now - probably more like 15. Always On is ENTERPRISE EDITION ONLY, and the VAST majority of installed SQL Server in the world is NOT that edition. There is absolutely no reason to not be setting up new mirroring today and in the coming years if it fits your HA/DR needs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 11, 2015 at 10:45 am
My post already states the....
"Local hosts file has the principle and mirrors IP and NetBIOS name and fqdn.".
March 11, 2015 at 10:47 am
I wholeheartedly agree!!!!!
March 12, 2015 at 6:58 am
TheSQLGuru (3/11/2015)
Always On is ENTERPRISE EDITION ONLY, and the VAST majority of installed SQL Server in the world is NOT that edition.
AlwaysOn Failover Cluster Instances are not restricted to higher levels of the product, they are available with a 2 node restriction in Standard edition.
Whilst AlwaysOn Availability groups are an Enterprise only feature they do allow huge savings on storage subsystems, potentially huger than the cost of the SQL Server licences. Without the need to rely on cross site replicated storage and aside the reduced cost, the system is both easier to implement and maintain.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply