No Failover and client crashes ODBC DSN Setting for Failover in connection String

We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.

However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.

The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.

Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.

The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.

Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.

Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.

We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?

Are we missing a service pack on the client?

Thanks,

Kimball

Have you referred to Maccola vendor in this case about the compatibility, I would guess this is to with that drivers.

No Failover and client crashes ODBC DSN Setting for Failover in connection String

We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.

However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.

The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.

Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.

The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.

Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.

Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.

We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?

Are we missing a service pack on the client?

Thanks,

Kimball

Have you referred to Maccola vendor in this case about the compatibility, I would guess this is to with that drivers.

Newly created witness server only connects to one of the partners?

We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.

See screenshot here

Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?

Hello. I started from scratch on a test environment and I get the exact same problem. On the mirror I get that Witness Connection status as Disconnected.

I don't use certificates and also no encrypton as in this tutorial: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

The direct result is that automatic failover does not work.

Some help will be great!

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I managed to make it work, but my solution is really convoluted and made me feel really uneasy:

- Setup a HOSTS file entry on Partner1 and Partner2 to point the witness IP address to the name WITNESS:

10.1.0.123 WITNESS

- Add the witness to the DB using the HOSTS entry (in TSQL)

alter database MyDatabase set witness = 'tcp://WITNESS:5022'

- Check mirroring monitor. Monitor confirms that the winess is only connected to Partner1

- Change the HOSTS entry on Partner1 to the wrong IP address for WITNESS:

0.0.0.0 WITNESS

- Restart the SQL Service on the Witness server. After the restart, mirroring monitor shows that the witness is now connected to Partner2, and not to Partner1

- Fix the HOSTS entry on Partner1 to the correct IP address again

- Wait 10 mins

- Check mirroring monitor - it now shows the witness connected to both servers! (if it's still not connected to Partner1, restart the witness again)

What? This makes no sense, but it worked for me.

|||

You know what. I followed your instructions step by step and indeed it works. But this is not a way I declare myself satisfied either...

It makes no sense... I wonder how anyone was able to make this work as it seems that this is a clear bug.

|||If someone wants the step by step to reproduce this I can tell it. It's a fairly basic process, following the instructions and still it doesn't work...|||

Anyway just to let you know I downloaded from Microsoft their SQL2005 in VHD (virtual hard drive) format and ran it under virtual server. And I tried step by step and it's giving the exact problem... there is something wrong there for sure.

No one willing to comment on this so far?

|||

I found that simply restarting SQL Server on the witness server solves this issue.

-dta

|||how exactlly do you do that? add entry to the hosts file? be exact please, i'm new and not very good with t-sql

Newly created witness server only connects to one of the partners?

We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.

See screenshot here

Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?

Hello. I started from scratch on a test environment and I get the exact same problem. On the mirror I get that Witness Connection status as Disconnected.

I don't use certificates and also no encrypton as in this tutorial: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

The direct result is that automatic failover does not work.

Some help will be great!

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I managed to make it work, but my solution is really convoluted and made me feel really uneasy:

- Setup a HOSTS file entry on Partner1 and Partner2 to point the witness IP address to the name WITNESS:

10.1.0.123 WITNESS

- Add the witness to the DB using the HOSTS entry (in TSQL)

alter database MyDatabase set witness = 'tcp://WITNESS:5022'

- Check mirroring monitor. Monitor confirms that the winess is only connected to Partner1

- Change the HOSTS entry on Partner1 to the wrong IP address for WITNESS:

0.0.0.0 WITNESS

- Restart the SQL Service on the Witness server. After the restart, mirroring monitor shows that the witness is now connected to Partner2, and not to Partner1

- Fix the HOSTS entry on Partner1 to the correct IP address again

- Wait 10 mins

- Check mirroring monitor - it now shows the witness connected to both servers! (if it's still not connected to Partner1, restart the witness again)

What? This makes no sense, but it worked for me.

|||

You know what. I followed your instructions step by step and indeed it works. But this is not a way I declare myself satisfied either...

It makes no sense... I wonder how anyone was able to make this work as it seems that this is a clear bug.

|||If someone wants the step by step to reproduce this I can tell it. It's a fairly basic process, following the instructions and still it doesn't work...|||

Anyway just to let you know I downloaded from Microsoft their SQL2005 in VHD (virtual hard drive) format and ran it under virtual server. And I tried step by step and it's giving the exact problem... there is something wrong there for sure.

No one willing to comment on this so far?

|||

I found that simply restarting SQL Server on the witness server solves this issue.

-dta

|||how exactlly do you do that? add entry to the hosts file? be exact please, i'm new and not very good with t-sql