comXo
asked on
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\SQLEXP
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
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\SQLEXP
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
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'
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'
ASKER
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
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
ASKER
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?
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?
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?
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?
ASKER
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.
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.
The output that you are getting is ok.
And other things are also ok.
I really could n't get on it.
And other things are also ok.
I really could n't get on it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
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