powershell - 如何从另一台服务器远程获取Biztalk应用程序列表

Question

我有以下代码来获取ServerB中的Biztalk应用程序。如果我在服务器B中运行它，则工作正常。如何从服务器A运行相同的脚本，并从服务器B获取Biztalk应用程序。调用命令远程处理似乎不起作用。

# Get local BizTalk DBName and DB Server from WMI
$btsSettings = get-wmiobject MSBTS_GroupSetting -namespace 'root\MicrosoftBizTalkServer'
$dbInstance = $btsSettings.MgmtDbServerName
$dbName = $btsSettings.MgmtDbName

# Load BizTalk ExplorerOM
[void] [System.reflection.Assembly]::LoadWithPartialName("Microsoft.BizTalk.ExplorerOM")
$BizTalkOM = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer
$BizTalkOM.ConnectionString = "SERVER=$dbInstance;DATABASE=$dbName;Integrated Security=SSPI"

$apps = $BizTalkOM.Applications | Select-Object Name,Status | Where-Object {$_.name -in 'BT.Order.CustomerOrder','BT.Order.FControl'}
$apps

Answer 1

如果您没有安装或无法安装BizTalk WMI / ExplorerOM(考虑一下-您正在尝试在其中显式加载Microsoft.BizTalk.ExplorerOM dll)，则可以只使用Invoke-Sqlcmd即可-但请注意它比使用OM更脆弱，可能会破坏新版本，在生产中应格外谨慎，等等。。。如果正在使用动态发送端口，则它也可能不起作用(我没有部署任何动态发送端口)目前要测试)。

$sql = "SELECT app.nvcName as Name,
    CASE
        WHEN SUM(o.nOrchestrationStatus) IS NULL AND SUM(r.Disabled) IS NULL AND SUM(sprt.nPortStatus) IS NULL  THEN 'N/A'
        WHEN SUM(CASE WHEN o.nOrchestrationStatus IS NULL THEN 3 ELSE o.nOrchestrationStatus END) = COUNT(*) * 3 AND SUM(CASE WHEN r.Disabled IS NULL THEN 0 ELSE r.Disabled END) = 0          AND sum(CASE WHEN sprt.nPortStatus IS NULL THEN 3 ELSE sprt.nPortStatus END) = count(sprt.nPortStatus) * 3  THEN 'Started' 
        WHEN SUM(CASE WHEN o.nOrchestrationStatus IS NULL THEN 1 ELSE o.nOrchestrationStatus END) = count(*)     AND SUM(CASE WHEN r.Disabled IS NULL THEN -1 ELSE r.Disabled END) = -COUNT(*) AND sum(CASE WHEN sprt.nPortStatus IS NULL THEN 1 ELSE sprt.nPortStatus END) = count(*) THEN 'Stopped'       
        ELSE 'Partially Started' 
    END as Status
FROM bts_application app WITH(NOLOCK)
LEFT JOIN (
    SELECT asm.nApplicationID, orch.nvcName, orch.nOrchestrationStatus FROM bts_assembly asm WITH(NOLOCK)
    inner join bts_orchestration orch WITH(NOLOCK)
    on asm.nid = orch.nAssemblyID) o
ON o.nApplicationID = app.nID

LEFT JOIN bts_sendport sprt with(nolock)
ON sprt.nApplicationID = app.nID

LEFT JOIN (
    select rprt.nApplicationID, rprt.nvcName as rprtName, rloc.Name as rlocName, rloc.Disabled from bts_receiveport rprt with(nolock)
    INNER JOIN  adm_ReceiveLocation rloc with(nolock)
    on rprt.nID = rloc.ReceivePortId) r
on r.nApplicationID = app.nid

--WHERE app.nvcName IN ('BT.Order.CustomerOrder','BT.Order.FControl')
GROUP BY app.nvcName"

Invoke-Sqlcmd -ServerInstance 'localhost' -Database 'BizTalkMgmtDb' -Query $sql | Where-Object { $_.Name -in 'BT.Order.CustomerOrder','BT.Order.FControl'}

请注意，您也可以通过取消注释sql字符串中的 WHERE子句，来将结果限制在SQL级别。

并请注意，如果您只是想要应用程序而不关心它们的状态，则可以将 大大简化为，它就像 Select nvcName from bts_application一样。