SqlDependency/Query notification - SQL Server reboot

Question

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 expected. If the SQL connection gets broken due to a SQL Server reboot, the application will need to subscribe again. — Dan Guzman
– Dan Guzman, Commented Jul 27, 2015 at 13:03

spenibus · Accepted Answer · 2015-10-23 01:42:21Z

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/

edited Oct 23, 2015 at 1:42

spenibus

4,41911 gold badges28 silver badges36 bronze badges

answered Oct 23, 2015 at 1:12

Mohamed Suffian Salehin

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlDependency/Query notification - SQL Server reboot

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related