• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1128
  • Last Modified:

SQL Server Mirroring

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
comXo
Asked:
comXo
  • 5
  • 4
1 Solution
 
dbaSQLCommented:
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
 
rajeevnandanmishraCommented:
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
 
comXoAuthor Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
comXoAuthor Commented:
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
 
rajeevnandanmishraCommented:
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
 
comXoAuthor Commented:
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
 
rajeevnandanmishraCommented:
The output that you are getting is ok.
And other things are also ok.
I really could n't get on it.
0
 
rajeevnandanmishraCommented:
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
 
comXoAuthor Commented:
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
 
comXoAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now