I have a need where I will need to check a specific table on a remote SQL Server for any data changes.

And this I will need to do as soon as it is possible e.g almost every second so that as soon as a change happens, I can do something about it on another server where I am running this watcher program.

I think opening a connection every second to remote may be expansive, and keeping a connection open for a very long time is considered a bad practice. What is the best option for me?

Server 1 has a watcher program, which will monitor data change activity on Server 2 (remote server). I must know about data change as soon as it happens on Server 2 and then perform some actions on Server 1

Thanks

1 Answers

1
David Browne - Microsoft On

I think opening a connection every second to remote may be expensive,

It's not. Read about Connection Pooling. The network connection and SQL Server session are not closed when you call SqlConnection.Close() or SqlConnection.Dispose(). The underlying connection is simply returned to the pool for later use.

But if the connection is lost or the server fails, you will get a new connection from the pool, so it's vastly more reliable than keeping a single connection open.