How to get Query Notification for events in the BizTalkMgmtDb?

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › How to get Query Notification for events in the BizTalkMgmtDb?

Viewing 2 reply threads
  • Author
    Posts
    • #24955

      Hi.

      I’m trying to discern the best way to get query notifications from SQL Server to discover database changes in the BizTalkMgmtDb,  when administrators/IT-Pros makes changes to the various artifacts and their properties in the different biztalk applications via the admin console.

      When changes occur to the BizTalkMgmtDb I need to be notified about the change (that an event occurred in a database) and then I need to make a query that identifies which application that was changed + which artifacts and properties that was changed within that application (which table changed, which row changed, or which data changed), by an admin person via the console.

      I need to do this in order to update a configuration and change management system in our enterprise, but Im not sure how to do this in the best way, since all the different artifacts in an application has its own table in the BizTalkMgmtDb, which makes a query quite complex (and im not good at SQL queries).

      I have read Richard Seroter’s article, amongst many others, but I still find my scenario of a more complex nature that I need to get some ideas/help on.

      If anyone has some thoughts or clever ideas how to go about this challenge it would be much appreciated.

      Thanks.

      -M.P

    • #24956

      Hi MP,

       

         Here are some suggestions that you might want to consider:

       

      1. A BizTalkGurus blog on SQL scripts (that can get you started on):

      http://www.biztalkgurus.com/blogs/biztalksyn/archive/2005/12/18/BizTalk-SQL-Scripts-for-Troubleshooting-Deployments.aspx

       

      2. A blog on developing custom tools for BizTalk:

      http://blogical.se/blogs/mikael/archive/2009/12/14/developing-custom-tools-for-biztalk.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+wmmihaa+%28Mikael+H%C3%A5kanssons+Blog%29

       

      3. Have you looked into BizTalkFactory PowerShell Provider for you administrative needs? Here’s a link to the main page on CodePlex:

      http://psbiztalk.codeplex.com/

       

         I hope these resources are helpful to you,

       

         Daniel.

       

    • #25087

      I have played around with this for a couple of days and I have made a SQL script (not finished yet) that reads out the exact same information as you would have under each “Application” in the Biztalk Server Admin console as an XML file from the BizTalkMgmtDb. This gives me the opportunity to read out and compare the data from different points of time during a day or week.

      Here is the script, which still needs quite a bit of tweaking before it is 100% and can be used. Maybe it can be of interest to others…

      ####################################################################################

       use BizTalkMgmtDb

      declare @applicationId int
      select
       application.nId   as “@Id”
       
      — , ____.nId  as “@assemId” 
       , application.nvcName as “@appName”
       , application.DateModified as “DateModified”  
      ————————————————————————————————
      —  Orchestration
      ————————————————————————————————
       ,(
        select
          Orchestration.orcName   as “@Name”
         , Orchestration.Modified  as “DateModified”     
         , Orchestration.assemName  as “assemName”

         , bindings
        from
         (
         select app.nID    as appId
           , ____.nID   as assemId
           ,app.nvcName  as appName
           , orcestration.nvcFullName as orcName
           , orcestration.dtModified as  Modified
           , ____.nvcFullName as assemName
           , (      —  Orchestration – bindings
            select
               p.nvcName   as “@Id”
              , case
               when sendt.nId is not null then sendt.nvcName
               when resc.nId  is not null then resc.nvcName
               when spg.nId is not null then spg.nvcName
               else ‘mangler’
                 end as “@Port”
            from  dbo.bts_application app
            left join dbo.bts_assembly ____   on ____.nApplicationID = app.nID
            inner join dbo.bts_orchestration orc on  ____.nID = orc.nAssemblyID
            left join dbo.bts_orchestration_port p on p.nOrchestrationID = orc.nId
            left join dbo.bts_porttype pt   on p.nPortTypeID = pt.nID
            inner join dbo.bts_orchestration_port_binding bind on bind.nOrcPortID = p.nID
            left join dbo.bts_sendport sendt  on sendt.nId = bind.nSendPortID and sendt.nApplicationID = app.nID
            left join dbo.bts_receiveport resc  on resc.nID = bind.nReceivePortID and resc.nApplicationID = app.nID
            left join dbo.bts_sendportgroup spg  on spg.nID = bind.nSpgID and spg.nApplicationID = app.nID
            where app.nID = application.nId
            and orcestration.nId = orc.nID
            for xml path(‘LogicalPort’),type) ‘Bindings’
         
           , ‘Orchestration’ as bizTalkPart
         from dbo.bts_application app
         left join dbo.bts_assembly ____   on ____.nApplicationID = app.nID
         inner join dbo.bts_orchestration orcestration on  ____.nID = orcestration.nAssemblyID
         where app.nID = application.nId
        ) as Orchestration
       for xml path(‘Orchestration’),type) ‘Orchestrations’
      ————————————————————————————————
      — Send Port Groups
      ————————————————————————————————
       ,(
        select
         SendPortGroups.portGroupName as “@portGroupName”
         , SendPortGroups.DateModified as “DateModified” 
         , transports
        from
        (
         select 
           app.nID   as appId
           , a.nID   as assemId
           ,app.nvcName as appName
           , a.nvcFullName as asseName
           , sendPortgr.DateModified as  DateModified
           , sendPortgr.nvcName as portGroupName
           , ( select distinct — Send Port Groups properties
             sp.nvcName as “@Name”
              ,case
              when st.bIsPrimary = 0 then ‘Backup’
              when st.bIsPrimary = 1 then  ‘Primary’
               end   as transport
             , adp.Name  as type
             , st.nvcAddress as URI
             , pipeRecive.Name as RecivePipeline
             , pipeSend.Name as SendPipeline
            from dbo.bts_application app
            left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
            left join dbo.bts_porttype p on p.nAssemblyID = a.nID
            inner join dbo.bts_sendportgroup spg on spg.nApplicationID = app.nID
            inner join dbo.bts_spg_sendport spgs on spgs.nSendPortGroupID = spg.nID
            inner join dbo.bts_sendport sp on sp.nID = spgs.nSendPortID and sp.nApplicationID = app.nID
            inner join dbo.bts_sendport_transport st on sp.nId = st.nSendPortID
            inner join dbo.adm_Adapter adp on adp.Id = st.nTransportTypeId
            left join dbo.bts_pipeline pipeSend on pipeSend.Id = sp.nSendPipelineID
            left join dbo.bts_pipeline pipeRecive on pipeRecive.Id = sp.nReceivePipelineID
            where app.nID = application.nId
            and sendPortgr.nID = spg.nID
            
           for xml path(‘TransportType’),type) ‘transports’
           , ‘SendPortGroups’ as bizTalkPart    
         from dbo.bts_application app
         left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
         inner join dbo.bts_sendportgroup sendPortgr on sendPortgr.nApplicationID = app.nID
         where app.nID = application.nId
        ) as SendPortGroups
       for xml path(‘SendPortGroup’),type) ‘SendPortGroups’
      ————————————————————————————————
      — Send Ports
      ————————————————————————————————
       ,(
        select
         SendPorts.sendPortName  as “@sendPortName”
         , SendPorts. DateModified as “DateModified”
         , transports
        from
         (
         select 
           app.nID   as appId
           , a.nID   as assemId
           , app.nvcName as appName
           , a.nvcFullName as  assemName
           , sendPort.nvcName as sendPortName
           , sendPort.DateModified as DateModified
           , ‘SendPorts’ as bizTalkPart
           , (  — Send Ports transport
            select distinct
              case
              when st.bIsPrimary = 0 then ‘Backup’
              when st.bIsPrimary = 1 then ‘Primary’
               end  as “@Type”
             , adp.Name  as type
             , st.nvcAddress as URL 
             , pipeRecive.Name as RecivePipeline
             , pipeSend.Name as SendPipeline
            from dbo.bts_application app
            left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
            left join dbo.bts_porttype p on p.nAssemblyID = a.nID
            inner join dbo.bts_sendport sp on sp.nApplicationID = app.nID
            left join dbo.bts_sendport_transport st on sp.nId = st.nSendPortID
            left join dbo.bts_sendport_transform tr on sp.nId = tr.nSendPortID
            left join dbo.bts_spg_sendport spgs on spgs.nSendPortID = sp.nId
            left join dbo.adm_Adapter adp on adp.Id = st.nTransportTypeId
            left join dbo.bts_pipeline pipeSend on pipeSend.Id = sp.nSendPipelineID
            left join dbo.bts_pipeline pipeRecive on pipeRecive.Id = sp.nReceivePipelineID
            where app.nID = application.nId 
            and sendPort.nId = sp.nId      
           for xml path(‘transport’),type) ‘transports’
         from dbo.bts_application app
         left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
         inner join dbo.bts_sendport sendPort on sendPort.nApplicationID = app.nID
         where app.nID = application.nId
         ) AS SendPorts
        for xml path(‘SendPort’),type) ‘SendPorts’
      ————————————————————————————————
      — Receive Ports
      ————————————————————————————————
       ,(select 
           resp.nvcName as “@reciverPortName”
          , resp.DateModified as DateModified
          , (
           select
           rl.Name   as “reciveLocationName”
           , rl.InboundTransportURL as URI
           , adp.Name  as type
           , rp.nvcName  as recivePort
           , host.Name   as reciveHandler
           , pipeRecive.Name as RecivePipeline
           , pipeSend.Name as SendPipeline
           from dbo.bts_application app
           inner join dbo.bts_receiveport rp on rp.nApplicationID = app.nID
           left join dbo.bts_receiveport_transform tr on rp.nId = tr.nReceivePortID
           left join dbo.adm_ReceiveLocation rl on rl.ReceivePortId = rp.nID
           left join dbo.adm_ReceiveHandler rh on rh.Id = rl.ReceiveHandlerId
           left join dbo.adm_Host host on host.Id = rh.HostId
           left join dbo.bts_pipeline pipeSend on pipeSend.Id = rl.SendPipelineID
           left join dbo.bts_pipeline pipeRecive on pipeRecive.Id = rl.ReceivePipelineID
           left join dbo.adm_Adapter adp on adp.Id = rl.AdapterId
           where app.nID = application.nId
           and resp.nID = rp.nID
           order by rl.Name
          for xml path(‘ReciveLocation’),type) ReciveLocations
         from dbo.bts_application app
         inner join dbo.bts_receiveport resp on resp.nApplicationID = app.nID
         where app.nID = application.nId
       for xml path(‘ReceivePort’),type) ‘ReceivePorts’
      ————————————————————————————————
      — Receive Locations
      ————————————————————————————————
       ,( 
        select
         ReceiveLocations.reciveLocationName as “@reciveLocationName”
         , ReceiveLocations.DateModified   as “DateModified”
         , ReceiveLocations.URI     as “URI”
         , ReceiveLocations.type     as “type”
         , ReceiveLocations.recivePort   as “recivePort”
         , ReceiveLocations.reciveHandler  as “reciveHandler”
         , ReceiveLocations.RecivePipeline  as “RecivePipeline”
         , ReceiveLocations.SendPipeline   as “SendPipeline”
        from (
         select 
          app.nID    as appId
          , a.nID    as assemId
          , app.nvcName  as appName
          , a.nvcFullName  as  assemName
          , rl.Name   as reciveLocationName
          , rl.DateModified as DateModified
          , rl.InboundTransportURL as URI
          , adp.Name   as type
          , rp.nvcName  as recivePort
          , host.Name   as reciveHandler
          , ‘ReceiveLocations’ as bizTalkPart
          , pipeRecive.Name as RecivePipeline
          , pipeSend.Name as SendPipeline
         from dbo.bts_application app
         left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
         inner join dbo.bts_receiveport rp on rp.nApplicationID = app.nID
         left join dbo.bts_receiveport_transform tr on rp.nId = tr.nReceivePortID
         left join dbo.adm_ReceiveLocation rl on rl.ReceivePortId = rp.nID
         left join dbo.adm_ReceiveHandler rh on rh.Id = rl.ReceiveHandlerId
         left join dbo.adm_Adapter adp on adp.Id = rh.AdapterId
         left join dbo.adm_Host host on host.Id = rh.HostId
         left join dbo.bts_pipeline pipeSend on pipeSend.Id = rl.SendPipelineID
         left join dbo.bts_pipeline pipeRecive on pipeRecive.Id = rl.ReceivePipelineID
         where app.nID = application.nId
        ) as ReceiveLocations
       for xml path(‘ReceiveLocation’),type) ‘ReceiveLocations’
      ————————————————————————————————
      — Schemas
      ————————————————————————————————
       ,(
        Select
           Schemas.FullName as “@targetNamespace”
         , Schemas.DateModified as “DateModified”
          , Schemas.rootName   as “RootName”
         
          , Schemas.SchemaTypes as “SchemaType”
        from (
            select distinct
           app.nID  as appId
           , a.nID   as assemId
           , app.nvcName as appName
           , a.nvcFullName as  assemName
           , spc.date_modified as “DateModified”
           ,spc.schema_root_name as rootName
           , ‘Schemas’  as bizTalkPart
           ,’http://’ + item.FullName as FullName
           ,case
           when SchemaType = 1 then ‘Property’
           when SchemaType = 0 then ‘Document’
           end as SchemaTypes
           –, *
         from dbo.bts_application app
         left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
         left join dbo.bt_DocumentSpec spc on a.nID = spc.assemblyid
         inner join dbo.bt_XMLShare xmls on xmls.id = spc.shareid
         left join dbo.bts_item item on item.id = spc.itemid and item.AssemblyId = spc.assemblyid
         where app.nID = application.nId
         and property_clr_class is null
         
         UNION

         select distinct
            app.nID  as appId
           , a.nID   as assemId
           , app.nvcName as appName
           , a.nvcFullName as  assemName
           , spc.date_modified as “DateModified”
           , ‘ ‘ as rootName
           , ‘Schemas’  as bizTalkPart
           ,’http://’ + item.FullName
           ,case
           when SchemaType = 1 then ‘Property’
           when SchemaType = 0 then ‘Document’
           end as SchemaTypes
           –, *
         from dbo.bts_application app
         left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
         left join dbo.bt_DocumentSpec spc on a.nID = spc.assemblyid
         inner join dbo.bt_XMLShare xmls on xmls.id = spc.shareid
         left join dbo.bts_item item on item.id = spc.itemid and item.AssemblyId = spc.assemblyid
         where app.nID = application.nId
         and item.SchemaType = 1
        ) as Schemas
       for xml path(‘Schema’),type) ‘Schemas’
      ————————————————————————————————
      — Maps
      ————————————————————————————————
       ,(
        Select
         Maps.mapName   as “@mapName”
         , Maps.DateModified  as “DateModified”
         , Maps.sourceSchema  as “sourceSchema”
         , Maps.targetSchema  as “targetSchema”
        from (

         select  app.nID  as appId
           , a.nID   as assemId
           ,app.nvcName as appName
          , a.nvcFullName  as asseName
          , map.date_modified as DateModified
          , item.fullName  as mapName
          , map.indoc_docspec_name as sourceSchema
          , map.outdoc_docspec_name as targetSchema
          , ‘Maps’   as bizTalkPart  
         from dbo.bts_application app
         left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
         left join dbo.bts_item item on item.AssemblyId = a.nID
         inner join dbo.bt_MapSpec map on map.itemid = item.id and map.assemblyid = a.nID
         where app.nID = application.nId
        ) as Maps
       for xml path(‘Map’),type) ‘Maps’
      ————————————————————————————————
      — Pipelines
      ————————————————————————————————
       ,(
        Select
         Pipelines.pipelineName   as “@pipelineName”
        from (

         select  app.nID  as appId
           , a.nID   as assemId
           , app.nvcName as appName
           , a.nvcFullName as asseName
           , pipe.Name  as pipelineName
           , ‘Pipelines’ as bizTalkPart 
         from dbo.bts_application app
         left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
         inner join dbo.bts_pipeline pipe on pipe.nAssemblyID = a.nID
         where app.nID = application.nId
        ) as Pipelines
       for xml path(‘Pipeline’),type) ‘Pipelines’
       
      ————————————————————————————————
       from dbo.bts_application application
      — left join dbo.bts_assembly ____ on ____.nApplicationID = app.nID
      — where ____.nApplicationID = @applicationId
      order by application.nvcName
      FOR XML path (‘Application’), ROOT(‘BizTalk’)

      ####################################################################################

      – M.P

Viewing 2 reply threads
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.