locked
Guidance for using SqlDependency in a cluster failover scenario RRS feed

  • Question

  • I am looking for guidance on how to use SqlDependency in a cluster failover scenario.

    I have an application which uses SqlDependency to get notified of changes - things are working fine so far.  The database resides on a database instance on a 2 node cluster.  Everything was going fine until a recent instance failover incident resulted in the application generating the below error.  The error was triggered when attempting to call SqlDepdendency.Start().  Connection string is always the same and did not change (refers to the instance name and not specific cluster node).  The only way to recover was to restart the App pool of the application.

    My question is two fold:

    1. Why would a cluster failover cause this specific error?  I have code to Start SqlDepdendency upon error notification which is what generated this specific error - which kind of defeats the purpose of me trapping the error by the way.  

    2. How to effectively recover from this scenario without an app pool recycle?  Does a cluster failover causes unrecoverable failures for SqlDependency i.e. is this specific behavior for SqlDepedency?  ADO.NET connection is able to open SqlConnection when opening connection to the same instance after a failover.

    Any help would be appreciated.  I have tried to search the web, but there is not much information out there for SqlDependency and failover scenarios.

    Thanks

    -Gan 

    SqlDependency does not support calling Start() with different connection strings having the same server, user, and database in the same app domain.

    Exception Type: System.InvalidOperationException

    Exception Source: System.Data @ Start

    Stack Trace:

       at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults)

     
    Wednesday, May 9, 2012 11:30 PM

All replies

  • wow no replies? :-).

    Should I post in a different forum?

    Friday, May 11, 2012 6:27 PM
  • Hi gana2k2,

    I found some information about your question here:

    =============

    If read-only routing is not in effect, connecting to a secondary replica location in an availability group will fail in the following situations:

    1. If the secondary replica location is not configured to accept connections.

    2. If an application uses ApplicationIntent=ReadWrite (discussed below) and the secondary replica location is configured for read-only access.

    SqlDependency is not supported on read-only secondary replicas.

    A connection will fail if a primary replica is configured to reject read-only workloads and the connection string contains ApplicationIntent=ReadOnly.

    =============

    http://msdn.microsoft.com/en-us/library/hh205662(v=vs.100).aspx

    Please try to set connection property here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 15, 2012 3:39 AM
  • Alan

    Thank you very much for taking the time to respond.  I will review the read-only secondary replicas in detail, but I am not sure it is applicable to my setup.

    When we fail over, we failover the instance and everything is available (read-write).  And FYI - we do not have mirroring.

    I should have posted my operating environments when I asked the question :-).  We are on SQL Server 2008 R2 x64, .Net 4.0.

    If I am not mistaken the referred article is applicable with SQL Server 2012?  I could be wrong.

    Thank you so much.

    -Gan

    Tuesday, May 15, 2012 5:58 PM
  • Any luck Gan,

    we are facing same issue some details are:

    data base server: SQl server 2008 r2

    using sqldepe in LinqToSQl

    Friday, June 15, 2012 1:36 PM