search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.59k stars 909 forks source link

Does not handle server going offline well #20385

Open kburtram opened 2 years ago

kburtram commented 2 years ago

Steps to Reproduce:

  1. Connect to server and open some queries, OE, dashboard, etc.
  2. Turn off server

Expected: Reasonable error message that server is unreachable and option to disconnect/close UI

Actual: UI breaks and no error message. There are errors in the logs. For example, query results are shown blank and many things timeout after a couple minute delays.

image

Errors in console

[Error - 10:17:18 AM] Request connection/listdatabases failed. (at j.appendLine (c:\Users\karlb\AppData\Local\Programs\Azure Data Studio - RC1\resources\app\extensions\mssql\dist\main.js:2:1693904))

console.ts:137 [Extension Host]   Message: Microsoft.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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
 ---> System.ComponentModel.Win32Exception (53): The network path was not found.
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<Open>b__29_0() in D:\a\_work\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 286
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.<>c__DisplayClass25_0.<ExecuteAction>b__0(RetryState _) in D:\a\_work\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\RetryPolicy.cs:line 128
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.<>c__DisplayClass26_0.<ExecuteAction>b__0(RetryState retryState) in D:\a\_work\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\RetryPolicy.cs:line 141
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.ExecuteAction[R](Func`2 func, Nullable`1 token) in D:\a\_work\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\RetryPolicy.cs:line 179
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.ExecuteAction(Action`1 action, Nullable`1 token) in D:\a\_work\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\RetryPolicy.cs:line 138
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.ExecuteAction(Action action, Nullable`1 token) in D:\a\_work\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\RetryPolicy.cs:line 127
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.Open() in D:\a\_work\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 282
   at Microsoft.SqlTools.ServiceLayer.Connection.ListDatabaseRequestHandler`1.HandleRequest(ISqlConnectionFactory connectionFactory, ConnectionInfo connectionInfo) in D:\a\_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ListDatabaseRequestHandler.cs:line 71
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.ListDatabases(ListDatabasesParams listDatabasesParams) in D:\a\_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 1026
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.<>c__DisplayClass72_0.<<HandleListDatabasesRequest>b__0>d.MoveNext() in D:\a\_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 1151

ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:53,State:0,Class:20  Code: 0  (at j.appendLine (c:\Users\karlb\AppData\Local\Programs\Azure Data Studio - RC1\resources\app\extensions\mssql\dist\main.js:2:1693904))
log.ts:301   ERR Error loading database names for query editor  TypeError: Cannot read property 'databaseNames' of undefined
    at K.getDatabaseNames (queryActions.ts:858)
    at processTicksAndRejections (internal/process/task_queues.js:93)
cheenamalhotra commented 2 years ago

Primary cause of this issue seems to be that when ADS makes multiple queries to SqlClient and server is not responsive, the connection resiliency design in SqlClient (enabled by default) to reconnect transparently turns into several minutes of delay.

If we can expose a new SqlConnection API that validates connection's active state, it can be used by ADS to check connection state before executing anything on this connection (same validation that SqlClient does currently for connection resiliency) e.g. when Azure connection drops, or there's a network issue.

ADS could then prompt end users when connection state is found dead, allowing user to choose to continue to reconnect/close the connection. The tricky part would be identifying when to prompt user about server's unresponsiveness v/s letting SqlClient silently reconnect (which will be more helpful for Azure connection drops).

Let me know if you have further insights!

cc @david-engel