We got very strange SqlDependency behaviour after SQL server restarts.
In few words:
BUG1: Sometimes (actually almost every time) after SQL server restart SQL server dependencies established AFTER RESTART never receive notifications.
BUG2: SqlNotificationInfo.Restart <-- Never got it, but according to the MSDN I should
Seems to make SqlDependency working after restart we must call SqlDependency.Stop/SqlDependency.Sart but from OnChangeEventHandler it's very hard to understand when we should call it because currently in OnChangeEventHandler it's not possible to understand Sql Server was really restarted.
Another thing is that after Sql Server restarted we still see Server Broker queue and service created. But the, after some time it is deleted! Then, if you'll wait longer you'll find server broker queue and service created again. BUT, sure all Dependencies made before is gone. Gone silently...
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)