SqlDependency / Query Notification - SQL Server Reboot

Question

I have an application running on a server with the SqlDependency / query notification - tracking changes in a table on another server.

It works fine until we reboot / reboot SQL Server. When SQL Server restarts due to some maintenance and fixes, another application throws the following errors and stops. I can say that it stops because it does not track changes after starting and starting SQL Server.

I need to restart the application to resubmit the request subscription. I do not make any exceptions inside the code that would stop the application. I will catch the exception and send an email.

System.Data.SqlClient.SqlException (0x80131904): A transport level error occurred while sending a request to the server. (provider: TCP provider, error: 0 - existing connection was forcibly closed by the remote host.) --->

System.Data.SqlClient.SqlException (0x80131904): A network-related or specific instance error occurred while establishing a connection to SQL Server. The server was not found or was not available. Verify the instance name is correct and configure SQL Server to connect remotely.

I am new to SqlDependency / query notifications, so I'm not sure if this is the expected behavior or something that I am doing wrong. It is my understanding (from other posts ( SqlDependency Reliablity? ) That I do not need to restart the job to resubmit.

Appreciate your time and answers

Answer 1

This is normal, because when the SQL server was restarted, the service was stopped, and any application connected to the sql server will be caused by this error:

When connecting to SQL Server, a network-related or instance-specific error occurred.

To avoid this problem, you can use a different instance for each application. To avoid this problem, you can install new server instances with different names. That when you restart one instance of sql server, another instance of sql server 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/