Find Orchestration Details Using BizTalk Mgmt DB
August 14, 2014
Posted by on
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 inteding to use this need to be the BizTalk administrator.
What if you have hundrebs of orchestration deployed in your BizTalk enviroinments and you need to go look up specific information like binidings end point used, sunbscriptions, state etc. It can be a realy 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. I am leveraging the BizTalk Mgmt DB to find the information outlined below.
This SQL script is goign 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 uder with this information.IT can come realy handly while deployment planing in terms of understanding whihc orchestrationn is dependent on what endpointso
Building the Sample
Run the followinf SQL script on the BizTalkMgmtDB, the only prerequisite is you should be the part of BizTalk adminstrator group?
DECLARE @SendPortAddress TABLE
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'
, hst.Name AS 'Host'
, op.nvcName AS 'Orchestration Port Name'
, case when (sp.nvcName is null and rp.nvcName is Null) then spg.nvcName
end as 'Send Port'
, spt.nvcAddress as 'Address'
, rp.nvcName as 'Receive Port'
, app.nvcName as 'Application Name'
, ao.nvcName as 'Assembly'
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