none
BizTalk: Adding ApplicationName keyword and value into WCF-SQL Adapter connection string? RRS feed

  • Question

  • Hello,

    We are monitoring the activities on our services exposed by sqlserver to know who is using what. It helps us to make decision on how/when to upgrade views and storedprocs based on usage and also to troubleshoot incidents.

    In our environment, other than users using the services (views and storedproc), we have many BizTalk applications that also consume the services. In the reporting tool (a cube), we can see service consumption by servicename/user/application/hostname/time and all.

    However, BizTalk Server has separate hosts using the same credentials and application sharing the same hosts. Thus, in the reporting tool, one cannot determine which BizTalk application port is using that specific service since they all have the same loginame and program_name on the sql server side (see master.sys.sysprocesses view).

    We could review our host configuration and add hosts with different credentials and assign them to the rcv or snd ports. But this path can quickly becomes a configuration hell (declaring host, add hosts instances, assign adapters to hosts, review host distribution by ports and ...)

    Instead, our DBA ask us to modify/add the "Application Name" keyword in the connection string and assign the BizTalk application name. Sounds easy. Unfortunately, this keyword is not accessible from WCF-SQL binding. Strangely, the WorkstationId, another connection string keyword, can be set from the bindings. But it is somehow wrong to use it for something other than hostname. And it would look awkward in the reports.

    So, is there a way to modify and add the applicationname keyword in the connection string of a WCF-SQL adapter (BizTalk Adapter Pack 2.0 CU5)? I did investigate and review Microsoft.Adapters.SQL code. I cannot understand the relation between WCF Channel and SqlAdapterConnectionUri classes. The latter seem the place where to intervene. But it may already to deep down into the mechanism.

    And why WorkstationId is in the binding and not ApplicationName? To me, it make more sense to customize the AppName and not the hostname, right?

    Thanks,

    Etienne


    Friday, February 22, 2013 4:07 PM

All replies

  • Hi,

    I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Best Regards,


    Chen Yu
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 26, 2013 6:32 AM
    Moderator
  • I do not think it is possible to send in an application name like this. I understand how this simplifies the analysis from a DBA perspective, but I could not find a way to do this with the WCF SQL connectivity properties.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline

    Monday, March 4, 2013 4:23 PM
    Moderator
  • Hi Etienne,

    As far as I know, the "Application Name" is not exposed from the WCF-SQL Adapter URI configuration.  The following link from MSDN detailed the URI properties exposed.
    SQL Server Connection URI

    The following details a completely defined WCF-SQL Adapter  URI.

    mssql://[Server_Name[:Portno]]/[Database_Instance_Name]/[Database_Name]?FailoverPartner=[Partner_Server_Name]&InboundId=[Inbound_ID]

    You should be able to to use the InboundId property to contain your BizTalk Application Name. In SQL Server your DBA should be able to extract the value from

    sys.sysprocess. I will test this out and report the result back to this thread over the weekend.

    Thanks,

    William



    • Edited by William Cummings1 Friday, March 15, 2013 10:21 PM Cleanup/unwanted characters
    Friday, March 15, 2013 10:20 PM