Blogging BizTalk

Lets Discuss BizTalk

Find Orchestration Details Using BizTalk Mgmt DB


Introduction

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? 

924789.png

DECLARE @SendPortAddress TABLE 
(nID intnvcAddress varchar(256)) 
 
Insert @SendPortAddress (nIDnvcAddress) 
 
select nSendPortIDnvcAddress 
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 Nullthen 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' 
FROM BizTalkMgmtDB.dbo.bts_orchestration_port_binding AS b WITH (NOLOCKINNER JOIN 
     BizTalkMgmtDB.dbo.bts_orchestration_port AS op WITH (NOLOCKON b.nOrcPortID = op.nID INNER JOIN 
     BizTalkMgmtDB.dbo.bts_orchestration AS o WITH (NOLOCKON o.nID = op.nOrchestrationID INNER JOIN 
     BizTalkMgmtDB.dbo.adm_Host AS hst WITH (NOLOCKON o.nAdminHostID = hst.Id INNER JOIN      
     BizTalkMgmtDB.dbo.bts_assembly AS ao WITH (NOLOCKON o.nAssemblyID = ao.nID LEFT OUTER JOIN 
     BizTalkMgmtDB.dbo.bts_application AS app WITH (NOLOCKON ao.nApplicationID = app.nID LEFT OUTER JOIN 
     BizTalkMgmtDB.dbo.bts_receiveport AS rp WITH (NOLOCKON b.nReceivePortID = rp.nID LEFT OUTER JOIN 
     BizTalkMgmtDB.dbo.bts_sendportgroup AS spg WITH (NOLOCKON b.nSpgID = spg.nID LEFT OUTER JOIN      
     BizTalkMgmtDB.dbo.bts_sendport AS sp WITH (NOLOCKON b.nSendPortID = sp.nID LEFT OUTER JOIN 
     @SendPortAddress AS spt ON sp.nID = spt.nID 
Order by app.nvcNameo.nvcNameao.nVersionMajorop.nID
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

D Goins Insperience

Technological outformation for this day and age

[INACTIVE BLOG] Connected Thoughts - Thiago Almeida

Connected systems and the occasional picture

Uri Katsir's Blog

BizTalk , BizTalk RFID and .NET

prashantbiztalkblogs

My BizTalk Experiences

BizTalk Server Tutorial

BizTalk Server Concepts and Common Errors

Extremely Talented Monkeys

A Technical Blog by Ed Jones: Azure, .NET, BizTalk, WCF, and SQL Server

On All Things Web

Discussing web development without limits

Vikas Bhardwaj's Blog

Articles about BizTalk Server and .NET C#

Cloud develop

a blog about (cloud) development... because I'm a nerd

Hooking Stuffs Together

My learning logs from day to day work experience about Integration platform using Microsoft technologies.

Connected Pawns

Mainly BizTalk & Little Chess

jhelatissimo

a blogger in the process.

MS Innovations Blog

Tips, Tricks, and Workarounds for BizTalk and other Microsoft technologies

srirambiztalks

Katradhu Kaialavu,Kalladhadhu Ulagalavu!

Vijay Microsoft Technical

BizTalk, WCF, ESB ToolKit, Windows Azure

Mind Over Messaging

Musings on BizTalk, Azure, and Enterprise Integration

%d bloggers like this: