I have an application running on a server which has a SqlDependency / query notification - monitoring changes on a table on a different server.
It works fine until we reboot/restart SQL Server. When SQL Server is rebooted due to some maintenance and patches, the other application throws the following errors and stops. I can definitely say it stops because it does not monitor changes once the SQL Server is up and running.
I have to restart the application to resubscribe to the query notification. I am not throwing any exception inside the code that would stop the application. I am catching the exception and sending an email.
System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) --->
System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
I am new to SqlDependency / query notifications so I am not sure whether this is an expected behaviour or something I am doing wrong. It's my understanding(from other posts (SqlDependency Reliablity?) that I don't have to restart the job to resubscribe.
Appreciate your time and answers
This is normal because when you restart the sql server, the service was stopped and any application that is connected to sql server will be thrown this error:
To avoid this problem, you can use a different instance for every application. You can install new server instances with different names to avoid this issue. Which when you restart a single sql server instance, the other sql server instance will still remain online.
You can follow these steps to install or create a new sql server instance:
https://4sysops.com/archives/how-to-create-a-new-sql-server-2012-instance/