Calling SqlDependency.Start two times continuously, the second time failed?

Question

The purpose of calling SqlDependency.Start multiple times is to ensure it's fine before some other action such as creating a new instance of SqlCacheDependency based on a Command. According to Microsoft's document about SqlDependency.Start at here https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.start(v=vs.110).aspx (the Remarks section), looks like calling SqlDependency.Start multiple times is totally fine:

Multiple calls with identical parameters (the same connection string and Windows credentials in the calling thread) are valid.

But actually it can fail (and really it has never succeeded for me) for the second call, making all next attempts to call SqlDependency.Start fail (silently by returning false, no exception is thrown).

What I did should meet the first restriction (mentioned in the Remarks section in the above link), that is all the calls to SqlDependency.Start have the same parameters (in fact there was just 1 same parameter which is the connection string). It looks just like this:

//at initialization step (such as in `Application_Start()` in ASP.NET MVC)
SqlDependency.Start(myConnectionString);//this usually returns OK 
//later at the time before creating an instance of SqlCacheDependency
//I tried to call the Start method again to ensure everything is ok
var ok = SqlDependency.Start(myConnectionString);//almost always false
if(ok){
    //almost never reach here ...
}

So it's really hard to understand about what stated by Microsoft (in the first restriction in the Remarks section), the 2 calls are exactly the same. But with the second call failed, any that same call used after that will still fail (meaning there is not any chance to start it successfully once I attempted to call it more than once).

When I see the log in Sql Server I can see that there are a lot of messages saying something like Cannot find the remote service ... because it does not exist

I don't need a solution or work-around this problem, I just need some explanation to why it does not work expectedly like what Microsoft stated, or I misunderstood what stated by Microsoft?

Answer 1

As Jeroen Mostert mentioned in the comments and the docs for SqlCommand.Start() state:

Returns

Boolean

true if the listener initialized successfully; false if a compatible listener already exists.

As the remarks in the docs describe, SqlDependency.Start() and SqlDependency.Stop() will keep track of the number of calls to each one. It will ensure a background connection is running or being set up if the number of calls to SqlDependency.Start() exceeds the number of calls to SqlDependency.Stop() (though I think it loses track and resets its count if you call SqlDependency.Stop() more times than than you call SqlDependency.Start()).

Start() Errors

It may help to clarify that it is possible for SqlDependency.Start() to fail. One way to get it to fail is to call it multiple times from one AppDomain with different connection strings. Within a particular AppDomain, SqlDependency.Start() will throw an exception if you pass in a different connection string unless if at least one of the following properties in the connection string is different from a previously passed connection string:

1. Database name
2. Username

I.e., you are expected to normalize or cache the connection string you first pass to SqlDependency.Start() so that you never pass it a string that has, for example, a different value for Max Pool Size. I think it does this to try to avoid creating a lot of broker queues and connections for a single process. Additionally, when it tries to match up a command to a broker queue when you actually set up an SqlDependency later, it probably uses these distinguishing connection string properties to decide which queue to use.

ASP.NET Life Cycle

From the ASP.NET Application Life Cycle documentation under “Life Cycle Events and the Global.asax file”, note the following:

The Application_Start method, while an instance method, is called only when the application is starting which often occurs during the first HTTP request for your application. The documentation specifically states:

You should set only static data during application start. Do not set any instance data because it will be available only to the first instance of the HttpApplication class that is created.

The method you should use to clean up things which you initialized in Application_Start is Application_End. When a webapp is gracefully stopped, an instance of your application class will be created and Application_End called on it. Note that this might be a different instance of the application class than Application_Start was called on.

Because of ASP.NET’s architecture, a distinct HttpApplication class instance is required for each request that is processing. That means that multiple instances will be created to handle concurrent requests. The docs also state that, for performance reasons, application class instances may be cached by the framework and used for multiple requests. To give you an opportunity to initialize and cleanup your application class at an instance level, you may implement Init and Dispose methods. These methods should configure the application class’s instance variables that are not specific to a particular requests. The docs state:

Init

Called once for every instance of the HttpApplication class after all modules have been created.

Dispose

Called before the application instance is destroyed.

However, you mentioned that you were initializing global state (i.e., SqlDependency.Start()) in Application_Start and cleaning up global state (i.e., SqlDependency.Stop()) in Dispose(). Due to the fact that Application_Start will be called once and is intended for configuring statics/globals and Dispose() is called for each application class instance that the framework retires (which may happen multiple times before Application_End() is called), it is likely that you are stopping the dependency quickly.

Thus, it may be that SqlDependency.Stop() is called after the server runs out of requests, in which case it would clean up the HttpApplication instance by calling Dispose(). Any attempts to actually start monitoring for changes by attaching an SqlDependency to an SqlCommand should likely fail at after that. I am not sure what already-subscribed commands will do, but they may fail at that point which would trigger your code to resubscribe a new dependency which should then hit an error. This could be the explanation for your “Cannot find the remote service” errors—you called SqlDependency.Stop() too early and too often.