March 18, 2008 at 10:09 pm
I have tried setting up mirroring through the security configuration wizard. I am getting the following error when I try to setup the mirror. Any Ideas?
===================================
The server network address "TCP://SrvPri.domain.local:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
------------------------------
Server Name: SRVSEC
Error Number: 1418
Severity: 16
State: 1
Line Number: 1
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
===================================
An error occurred while starting mirroring.
===================================
Alter failed for Database 'myDB'. (Microsoft.SqlServer.Smo)
------------------------------
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
at Microsoft.SqlServer.Management.Smo.Database.Alter()
at Microsoft.SqlServer.Management.SqlManagerUI.DBPropMirroring.StartMirroring(Object sender, EventArgs e)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
-- Francisco
March 18, 2008 at 11:45 pm
I am following the steps here:
http://msdn2.microsoft.com/en-us/library/ms189127.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337361.aspx
I setup the endpoint and confirmed that the server is using my specified port, but I still get the dreaded error when I use the following to setup the Partner:
ALTER DATABASE myDB
SET PARTNER = 'TCP://10.1.1.80:7022'
Error:
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://10.1.1.80:7022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
-- Francisco
March 19, 2008 at 9:46 am
I ran a netstat to verify that the primary server IS listening on proposed port. that is verified. but the error is persistent?
-- Francisco
March 19, 2008 at 10:17 am
Francisco:
I am not positive about this, however I don't think that you can use the Ip address to qualify the path, I believe that you have to use a DNS name.
i.e. TCP://mysqlserver.somcompany.org:5022 as an example.
Additionally when you do this you need to make sure that the path can be seen from both sides. What I mean by that is that you need to make sure you can ping the partner from the primary, the primary from the partner. I am just getting working through these issues myself so my explanations may not be dead on... so bear with me.
If you can not ping from both sides, you need to add a routing table entry from what I undestand on the servers.
When I ran into these issues I discovered that when I looked at the partner server the database that I was trying to mirror went from Restoring state to Recovery, even though the primary server responded back by telling me that it couldn't find the end point. Make sure that you check the state of the partner database, basically you could be chasing your tail trying to figure out where the "real" issue is. Log into your partner server instance and refresh the view, make sure that the partner server db instance is indeed not in Recovery mode. If it is, you then know that the end points were established and that it is really a communication channel issue... i.e. you need to use the DNS name.
I hope that helps a little... this is a tough one to get working right. 🙂
March 20, 2008 at 4:29 am
I am pretty sure that it doesn't matter if you use DNS name or IP address. Obviously the Port Number is vital, and remember that a Ping test will only check that IP routing to the remote server is working; it will not verify that a particular Port is open. A variety of switches/firewalls along the way could block the Port, either for incoming and/or outgoing traffic.
My suggestion - run through the "Configure Database Mirroring Security Wizard" (I typically run this on the Principal, but you don't have to), and when you get to the Mirror and Witness pages, connect to the relevant instance and check that the Listener Port and Endpoint name are automatically populated. That will tell you if the Endpoints are correctly configured and available.
March 20, 2008 at 5:36 am
I'd suggest the same as AndyD, by using the Security Configuration Wizard the endpoints will be setup for you automatically. Each server can only have only one mirroring endpoint so you might have to remove your existing ones.
Not only will the Security Configuration Wizard setup the endpoint on each server, it will create the SQL logins on each of one as well. In the Security Configuration Wizard when prompted for the usernames on each of the servers make sure to include the domain name, for example MyDomain\SQLServicePrimary.
However, if your servers are in a workgroup instead of a domain you might want to look at using certificates for mirroring: http://msdn2.microsoft.com/en-us/library/ms191140.aspx
March 20, 2008 at 5:48 am
I would also suggest you verify both versions of SQL are the same. Setting up mirroring using T-SQL doesn't tell you the error if you try this. You have to use the Mirroring Wizard. Also, to do async mirroring, you need to use Enterprise edition. Hope this helps.
March 20, 2008 at 8:05 am
I finally figured out my error using this blog. While I restored the db w/ the NORECOVERY option, I wasn't doing the same for the transaction log. I followed this blog's instructions and finally started up the mirror.
Thank you very much for your help and pointers in the right direction.
-- Francisco
September 9, 2008 at 11:37 am
Francisco,
Now that you've got the mirroring session up and running I'm hoping you'd help me verify why mine's not. I set up db mirroring with 2 sql enterprise db, one instance on each sql database, on a high safety w/o auto failover also known as high protection setup. The sql directory structure & hardware are similar on both.
Steps taken:
1. Create endpoint on Principal then Mirror.
2. Backup Principal, restore on Mirror: full, differential, transaction logs & tail log. Restore WITH NORECOVERY.
3. Set up partnership: onn Mirror then on Principal.
4. Set the safety level on the Principal to FULL.
Servers are in workgroup config. Can connect to internet on either but ping times out on each of these servers.
Thanks for any suggestions you may have.
September 9, 2008 at 2:52 pm
tdb92843 (9/9/2008)
Francisco,Servers are in workgroup config. Can connect to internet on either but ping times out on each of these servers.
tdb92843,
Are you saying that you cannot ping server a from server b and vise versa or you simply can ping out to the internet?
-- Francisco
September 16, 2008 at 3:05 pm
Thanks for the reply Francisco.
Am able to ping from the principal to the mirror & vice versa. Both are using the same endpoint and Sql Server is listening to port 1433.
Actually, item 4 was done 1st: set safety mode of principal to full.
September 17, 2008 at 1:57 am
Ideally, you want to test that the particular port the Endpoint is mapped to, is available from the other server. Easiest way to test is to telnet to the port:
telnet
eg. telnet MySQLServer 5022
Do this from each server in the mirror, to the relevant endpoint ports on the other servers. You could also replace with the IP Address to verify DNS is all set up and working nicely.
Andy
July 29, 2009 at 7:20 am
I'm not exaggerating when I say I've spent hours looking into this problem and running through all the really helpful information on the net (people are great!) but in the end it was the smallest thing. Basically, as soon as I changed the statement on the principal server from
"alter database mytest set partner = 'tcp://myserver.mydomain.com:7022"
to
"alter database mytest set partner = tcp://myserver.mydomain.local:7022"
it worked fine. I have no idea about domains so I don't understand what .com and .local do but you might like to try it. I've gone grey on this one.
Thanks to everyone though for very helpful suggestions
July 30, 2009 at 1:29 am
Can I suggest you read up a little on DNS? As per my suggestion (above), if you had replaced the Server Name with the IP Address, you would have instantly solved your problem.
As for the "difference" between .com and .local - well, they are simply entirely different Top Level Domains. I would guess that within your corporate network, your Network Guys/Active Directory Guys have created a non-internet-routable private network, and given it the TLD of .local; this is quite normal.
Andy
July 30, 2009 at 1:46 am
Hi Andy
Thanks for that - unfortunately I don't work in the team that support DNS and they were particularly unhelpful in explanations but never mind - got there in the end!
Thanks again
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply