SQL Server – How to Set HOST_NAME() Value in Stored Procedure

One thing you could do is just set the ServerConnection portion of the Server object to an explicit connection string, specifying the port name.

You could either do this by accessing the Server.ConnectionContext property:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server

$SqlServer.ConnectionContext.ConnectionString = $ConnectionString

$SqlServer.Databases |
    Select-Object Name

Or by passing the ServerConnection object in when you instantiate the Server object:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConnection.ConnectionString = $ConnectionString

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerConnection)

$SqlServer.Databases |
    Select-Object Name

EDIT: I just re-read your question, and it seems like one of your attempts is basically identical to my second one up above. I took your code and all I changed was the data source portion of your connection string, and it worked just fine for me:

$cnString = "Data Source=Server\instance,port;Initial Catalog=master;Trusted_Connection=true;"
$cn = new-object system.data.sqlclient.sqlconnection($cnstring)
$cnSql = New-Object Microsoft.sqlserver.management.common.serverconnection($cn)
$s = New-Object Microsoft.sqlserver.management.smo.server($cnSql)
$s | Select Name, Version

First, CONTEXT_INFO is a property of the session, not the connection. It gets reset by sp_reset_connection when the same session is reused and the first batch is executed. It seems that CONTEXT_INFO was not reset in SQL Server 2000 and possibly earlier versions, but starting with SQL Server 2005 it is definitely reset to NULL.

Part of the confusion here is that the question is specific to "Microsoft Dynamics AX" since general .NET programming wouldn't have the registry key noted in that blog article. Also, the information that Microsoft Dynamics is then storing in CONTEXT_INFO is its application session details, which have nothing to do with SQL Server SPIDs and cannot be used to infer that connection pooling is occurring since the application session will naturally span multiple connections as well as SPIDs.

The mechanism being used to set CONTEXT_INFO pretty much has to be a separate, additional query executed prior to any other queries for that session. Something along the lines of:

SqlConnection _Connection = new SqlConnection("{connection-string}");
_Connection.Open();

if(_IsConnectionContextRegistryKeySet)
{
  SqlCommand _Command = _Connection.CreateCommand();
  _Command.CommandType = CommandType.Text;

  _Command.CommandText = @"DECLARE @BinaryInfo VARBINARY(128);
                           SET @BinaryInfo = CONVERT(VARBINARY(128), @StringInfo);
                           SET CONTEXT_INFO @BinaryInfo;";

  SqlParameter _ParamInfo = new SqlParameter("@StringInfo", SqlDbType.VarChar, 100);
  _ParamInfo.Value = String.Format("{0} {1} {2}...", AXuserID, AXsessionID, ...);
  _Command.Parameters.Add(_ParamInfo);

  _Command.ExecuteNonQuery();
  _Command.Dispose();
}

Hence, the small amount of additional overhead incurred for enabling this info to be set comes from the execution of this additional query.

Second, you can test for connection pooling by using SQL Server Profiler. Select the "RPC:Completed" event in the "Stored Procedures" category, make sure that "TextData", "ClientProcessID", and "SPID" are checked for that event (at the very least, you can select other columns if you like). Then, go to "Column Filters", select "TextData", and in the "Like" condition add the following condition: exec sp[_]reset[_]connection. Now run that trace. If you see instances of exec sp_reset_connection coming through, then that is due to connection pooling being used.

Additionally, there are two side-effects of connection pooling that may or may not show up in the DMVs depending on how many connections are being requested. The following query should capture many / most of the connections that are pooled (please note that it has nothing to do with CONTEXT_INFO):

SELECT sssn.login_time,
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       conn.*
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
WHERE conn.session_id <> conn.most_recent_session_id
OR    DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time) > 50
ORDER BY conn.connect_time;

This query looks for the following indications of connection pooling being used:

• Current session_id is not the same as the prior session_id. If these two IDs are the same then it may or may not be a connection that is using pooling, since the same SPID can be reused. But, if they are different, then that can only be the result of connection pooling.
• Time between the connection being made and time that the session starts is over 50 milliseconds (though that threshold might vary by system). Usually the first session to get created upon a connection is less than 30 milliseconds after the connection, but it "generally" shouldn't be above 50, even if executing multiple SqlCommands.

Along similar lines, it should also be possible to test for connection pooling by creating a temp table, and every few seconds, capturing both [session_id] (INT) and [connection_id] (UNIQUEIDENTIFIER) from sys.dm_exec_connections. Then, just look for rows that have the same connection_id but different session_id.

Finally, the query posted in the question is not valid for indicating connection pooling with respect to most web applications. The issue here is that typically, the properties of "program_name", "login_name", and "host_name" would be the same for all connections made by web/app server (hence the need for the per-processor / per-core licensing models instead of just having the CAL model).