locked
Automatic Failover Not Working RRS feed

  • Question

  • Just a quick question, I have a scenario in which we have SQL mirror and automatic failover isn't working. I keep seeing the error 1474 with the message

    Database mirroring connection error 4 'An error occurred while receiving data: '24(The program issued a command but the command length is incorrect.)'.'

    Whenever I shut down the principal to simulate automatic failover.

    The Principal and Mirror are running SQL 2005 SP3 and I just noticed that the witness is running SQL 2012 Express. Everything works fine except auto failover and I am assuming that it's because the witness is on a much different version. I know it's probably kind of a dumb question but I just wanted to confirm for sure before I have our techs start downgrading (we probably have several that got the wrong version).

    Thank you in advance for your time!


    • Edited by Scott_42 Tuesday, June 30, 2015 1:50 PM
    Monday, June 29, 2015 2:27 PM

Answers

  • Hi Scott

    I can't say I've tried a witness on a different version, but here's what the documentation says:

    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. For example, a SQL Server 2008 witness is supported when you are upgrading from a SQL Server 2008 mirroring configuration but cannot be added to an existing or new SQL Server 2008 R2 or later mirroring configuration.

    So I think it's a good idea to downgrade the witness!

    By the way it's absolutely fine to use Express edition as a witness, it's officially supported and I've never had an issue with it.

    Monday, June 29, 2015 3:49 PM

All replies

  • Does witness contains SQL agent as it is Express i think agent wont. Please change the witness version from express to server edition and try, it will work for sure.
    Monday, June 29, 2015 2:34 PM
  • No agent as it is Express. We don't have licenses to do server edition but express has always worked for us in the past.
    Monday, June 29, 2015 2:55 PM
  • Hi Scott

    I can't say I've tried a witness on a different version, but here's what the documentation says:

    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. For example, a SQL Server 2008 witness is supported when you are upgrading from a SQL Server 2008 mirroring configuration but cannot be added to an existing or new SQL Server 2008 R2 or later mirroring configuration.

    So I think it's a good idea to downgrade the witness!

    By the way it's absolutely fine to use Express edition as a witness, it's officially supported and I've never had an issue with it.

    Monday, June 29, 2015 3:49 PM
  • Well, that pretty much spells it out! I'll schedule a time to do the downgrade and post the results to confirm. Thanks for the find!
    Monday, June 29, 2015 3:54 PM
  • I just did a downgrade as a test at the location where auto failover wasn't working so the Witness SQL version now matches the version of the Principal and Mirror and automatic failover is working again. Thanks again!
    Tuesday, June 30, 2015 1:58 PM
  • I just did a downgrade as a test at the location where auto failover wasn't working so the Witness SQL version now matches the version of the Principal and Mirror and automatic failover is working again. Thanks again!
    Thanks for confirming!
    Tuesday, June 30, 2015 2:03 PM