0

I have a C# app that connects to an 8 node SQL Server Always-On cluster. We are using the ApplicationIntent=ReadOnly parameter and the Listener IP in the connection string to allow connections to all servers for queries.

We are logging, within the app, the elapsed time it takes for stored procedures to execute and return results. In the logs we would also like to capture the exact server the connection was made to. When an exception is thrown, there is a "Routing Destination" in the stack trace that gives the exact server being connected to instead of the Listener.

I have seen in the SQL Client code that the Routing Destination is coming from an Internal Connection, but the object seems to be protected, internal, or private whenever it's used.

Has anyone figured out how to get to this Internal Connection object or the Routing Destination? Or does anyone know another way to get the true SQL Server name from someplace else?

As requested, here is a sample method. I would want to get the actual server name that was connected to and log it in the Catch block:

public static async Task<int> InserStat(Statistic stat)
    {
        List<SqlParameter> parameters = new List<SqlParameter>();
        int returnCode = -1;
        try
        {
            parameters.Add(new SqlParameter() { ParameterName = StoredProcParamNames.Account, Value = stat.Account, DbType = DbType.String, Direction = ParameterDirection.Input });
            parameters.Add(new SqlParameter() { ParameterName = StoredProcParamNames.DataSource, Value = stat.Datasource, DbType = DbType.String, Direction = ParameterDirection.Input });
            parameters.Add(new SqlParameter() { ParameterName = StoredProcParamNames.Indicator, Value = stat.Listed, DbType = DbType.Boolean, Direction = ParameterDirection.Input });
            parameters.Add(new SqlParameter() { ParameterName = StoredProcParamNames.ServiceIndicator, Value = stat.Service, DbType = DbType.Boolean, Direction = ParameterDirection.Input });
            parameters.Add(new SqlParameter() { ParameterName = StoredProcParamNames.TimeStamp, Value = stat.TransactionTime, DbType = DbType.DateTime2, Direction = ParameterDirection.Input });
            parameters.Add(new SqlParameter() { ParameterName = StoredProcParamNames.ReturnValue, DbType = DbType.Int32, Direction = ParameterDirection.ReturnValue });

            using (SqlConnection connection = await dataManager.GetWriteBackConnectionAsync())
            using (SqlCommand command = dataManager.SetupDbCommand("0", StoredProcNames.InsertStat, connection, parameters) as SqlCommand)
            {
                await command.ExecuteNonQueryAsync();
                returnCode = (int)parameters[parameters.Count - 1].Value; // Return code is the last parameter in the list
            }
        }
        catch (Exception e)
        {
            Dictionary<string, object> loggingDetails = PopulateLoggingDetails(parameters);
            Logger.WriteException(e, VNRCategory.DAC, -1, TraceEventType.Error, StoredProcNames.InsertStat, loggingDetails);
        }
        if (returnCode != 0)
            Logger.WriteInfoLog("Insert return code: " + returnCode, Category.DAC);
        return returnCode;
    }
JSD
  • 1
  • 1
  • Have you tried using @@SERVERNAME in the query to see if you can look up the true server you are connected to? – Jawad Dec 02 '19 at 17:15
  • I can do that in the stored procedure, but not in the C# code. I'm logging in C#. – JSD Dec 03 '19 at 16:21
  • can you provide sample code for your sql execution and response methods. – Jawad Dec 03 '19 at 17:58

0 Answers0