Problem adding Witness in the DB Mirroring Configuration

Category: sql server mirroring

Question

SQLDBA01 on Thu, 13 Dec 2018 15:46:45


Hello,

Principal - SQL Server Std 2008 SP3

Mirror - SQL Server Std 2008 SP3

Witness - SQL Server 2005 Developer Edition SP3

I have 3 servers in DB Mirroring configuration.  I can add Principal and Mirror partner just fine but when it gets to adding Witness it throws out this error the below error. I have done all the basic testing from Pings to Telnet to port 5022 across Principal, Mirror and Witness but it is not getting anywhere.  Worked with Windows SA and he verified that there are no connectivity issues he had observed from the OS side and there are not firewall issue.

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://<ServerName>:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Please help me it has been couple of days where we have not been successful.  I can provide any information that you need for me to troubleshoot this issue therefore did not want to throw out their logs before soliciting your input.  I'd highly appreciate any help and response.

Thanks

SQLDBA01



Replies

jmcmullen on Thu, 13 Dec 2018 16:21:32


The servers need to be the same version of SQL Server, see here

Except during an upgrade from an earlier version of SQL Server, 
the server instances in a mirroring session must all be running the same version of SQL Server

You should get your hands on an Express edition of SQL Server 2008 (SP3) and use that.


SQLDBA01 on Thu, 13 Dec 2018 17:51:39


Thanks for the reply.

Actually we did not upgrade the SQL Server. We did an OS refresh for brand new Principal and Mirror servers and installed same SQL Server 2008 version.  DB mirroring was removed before the OS upgrade so we want to use the same existing Witness server as there are other DB mirroring on other SQL Servers.

Everything was working fine on the old servers as far as DB mirroring is concerned.

jmcmullen on Thu, 13 Dec 2018 18:12:35


I wonder if the primary and mirror were previously upgraded from 2005 to 2008? As per the documentation different versions are supported as part of an upgrade process but NOT as part of a new deployment, i.e. newly adding a server with a different version isn't possible.

I think your only option here is to install 2008 as a named instance on your existing server (or indeed on a new server) and take it from there. It's the 'supported' config. 

Also, having developer edition as your witness may not be 'allowed' from a licensing perspective. I'm not sure on that though - I'm far form a licencing expert :)

SQLDBA01 on Thu, 13 Dec 2018 18:34:38


No sir, P and M were not upgraded from 2005 to 2008.

Just additional point that we did a new deployment for 2 other DB servers also with brand new P and M machines (SQL 2008 Std) and on that one we have existing Witness which is using SQL Server 2008 Developer Edition and we did not encounter any issues with the Witness add.

When you say that 'newly adding a server with a different version isn't possible', are you saying flavor wise Dev vs Std or 2005 vs 2008 wise?

I have requested a new machine for Witness from the Windows SA as a trial to make sure it is not connectivity issue. I will install 2005 Dev first and see if it works, if not will install 2008 then and test again.

jmcmullen on Thu, 13 Dec 2018 18:59:51


In this context by version I mean 2005/2008/etc. The editions should be fine, in fact you'd typically have the witness at the same version (2008) but use Express edition (rather than Std or Ent) - simply because it's cheaper from a licensing perspective. 

I'm sure Developer edition is fine to use technically as a witness - i'd just be slightly concerned about the licensing implications (as it's not licensed to be used in a "production" environment). 

SQLDBA01 on Thu, 13 Dec 2018 19:46:51


Thanks for the explanation. 

So in the conclusion I would say even if we did not perform in-place upgrade of SQL Server version on the existing (old) servers rather keeping P and M at SQL 2008 same version on the new server deployments (installed a fresh copy of SQL Server 2008 and restored DBs) but existing witness being 2005 did not like it so it needs to at the same version 2008 whether Express/Std/Ent according to the documentation you forwarded.

Did I get that right?



jmcmullen on Thu, 13 Dec 2018 20:08:01


Yep I think that's a fair summation. If you get your witness to match you current P/M version (2008) you should be good. 

SQLDBA01 on Thu, 13 Dec 2018 20:22:25


Great...thank you for your time and help.  I will post here when we have a successful result. Regards!

SQLDBA01 on Thu, 13 Dec 2018 23:11:52


I tried another brand new SQL 2008 Std machine as a witness but it is throwing out the same error on Principal. Darn it!

jmcmullen on Thu, 13 Dec 2018 23:36:12


That's odd, is there any extra information in the SQL Server error log on the principal and witness? The errors thrown aren't very instructive but there can but more meaningful info in the logs.

Can you rule out a connectivity issue by trying to telnet from your principal to your witness on port 5022? 

SQLDBA01 on Fri, 14 Dec 2018 15:25:32


Good Morning,

I checked SQL Logs on the brand new Witness (using as a test) and it was using a different Encryption Algorithm RC4. I changed it to AES to match with P and M servers and voila! it worked. We are going to communicate with the customer that we will need a dedicated new Witness VM server as I was just using one of their existing new DB server as a trial witness.

From the Witness Server SQL Logs:

Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.

Thank you very much for your guidance and help again.  I wish I could have posted here before rather than spending so many hours on it.

Regards,

SQLDBA01

jmcmullen on Fri, 14 Dec 2018 15:27:40


Nice!  Glad it's sorted.