6

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

Community
  • 1
  • 1
itsfighter
  • 167
  • 4
  • 15
  • 1
    This is expected. If the SQL connection gets broken due to a SQL Server reboot, the application will need to subscribe again. – Dan Guzman Jul 27 '15 at 13:03

1 Answers1

0

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:

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

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/

spenibus
  • 4,339
  • 11
  • 26
  • 35
Mohamed Suffian Salehin
  • 1