Connection lost to Azure SQL DB

[twright-msft]

Issue Type: Bug

I connected to Azure SQL DB, ran a few queries, left the query window open for about 15-20 minutes and then went to run another query and got this error message:
Query failed: Invalid operation. The connection is closed.

Azure Data Studio version: Azure Data Studio 1.1.2-insider (58f950f, 2018-10-15T03:08:44.410Z)
OS version: Darwin x64 17.7.0

System Info

Item	Value
CPUs	Intel(R) Core(TM) i7-4770HQ CPU @ 2.20GHz (8 x 2200)
GPU Status	2d_canvas: enabled checker_imaging: disabled_off flash_3d: enabled flash_stage3d: enabled flash_stage3d_baseline: enabled gpu_compositing: enabled multiple_raster_threads: enabled_on native_gpu_memory_buffers: enabled rasterization: enabled video_decode: enabled video_encode: enabled webgl: enabled webgl2: enabled
Load (avg)	2, 2, 2
Memory (System)	16.00GB (0.02GB free)
Process Argv	/Applications/Azure Data Studio.app/Contents/MacOS/Electron -psn_0_598162
Screen Reader	no
VM	0%

Extensions (2)

Extension	Author (truncated)	Version
ssmskeymap	kev	0.2.0
sql-vnext	Mic	0.6.9

[kburtram]

The query editor will try to hold an open connection for the duration of the document's lifetime. i.e., if you run a SQL script multiple times it will reuse the connection. This is generally what you want to avoid having to wait to reconnect each time you run a query.

The downside is that if the connection is closed the UX can be what you're seeing. You run a query and get a general connectivity exception. We recently made a change that when there is any exception raised during query execution we'll close and reopen the connection. This means that subsequent reexecutions will have a new "good" connection and likely succeed.

The query execution code checks whether the SqlConnection is Open prior to running the query, but unfortunately the SqlConnection will often incorrectly report as Open until it's used and throws, at which point it then correctly reports as closed.

A couple ideas to "smooth out" the inactive editor scenario are (1) we could automatically reopen connections that haven't been used for several minutes, this would cause a little delay when the editor becomes active again, but would avoid hitting these errors in many cases (2) we could have a connection "watchdog" component that sent simple "no-op" queries periodically on all tracked connections to ensure they are actually still open, this would allow us to close\reopen connections transparently to users.

For reference, I don't think SSMS does either of these mitigation, and relies on Connections to be generally reliable. I'll double-check to verify this. Also, please note we are seeing Socket connections to SQL Server generally less reliable on macOS .Net Core clients than on Windows clients.

[kburtram]

The "dropped connection during demo scenarios" issue has been fixed. There are investigation details in #2718. I'll close this issue based on that fix. There are still a few related connectivity issues we're actively working on.