?
Solved

SQL Server Mirroring

Posted on 2011-10-11
10
Medium Priority
?
1,064 Views
Last Modified: 2012-05-12
Hi,

I have a Database setup that is being Mirrored to another Server in the same Workgroup on the same IP range (different physical locations). This current mirror is working.

Trying to add a Witness at a different location and on a different IP address range isnt working though. Their are already rules to transmit data for this IP range via routers to the site which all works fine. I can connect to each database from each as well so i know there are no real connection issues.

The error Im getting though however is ...

The ALTER DATABASE command failed due to an invalid connection string.
(this is from the command "ALTER DATABASE [ClickToCall] SET WITNESS = N'TCP://10.10.10.40\SQLEXPRESS:5022'")

If I remove the SQLEXPRESS part then I get...
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.10.10.40:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)


All the whislt, in the object explorer the database is connected.

The firewalls on each of the servers are off, even the tricky secondary ones, and the versions of each server are the same at 10.50.1600. Also, Before moving to the different sites, each of these have been connected in a witnessed mirror, although they were all on the same IP range.


Does anyone know why this would have such a bizarre connection issue?

Many Thanks
Alex
0
Comment
Question by:comXo
  • 5
  • 4
10 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36948218
have you seen these mirroring troubleshooting tips?
http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/611d4c49-c37e-42de-b1e0-7db7ece718f5/


Also, this is the same error you are receiving, and it seems in the same conditions:
http://support.microsoft.com/kb/940254
0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 36948429
Hi,
Are you using the same window account for connecting the 3 SQL Server servers (Principal, Mirror & Witness)?

As you said that the IP range is different can you try adding an entry in windows host file about IP and server name (on the principal for Witness and on the witness for principal).

I hope you have already tested the telnet connectivity (with port) between principal and witness server.

Then use the below command with the ServerName instead of IP.
ALTER DATABASE [ClickToCall] SET WITNESS = N'TCP://WitnessSever:5022'")


0
 

Author Comment

by:comXo
ID: 36951945
Thanks dbaSQL, im checking out the links you sent me now.

rajeevnandanmishra... Im using mixed mode and each of the servers are using the sa login.
Ive tried adding the ip and server name to each of the host files to no avail.

The witness server is an express version, should i have \sqlexpress after the server name of the alter statement

The telnet though I tried and couldnt connect, but i wasnt sure about it... as the ssms was connectable both ways.

Cheers for your help, I shall get back to you with my progress.

Thanks
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:comXo
ID: 36952555
Hi,

To let you know, Ive tried telnetting between the mirror and principle which is already connected and working, and im unable to connect these either?

0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 36954003
The tasks that you are doing are corrrect and should be able run properly. No you need not to specify "\sqlexpress".
Suppose you have added "10.10.10.40" as "MyWitnessServer" in the host file on principal server. Then the following command on the principal server should be able to run correctly.
>TELNET MyWitnessServer 5022

I had never tried this with SQL Express. Bythe way which version of SQL Express you are using for witness server?
0
 

Author Comment

by:comXo
ID: 36961140
Hi,

Yeah ive added to the host files, but no joy on telnet... It just sits looking blankley at me until it times out and says press any key to continue.

Microsoft Telnet> open xxxxxxxx 5022
Connecting To xxxxxxxx...

If I do it straight from the cli as you have, the screen goes black and then sits until it times out until it returns to the cli.

Is this usual? Could it be waiting for a response of some kind?

SQLExpress is the same version as the Primary and Mirror. In fact I think I used the same CD, just selected the Express with Advanced Services... (10.50.1600)

Ive just tried to telnet from the Principle to the Mirror and I am getting the same results.


0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 36963325
The output that you are getting is ok.
And other things are also ok.
I really could n't get on it.
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 2000 total points
ID: 36963348
It may seems stupid, but, is it possible for you to stop the current mirroring. And then re-run all the steps again (including the addition of Witness Server).

I also request you to use the "Request Attention" mode. Somebody will definitely be able to resolve it.
0
 

Author Comment

by:comXo
ID: 36968001
Hi,

Stopping the mirroring and running it again could be an option but i will have to over the weekend. I'll let you know how this goes.

I dont understand the next though, what do you mean by "Request Attention" mode?

Thanks
0
 

Author Comment

by:comXo
ID: 36993662
Hi,

I have just been able to redo all the mirroring configuration. Having done a new full back up and transaction log backup, restored both on the mirror and witness.

This is now for what ever reason working.

Thanks for all your help with this.

As a side note, Once i went through the Configure security wizard, the Server network address box for the witness removed the \SQLEXPRESS from the address. It does work without it here, although i did have this in the connection box whilst running through the wizard.

Alex
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question