Share via

Find Orchestration Details Using BizTalk Mgmt DB

  • Article

Introduction​

What if you have hundreds of orchestration deployed in your BizTalk environments and you need to go look up specific information like bindings end point used, subscriptions, state etc. It can be a really painful job to go look this up from BizTalk admin console. This article will provide you and alternate approach and obviously a quick one to get the required detail you need. This topic is intended towards Microsoft BizTalk server users and I have tested it to be working with BizTalk 2006 R2/ BizTalk 2009/ BizTalk 2010/ BizTalk 2013/ BizTalk 2013 R2. The person intending to use this need to be the part of BizTalk operator group or if it has to be used in a custom solution then the app id used shall have the admin access on BizTalk.

​Used Case

In one of my projects there were over 300 orchestrations used, at times there were random answer needs by the Development team/Support team related to orchestrations. With the access restrictions challenges it was decided to come up with a UI which can provide some basic information about the orchestrations hence eliminating the need for the users to use BizTalk mmc for the same purpose. Hence there was a custom SSRS report created and underlying was this SQL code returning the required result set. 
Following  BizTalkMgmtDB tables are used to get the information:

  • bts_sendport_transport
  • bts_orchestration_port_binding
  • bts_orchestration_port
  • bts_orchestration
  • adm_Host
  • bts_assembly
  • bts_application
  • bts_receiveport
  • bts_sendportgroup
  • bts_sendport

This SQL script is going to provide you the all the bindings and end point details for BizTalk orchestrations, it can be utilized in SSRS report or web based UI to provide end under with this information. It can as well come handy while deployment planing in terms of understanding which orchestration is dependent on what endpoints

Run the following SQL script on the BizTalkMgmtDB, the only prerequisite is you should be the part of BizTalk administrator group.
Information Extracted are following:

  1.  Orchestration Name : This is the Typename of the orchestration
  2. nVersionMajor : Major assembly version - in case you have more than one version of the same orchestration deployed.
  3. Host : The host your orchestration is running under.
  4. Orchestration Port Name : Logical port name of the orchestration
  5. Send Port : Physical send port.
  6. Address : Send Port endpoints
  7. Receive Port: Physical Receive Port name
  8. Application Name: Application container orchestration is deployed under.
  9. nvcFullName : Assembly Name.Typename 
  10. Assembly: Assembly Name of the BizTalk project Orchestration is written under
  11. ModifiedDate: It will tell when the orchestration was last modified. (Note: It will as well change if the orchestration was unenlisted and started)

https://psrathoud.files.wordpress.com/2016/08/924789.png?w=630

DECLARE @SendPortAddress TABLE
(nID int,
nvcAddress varchar(256))

Insert @SendPortAddress (nID, nvcAddress)

select nSendPortID, nvcAddress
From BizTalkMgmtDb.dbo.bts_sendport_transport AS spt WITH (NOLOCK) 
where len(nvcAddress) > 0

SELECT o.nvcName AS 'Orchestration Name'
, ao.nVersionMajor
, hst.Name AS 'Host'
, op.nvcName AS 'Orchestration Port Name'
, case when (sp.nvcName is null and rp.nvcName is Null) then spg.nvcName
  else sp.nvcName
  end as 'Send Port'
, spt.nvcAddress as 'Address'
, rp.nvcName as 'Receive Port'
, app.nvcName as 'Application Name'
, o.nvcFullName
, ao.nvcName as 'Assembly'
,o.dtModified as 'LastModified'
FROM BizTalkMgmtDb.dbo.bts_orchestration_port_binding AS b WITH (NOLOCK) INNER JOIN
     BizTalkMgmtDb.dbo.bts_orchestration_port AS op WITH (NOLOCK) ON b.nOrcPortID = op.nID INNER JOIN
     BizTalkMgmtDb.dbo.bts_orchestration AS o WITH (NOLOCK) ON o.nID = op.nOrchestrationID INNER JOIN
     BizTalkMgmtDb.dbo.adm_Host AS hst WITH (NOLOCK) ON o.nAdminHostID = hst.Id INNER JOIN     
     BizTalkMgmtDb.dbo.bts_assembly AS ao WITH (NOLOCK) ON o.nAssemblyID = ao.nID LEFT OUTER JOIN
     BizTalkMgmtDb.dbo.bts_application AS app WITH (NOLOCK) ON ao.nApplicationID = app.nID LEFT OUTER JOIN
     BizTalkMgmtDb.dbo.bts_receiveport AS rp WITH (NOLOCK) ON b.nReceivePortID = rp.nID LEFT OUTER JOIN
     BizTalkMgmtDb.dbo.bts_sendportgroup AS spg WITH (NOLOCK) ON b.nSpgID = spg.nID LEFT OUTER JOIN     
     BizTalkMgmtDb.dbo.bts_sendport AS sp WITH (NOLOCK) ON b.nSendPortID = sp.nID LEFT OUTER JOIN
     @SendPortAddress AS spt ON sp.nID = spt.nID
Order by app.nvcName, o.nvcName, ao.nVersionMajor, op.nID

Source Code Download