none
Do we need to Release SQL Connection when using SQL Adapter in BizTalk Server 2006 RRS feed

  • Question

  • Hi All,
    I have a general query about SQL Adapter.
    I am getting data from SAP RFC using SAP adapter.
    this data i store in SQL table using SQL adapter.

    Also i one of the application i use SQL adapter at receive port and polling interval is 30 seconds as i need to get Xml from SQL and send this to SAP ..

    Now i there anything i need to release connection when i use SQL adapter(I am using Send-Receive Port).
    SQL is on different server and paging memory we see is around 10GB ..and RAM installed is 12GB
    So when we restart the SQL Services the paging memory come down to 4GB..
    So i was thinking would this might be the case that SQL adpater hold the connection..
    Pls Advise..

    Thanks
    Sandeep
    Monday, July 20, 2009 5:10 AM

Answers

  • Well Microsoft is managing SQL Connection quite efficiently and you need not to bother about the releasing any SQL connection.

    It maintains connection pool (disposes the connection after some sleep time) and as you have Send-Receive port in that case it will reuse the existing connection else will create new connection object and handle the request.

    I would suggest to have separate host instance for SQL and SAP(send and receive port) for load balancing. Also check your code for any memory leak and try to dispose Objects as soon as you are done with them.
    Ajeet Kumar
    Monday, July 20, 2009 8:25 AM
  • You can use SQL Server Profiler to check out whats going wrong for you. I am not sure about the default sleep interval (I guess something around 5 mins). You can make use of some of the properties available for SQL Adapter (on All page of Transport page in Administrative console).

    Also check this link http://technet.microsoft.com/en-us/library/cc738560(WS.10).aspx . Also you can run sp_who2 and see the total current SQL connections and its Status.

    Ajeet Kumar
    Monday, July 20, 2009 9:40 AM

All replies

  • Well Microsoft is managing SQL Connection quite efficiently and you need not to bother about the releasing any SQL connection.

    It maintains connection pool (disposes the connection after some sleep time) and as you have Send-Receive port in that case it will reuse the existing connection else will create new connection object and handle the request.

    I would suggest to have separate host instance for SQL and SAP(send and receive port) for load balancing. Also check your code for any memory leak and try to dispose Objects as soon as you are done with them.
    Ajeet Kumar
    Monday, July 20, 2009 8:25 AM
  • Hi Ajeet,
    Thanks for your reply..
    Is there any tool which shows me the resourses used by SQL Adapter..
    Also Ajeet what would be the sleep interval after which BizTalk releases the connection to SQL

    Thanks
    Sandeep
    Monday, July 20, 2009 8:51 AM
  • You can use SQL Server Profiler to check out whats going wrong for you. I am not sure about the default sleep interval (I guess something around 5 mins). You can make use of some of the properties available for SQL Adapter (on All page of Transport page in Administrative console).

    Also check this link http://technet.microsoft.com/en-us/library/cc738560(WS.10).aspx . Also you can run sp_who2 and see the total current SQL connections and its Status.

    Ajeet Kumar
    Monday, July 20, 2009 9:40 AM