Basic Mirroring Setup Help

After going through the Enterprise Manage mirroring setup, I get the error message ...

The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

I have three instances of SQL Server 2005 Enterprise Edtion
MSSQLSERVER (primary) endpoint 5022
MSSQLSERVER (partner) endpoint 5023
MSSQLSERVER (witness) endpoint 5024

I have enabled TCP\IP and named pipes on all three protocols.
In surface configuration I have enabled everything.
Each instance has the -T1400 flag set.
Each instance is running under mixed security.
Each sql Server and sql agent instance runs exactly in the same domain account.
The machine has been rebooted.
The following tests all pass

telnet MYMACH.mynet.net 5022
telnet MYMACH.mynet.net 5023
telnet MYMACH.mynet.net 5024

This problem happens both with Enterprise Edition and Developer Edition.

Any ideas?

AIM

Hi,

Thank you for providing informtaion into the problem that occurred. It is very difficult to diagnose some of these problems with so little information.

My first guess is security, but lets start from the top.

I assume that you have all three instances on the same machine, correct? That should work just fine. And you have setup the endpoints to listen on the different ports, which is correct. Now you said that you can telnet to the different ports, I'm assuming that you did it from the same machine. If you can try the telnet test again from a different machine that would be good, but not neccessary. Should be fine.

If this is the recent SP1 CTP build, http://www.microsoft.com/sql/ctp_sp1.mspx, then you do not need the 1400 trace flag (it is just a noop now). Should be fine.

Now the account that the instances are running under? What type of account is it? Can we make it a Sys Admin account on all three instances? Look in the errorlog for the instances. If you see anything along the lines of "denied access" in there?

Also, one last thing; you state that the error message is:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

If you want the principal to listen on the 5022 port, then you should have gotten an error saying "... MYMACH.mynet.net:5023..." Can you go back and check your setup?

Thanks,

Mark

|||

O.K.

FIRST

All three instances are running under a Windows Account that is an Adminstrator on the machine. This has always been this way.

SECOND

I said:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

I meant to say:

"The server network address "TCP://MYMACH.mynet.net:5023" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

O.K. WHAT NEXT SHOULD I TRY?

THANKS.

AIMDBA

|||

Can you verify that the Windows account that the instances are running as are "SQL Server System Administrators?" Thanks.

If you could attach the error logs during the time that you tried to setup database mirroring, that would help a lot. Grab the error logs from both the principal and mirror instance. By default these are located at:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

Get the "errorlog" that contains the time at which you issued the start mirroring command.

At this point, I see two approaches.

1. posting the errorlogs so that I can look at them.

2. trying to setup mirroring just using t-sql. There are some examples in BOL.

Let me know how things are going.

Thanks,

Mark

|||

I'm getting the same error here. Below is my sql server error (machines are on different IP's) and below this is my error log.

An error occurred while starting mirroring.

ADDITIONAL INFORMATION:

Alter failed for Database 'UOP_GSO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2040.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://BOGEYWRKSTATION.svc.nt.edu: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. (Microsoft SQL Server, Error: 1418)

2006-04-12 16:48:01.75 spid51 Using 'xpstar90.dll' version '2005.90.2040' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2006-04-12 16:48:02.55 spid12s The Service Broker protocol transport is disabled or not configured.
2006-04-12 16:48:02.57 spid12s Server is listening on [ 'any' <ipv4> 5022].
2006-04-12 16:48:02.57 spid12s The Database Mirroring protocol transport is now listening for connections.
2006-04-12 16:48:02.77 spid12s Service Broker manager has started.
2006-04-12 16:48:04.49 spid5s Recovery is complete. This is an informational message only. No user action is required.

|||

First thing to do is to go to the "Troubleshooting Database Mirroring Setup" topic in BOL. It was designed to solve this first basic question of "why do I get error 1418?"

The snippette of the errorlog that you posted indicates that the database mirroring endpoint was started at one point, but that doesn't tell me much.

If you could post the part of the errorlog around the time you got the 1418 error, that would tell me much more. It is generally one of two things.

1. Network error where we tried to open the port and for some reason the network said we failed.

2. Security problem where we connected to the port but for some reason, our security credentials were not correct.

Thanks,

Mark

|||

Got it up and running. Had nothing to do with the specific error that I was receiving but I had not restored the failover correctly.

thanks

|||

Bogey1,

What did method did you use, (what did you do) to do the correct failover restore?

AIMDBA

|||Two seperate machines on the network, but still in the same domain. Running domain accounts for sql server. I did a backup of my primary and applied that to my mirror, but I kept getting the error my mirror is not in sync. So I just restored my backup to my primary and started the mirroring. Works quite well.|||

So, to initialize the mirror, you must do a full backup of the database on the princpal, restore the full backup to the mirror with NO_RECOVERY, and then usually (there are some cases where you do not have to do another backup or restore, but they are not normal operating scenarios) take a log backup and restore it to the mirror with NO_RECOVERY. You must also restore any other log backups that are taken on the principal to the mirror.

Thanks,

Mark

|||Rather that starting an entirely new thread, I figured I would post my question here since my issues are stemming from the next point of the mirror creation process.

Basically I take my full principal backup and then run the restore with no recovery to the mirror, once the restore is underway and then complete, according to my sql logs, I have the DBname (Restoring) indicator in management studio. Low and behold this message can be there for countless hours and in this scenario the database is unalvailable for use. Any ideas on what could be causing this?

Thanks
|||

This is correct behavior. Doing any type of restore with NO_RECOVERY leaves the database in an unrecovered state.

Continue with setting up database mirroring.

Thanks,

Mark

|||

I'm facing the same issue with error 1418. I have 3 VM servers ( hosted in the same host machine) that host each an instance of SQL server. I'm using a domain account with admin right on each server to run SQL server and my database mirroring. I have granted System admin right to that account and full right on all sql folder. Telnet test is successfull. after the mirroring failed, there is no failure audit in the security audit log. Everything seems to be setup properly. I have added the service account in all group created by SQL Server during the installation. But I'm still receiving this error.

In another network, where the servers are not VM Ware server but real physical machine I do not have any problem The mirroring is working fine. But with the VMWare server , I have the problem. Is this problem linked to the virtual machine architecture? Why I don't have the problem with physical machine?