MS SQL 2005...working against me!

Happy Holidays Gents,

I am just getting ready to head on a vacation myself, but can't get out of here until I can complete the following task...

I need to set up three machines for a test of the mirroring abilities of SQL server. This setup includes three machines; principal, mirror, witness....

All was well and good until I tried to mirror from one database to the other...

When everthing seems to be set up correctly and I even get the message that the servers are green light to start I always end up with this message...

-Alter failed for Database "testdatabase". (Microsoft.SqlServer.Smo)
-An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
-The server network address "TCP://testmirror.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. (Microsoft SQL Server, Error: 1418)

This makes no sense since I can ping, telnet, and know for sure 5022 is open and not in use. The firewall is off, the tcp/ip is enabled and named pipes are enabled. Everything is set just like the instructions say and after countless guides, tips, tricks I have gotten no farther.

Some things I noticed that seem odd is I get a similar can't connect msg if I try and use the command prompt and put in anything with 'sqlcmd', but maybe I just don't know the syntax well enough, still seems strange that it keeps telling me I need to check to make sure remote and local connections are enabled (I have checked, double checked and triple checked just to be sure windows wasn't changing it back on me).

When using the Management Studio to set up the mirror I notice that when I finish the setup wizard it has the mirror listed without its FQDN, but I checked the machine and it is on the domain so I just edit the mirror information to include the domain information.

My real question is what gives? Has ANYONE ever had issues like this with SQL? Do I need to start over? Is there some tiny detail that can hinder this whole mirroring process? The machines themselves are more or less fresh installs of XP SP2 and it is SQL Server 2005 SP2. I am really frustrated and I am not a pro at this so to say the least...this SUCKS!

As always any help is much appreciated and I am in desperate need of it. Anything you can think of I am willing to try as right now I am about to solve this problem by putting my foot through the side of the computer.

Thanks again guys!

Answer these please:

-What are you using to access this database?
-What is your connection string?
-Paste the code you're using (or describe controls) to initiate the connection;
-If you're using VS, is it linked in your Server Explorer?
-From which computer are you connecting with Command Prompt to try SqlCmd?

After modifying the host file to include the mirror and witness I get a different error....1431

"Neither the partner nor the witness server instance for database "testdatabase" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)

So I was going to just shoot myself, because these errors made no sense at all! BUT then as I was about to load my revolver I had a thought...maybe...just maybe if I modify the HOST file on each computer to include the information about the other two computers it might just work.

IT WORKED!!!!

By modifying the host files to include the IP and FQDN of each computer the database is being mirrored and the witness is also working. PRAISE TO THE GEEK GODS!

To answer a few questions from Sonata...
They are three basic computers with XP SP2 on them with the principal and mirror running SQL 2005 Developer SP2 (this is key if you don't want to have to use the 1400 trace flag thing) and SQL express on the witness.

I had tried a lot of code and such so I won't post it all, but mainly I used the ALTER DATABASE, CREATE and DROP commands.

I was using the local computer to connect to sqlcmd so that is still strange, but isn't hindering the mirror so I am glad it all is working. Tomorrow I am going to run tests to see if it actually will failover and if I can reverse the roles of the principal and mirror.

Have a great holiday season everyone and a Happy New Year to one and all!

So I am just getting countless random errors, none of them make sense and here is the latest one I got...

"Neither the partner nor the witness server instance for database "testdatabase" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)

I guess these error msgs aren't always the real cause of the problem...very very frustrating because I had done everything instructed in order to correct each error I came across with no success.

So I was going to just shoot myself, because these errors made no sense at all! BUT then as I was about to load my revolver I had a thought...maybe...just maybe if I modify the HOST file on each computer to include the information about the other two computers (and itself just in case) it might just work.

IT WORKED!!!!

By modifying the host files to include the IP and FQDN of each computer the database is being mirrored and the witness is also working. PRAISE TO THE GEEK GODS!

To answer a few questions from Sonata...
-These are three basic computers with XP SP2 on them with the principal and mirror running SQL 2005 Developer SP2 (this is key if you don't want to have to use the 1400 trace flag thing) and SQL express on the witness.

-I had tried a lot of code and such so I won't post it all, but mainly I used the ALTER DATABASE, CREATE and DROP commands.

-I was using the local computer to connect to sqlcmd so that is still strange, but isn't hindering the mirror. Tomorrow I am going to run tests to see if it actually will failover and if I can reverse the roles of the principal and mirror.

Have a great holiday season everyone and a Happy New Year to one and all!

I must say this hosts file trick really works. I was pulling my hair out until I found this post.

Update your hosts files on each machine and presto! it works.

Thank you very much for posting this information.

David
:)

I am actually really glad that you were able to use this information to get to a solution. I just wish that the errors M$ SQL spits at you would make more sense, but alas what do you expect with M$.

I just had to do another failover cluster for a company that had put the computers I was to use in a non-domain setup. Plus I couldn't join the computers to the domain because of security issues.

I had to teach myself a crash course in creating SQL certificates. Now that is painful, but I'll spare you the details, in the end I got it all working...minus a few patches of hair.