Home
  •  

AppFabric SQL Instance Store Discovery

Microsoft.ApplicationServer.PersistenceStoreConnection.Discovery (Discovery)

This discovery discovers connections to SQL Workflow instance databases that are currently in use by AppFabric managed applications on the machine.

This is achieved by reading application configuration files.

Knowledge Base article:

Summary

This discovery discovers connections to SQL Workflow instance databases that are currently in use by AppFabric managed applications on the machine.

This is achieved by reading application configuration files.

Element properties:

TargetMicrosoft.ApplicationServer.WFManagementService
EnabledTrue
Frequency14800
RemotableFalse

Object Discovery Details:

Discovered Classes and their attribuets:
Discovered relationships and their attribuets:

Member Modules:

ID Module Type TypeId RunAs 
DS DataSource System.CommandExecuterDiscoveryDataSource Microsoft.ApplicationServer.StoreReaderAccount

Source Code:

<Discovery ID="Microsoft.ApplicationServer.PersistenceStoreConnection.Discovery" Enabled="true" Target="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.WFManagementService" ConfirmDelivery="true" Remotable="true" Priority="Normal">

  <Category>Discovery</Category>

  <DiscoveryTypes>

    <DiscoveryClass TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Service">

      <Property TypeID="System!System.Entity" PropertyID="DisplayName"/>

    </DiscoveryClass>

    <DiscoveryClass TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Stores">

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Stores" PropertyID="StoresName"/>

      <Property TypeID="System!System.Entity" PropertyID="DisplayName"/>

    </DiscoveryClass>

    <DiscoveryClass TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreConnection">

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreConnection" PropertyID="StoreName"/>

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreConnection" PropertyID="Location"/>

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoreConnection" PropertyID="ConnectionStringName"/>

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoreConnection" PropertyID="Provider"/>

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoreConnection" PropertyID="ConnectionStringHash"/>

      <Property TypeID="System!System.Entity" PropertyID="DisplayName"/>

    </DiscoveryClass>

    <DiscoveryClass TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStore">

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStore" PropertyID="DBIdentity"/>

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Store" PropertyID="ConnectionStringHash"/>

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Store" PropertyID="Provider"/>

      <Property TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Store" PropertyID="DataSource"/>

      <Property TypeID="System!System.Entity" PropertyID="DisplayName"/>

    </DiscoveryClass>

    <DiscoveryRelationship TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.WFManagementServiceHostsPersistenceStoreConnection"/>

    <DiscoveryRelationship TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.ServiceContainsStores"/>

    <DiscoveryRelationship TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoresContainsPersistenceStore"/>

    <DiscoveryRelationship TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreContainsPersistenceStoreConnection"/>

    <DiscoveryRelationship TypeID="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreConnectionReferencesPersistenceStore"/>

  </DiscoveryTypes>

  <DataSource ID="DS" RunAs="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoreReaderAccount" TypeID="System!System.CommandExecuterDiscoveryDataSource">

    <IntervalSeconds>14800</IntervalSeconds>

    <ApplicationName>%windir%\system32\windowspowershell\v1.0\powershell.exe</ApplicationName>

    <WorkingDirectory/>

    <CommandLine>-Command ".\Create-PersistenceStoreConnectionInfo.ps1" </CommandLine>

    <TimeoutSeconds>600</TimeoutSeconds>

    <RequireOutput>true</RequireOutput>

    <Files>

      <File>

        <Name>Create-PersistenceStoreConnectionInfo.ps1</Name>

        <Contents><Script>



function Get-WFManagementConfiguration

{

	Param([string]$wfManagementServiceName)

	$DefaultRuntimeVersion = "4.0"

	$WFManagementSectionName = "microsoft.applicationServer/persistence/workflowManagement/workflowManagementServiceInstances"	

	

		

	[Microsoft.Web.Administration.ConfigurationElement]$wfManagementConfiguration = $null

	

	$serverManager = new-object Microsoft.Web.Administration.ServerManager	

	[Microsoft.Web.Administration.Configuration]$rootWebConfiguration = $serverManager.GetWebConfiguration($null);

		

	$rootWebConfiguration.SetMetadata("defaultManagedRuntimeVersion", $DefaultRuntimeVersion);

	

	[Microsoft.Web.Administration.ConfigurationSection]$wfManagementSection = $rootWebConfiguration.GetSection($WFManagementSectionName);		

	[Microsoft.Web.Administration.ConfigurationElementCollection]$wfManagementInstances = $wfManagementSection.GetCollection();

	

	foreach ($wfManagementInstance in $wfManagementInstances)

	{

		[string]$currentManagementName = $wfManagementInstance.GetAttributeValue("name");

		if ($currentManagementName.CompareTo($wfManagementServiceName) -eq 0)

		{

			$wfManagementConfiguration = $wfManagementInstance;

			break;

		}

	}

	

	return $wfManagementConfiguration

	

	trap [Exception]

	{

		if ($script:oAPI -ne $null)

		{	

			foreach($errorDetail in $Error)

			{

				$script:oAPI.LogScriptEvent('Create-PersistenceStoreConnectionInfo.ps1', 101, 1, $errorDetail.Exception)

				Write-Host $errorDetail.Exception

			}

		}	

		

		return	$null	

	}

}



function Get-ConnectionString

{

	Param([string]$connectionStringName, [string]$siteName, [string]$virtualPath)

	$DefaultRuntimeVersion = "4.0"

	$ConnectionStringsSectionName = 'connectionStrings'

	$NameAttributeName = 'name'

	$ConnectionStringsAttributeName = 'connectionString'

        

    [string]$connectionString = $null;

	$serverManager = new-object Microsoft.Web.Administration.ServerManager	

	[Microsoft.Web.Administration.Configuration]$webConfig = Get-WebConfiguration -serverManager $serverManager -siteName $siteName -virtualPath $virtualPath

    	

	$webConfig.SetMetadata("defaultManagedRuntimeVersion", $DefaultRuntimeVersion);

    

    [Microsoft.Web.Administration.ConfigurationSection]$connectionStringSection = $webConfig.GetSection($ConnectionStringsSectionName)

    [Microsoft.Web.Administration.ConfigurationElementCollection]$connectionStringCollection = $connectionStringSection.GetCollection()

    if ($connectionStringCollection -ne $null)

    {

        foreach ($connectionStringElement in $connectionStringCollection)

        {

            [string]$name = $connectionStringElement.GetAttributeValue($NameAttributeName)

            if ((![string]::IsNullOrEmpty($name)) -and ($name.CompareTo($connectionStringName) -eq 0))

            {

                $connectionString = $connectionStringElement.GetAttributeValue($ConnectionStringsAttributeName)

				break

            }

        }

    }    

	

    return $connectionString

	

	trap [Exception]

	{

		if ($script:oAPI -ne $null)

		{	

			foreach($errorDetail in $Error)

			{

				$script:oAPI.LogScriptEvent('Get-ConnectionStringInfoObjects.ps1', 101, 1, $errorDetail.Exception)

				Write-Host $errorDetail.Exception

			}

		}	

		

		return	$null	

	}

}



function Get-WebConfiguration

{

	Param($serverManager, [string]$siteName, [string]$virtualPath)

        

    [Microsoft.Web.Administration.Configuration]$webConfig = $null;

    if ([string]::IsNullOrEmpty($siteName) -and [string]::IsNullOrEmpty($virtualPath))

    {

        $webConfig = $serverManager.GetWebConfiguration($null);

    }

    elseif (![string]::IsNullOrEmpty($siteName))

    {

		

		if ([string]::IsNullOrEmpty($virtualPath))

		{

			$virtualPath = '/'

		}	

	

        $webConfig = $serverManager.GetWebConfiguration($siteName, $virtualPath)

    }

    else

    {

        throw "Invalid configuration at " + $siteName + "/" + $virtualPath;

    }

	

    return $webConfig;

}



function NormalizeScopePath

{

    Param([string] $scope)

    [string]$normalizedScope = $scope;

    if (![string]::IsNullOrEmpty($scope))

    {

        # Trim whitespaces, leading and trailing '/' if any

        $normalizedScope = $scope.Trim();

        if ($normalizedScope[0] -eq '/')

        {

            $normalizedScope = $normalizedScope.Substring(1);

        }

        [int]$length = $normalizedScope.Length;

        if (($length -ge 1) -and ($normalizedScope[$length - 1] -eq '/'))

        {

            $normalizedScope = $normalizedScope.Substring(0, $length - 1);

        }

    }

	

    return $normalizedScope;

}



function Get-UniqueConnectionInfoObjectsFromInstanceStoreInfoObjects

{

	Param($instanceStoreInfoObjects)

	[Hashtable]$currentConnectionStringCache = New-Object Hashtable

	

	foreach ($instanceStoreInfoObject in $instanceStoreInfoObjects)

	{

		$parameters = $instanceStoreInfoObject.Group[0].Parameters

		$connectionString = $null		

		

		if ($parameters.Get('connectionString') -ne $null)

		{

			$connectionString = $parameters.Get('connectionString')

		}

		elseif ($parameters.Get('connectionStringName') -ne $null)

		{			

			$connectionString = Get-ConnectionString -connectionStringName $parameters.Get('connectionStringName') -siteName $instanceStoreInfoObject.Group[0].SiteName -virtualPath $instanceStoreInfoObject.Group[0].VirtualPath			

		}



		$uniqueKey = [string]::Concat($connectionString, ':', $instanceStoreInfoObject.Group[0].Name, ':', $instanceStoreInfoObject.Group[0].SiteName, $instanceStoreInfoObject.Group[0].VirtualPath)

		

		if ($connectionString -ne $null -and !$currentConnectionStringCache.Contains($uniqueKey))

		{			

			$currentConnectionStringCache.Add($uniqueKey, @($connectionString, $instanceStoreInfoObject.Group[0].Name, [string]::Concat($instanceStoreInfoObject.Group[0].SiteName, $instanceStoreInfoObject.Group[0].VirtualPath)))

		}		

	}

	

	return $currentConnectionStringCache

}



function Get-UniqueConnectionInfoObjects

{

	Param([string]$siteName, [string]$applicationPath, [string]$storeNameFilter)

	$connectionInfoObjects = $null

	$instanceStoreInfoObjects = $null

	

	if ([string]::IsNullOrEmpty($siteName) -or $siteName.CompareTo('Root') -eq 0)

	{		

		$instanceStoreInfoObjects = Get-AsApplication | Get-ASAppInstanceStore | where {$_.ProviderName -match "SqlPersistenceStoreProvider"} | where {$_.Name -match $storeNameFilter} | Group-Object Name, SiteName, VirtualPath  -ErrorAction SilentlyContinue		

	}

	else

	{

		$instanceStoreInfoObjects = Get-ASAppInstanceStore -SiteName $siteName -VirtualPath $applicationPath | where {$_.ProviderName -match "SqlPersistenceStoreProvider"} | where {$_.Name -match $storeNameFilter} | Group-Object Name, SiteName, VirtualPath  -ErrorAction SilentlyContinue

	}	 	

		

	if($instanceStoreInfoObjects -ne $null)

	{	

		$connectionInfoObjects = Get-UniqueConnectionInfoObjectsFromInstanceStoreInfoObjects -instanceStoreInfoObjects $instanceStoreInfoObjects

	}

	else

	{

		$script:oAPI.LogScriptEvent('Create-PersistenceStoreConnectionInfo.ps1', 200, 2, 'Unable to fetch any Persistence connection string info objects')

	}

	

    return $connectionInfoObjects;

}



function Get-SHA1Hash

{

	param([string]$stringValueToHash)	

	$hashAlgorithm = new-object System.Security.Cryptography.SHA1Managed

	$computedHashBytes = $hashAlgorithm.ComputeHash($([Char[]]$stringValueToHash.ToUpperInvariant()));



	$stringBuilder = New-Object System.Text.StringBuilder

	$computedHashBytes | Foreach-Object { [void] $stringBuilder.Append($_.ToString("X2")) }

	return $stringBuilder.ToString()

}



function Create-PersistenceStoreConnectionObjects

{

	Param([string]$connectionString, [string]$provider, [string]$connectionStringName, [string]$storeName, [string]$location, $discovery, $dbInfoCache)

	

	if ([string]::IsNullOrEmpty($location))

	{

		$location = 'Root'

	}   



    # Create Service contains db Servers relation

    $sourceService = $Discovery.CreateClassInstance("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Service']$")



    $sourceDBServers = $Discovery.CreateClassInstance("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Stores']$")

    $sourceDBServers.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Stores']/StoresName$", 'AppFabric Databases')

    $Discovery.AddInstance($sourceDBServers)



    $relation = $Discovery.CreateRelationshipInstance("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.ServiceContainsStores']$")

    $relation.Source = $sourceService

    $relation.Target = $sourceDBServers

    $Discovery.AddInstance($relation)

	

	$hashedConnectionString = Get-SHA1Hash -stringValueToHash $connectionString

	

    # Create Store Connection object

     $dbConnection = $discovery.CreateClassInstance("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreConnection']$")

     $dbConnection.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreConnection']/StoreName$", $storeName)	

     $dbConnection.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreConnection']/Location$", $location)	

     $dbConnection.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoreConnection']/ConnectionStringHash$", $hashedConnectionString)

     $dbConnection.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoreConnection']/ConnectionStringName$", $connectionStringName)

     $dbConnection.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoreConnection']/Provider$", $provider)	

     $dbConnection.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", '$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$')

     $dbConnection.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.LocalService']/ServiceName$", '$Target/Property[Type="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.LocalService"]/ServiceName$')

     $dbConnection.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", $connectionStringName)

     $discovery.AddInstance($dbConnection)

	

     if($dbInfoCache -ne $null -and $dbInfoCache.Contains($hashedConnectionString) -and 

		$dbInfoCache[$hashedConnectionString].PDbIdentity -ne $null)

     {

		$dbInfo = $dbInfoCache[$hashedConnectionString]		

		[string]$provider = $dbInfo.Provider

		[string]$dbIdentity = $dbInfo.PDbIdentity

		[string]$dbDataSource = $dbInfo.DataSource

		[string]$dbDatabaseName = $dbInfo.Database



		$discoveredDatabase = $discovery.CreateClassInstance("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStore']$")

		$discoveredDatabase.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Store']/ConnectionStringHash$", $hashedConnectionString)	

		$discoveredDatabase.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Store']/Provider$", $provider)

		$discoveredDatabase.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.Store']/DataSource$", $dbDataSource)

		$discoveredDatabase.AddProperty("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStore']/DBIdentity$", $dbIdentity)		

	    	

		$discoveredDatabase.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", $dbDatabaseName)

		$discovery.AddInstance($discoveredDatabase)



		# Add relation Stores contains PersistenceStore relation

		$relation = $Discovery.CreateRelationshipInstance("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.StoresContainsPersistenceStore']$")

		$relation.Source = $sourceDBServers

		$relation.Target = $discoveredDatabase

		$Discovery.AddInstance($relation)



		$relation = $Discovery.CreateRelationshipInstance("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreContainsPersistenceStoreConnection']$")

		$relation.Source =  $discoveredDatabase

		$relation.Target =  $dbConnection

		$discovery.AddInstance($relation) 



		$relation = $Discovery.CreateRelationshipInstance("$MPElement[Name='MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.PersistenceStoreConnectionReferencesPersistenceStore']$")

		$relation.Source =  $dbConnection

		$relation.Target =  $discoveredDatabase

		$discovery.AddInstance($relation)  

	}

}



$script:oAPI = new-object -comObject "MOM.ScriptAPI"

$Discovery = $script:oAPI.CreateDiscoveryData(0, '$MPElement$', '$Target/Id$')



#$script:oAPI.LogScriptEvent("Create-PersistenceStoreConnectionInfo.ps1", 123, 4, "Init")



[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Web.Administration") 

if ((Get-Command -Module ApplicationServer) -eq $null)

{

  Import-Module ApplicationServer # Application Server Extensions for .NET 4

}



[string]$DbInfoCacheFileName = "$env:ALLUSERSPROFILE\Microsoft AppFabric Management Pack\DbInfoCache.xml"

[Hashtable]$existingDbInfoCache = $null

if(test-path $DbInfoCacheFileName)

{	

    $existingDbInfoCache = Import-Clixml -Path $DbInfoCacheFileName

}

		

$InstanceStoreName = "name"

$InstanceStoreLocation = "location"

$WorkflowManagementServiceInstanceStoresElement = "instanceStores"



$wfManagementServiceName = '$Target/Property[Type="MicrosoftApplicationServerLibrary!Microsoft.ApplicationServer.WFManagementService"]/ManagementName$'

if ($wfManagementServiceName.CompareTo('Nameless_WMS_Instance') -eq 0)

{

	$wfManagementServiceName = [string]::Empty

}

	

[Microsoft.Web.Administration.ConfigurationElement]$wfManagementConfiguration = Get-WFManagementConfiguration -wfManagementServiceName $wfManagementServiceName

[Microsoft.Web.Administration.ConfigurationElement]$instanceStoresElement= $wfManagementConfiguration.ChildElements[$WorkflowManagementServiceInstanceStoresElement];	

[Microsoft.Web.Administration.ConfigurationElementCollection]$instanceStoreElementCollection = $null



if ($instanceStoresElement -ne $null)

{

    $instanceStoreElementCollection = $instanceStoresElement.GetCollection();    

}

					

if ($instanceStoreElementCollection -eq $null -or $instanceStoreElementCollection.Count -eq 0)

{

	$connectionInfoObjects = Get-UniqueConnectionInfoObjects -siteName  $null -applicationPath $null -storeNameFilter $null	

	foreach($connectionInfoObject in $connectionInfoObjects.Keys)

	{

		Create-PersistenceStoreConnectionObjects -connectionString $connectionInfoObjects[$connectionInfoObject][0] -provider 'System.Data.SqlClient' -connectionStringName $connectionInfoObjects[$connectionInfoObject][1] -storeName $connectionInfoObjects[$connectionInfoObject][1] -location $connectionInfoObjects[$connectionInfoObject][2] -discovery $Discovery  -dbInfoCache $existingDbInfoCache		

	}

}

else

{	

	foreach ($instanceStoreElement in $instanceStoreElementCollection)

	{

		[string]$instanceStoreName = $instanceStoreElement.GetAttributeValue($InstanceStoreName)

		[string]$scopePath = $instanceStoreElement.GetAttributeValue($InstanceStoreLocation)

		

		[string]$normalizedScope = NormalizeScopePath -scope $scopePath			

		if ([string]::IsNullOrEmpty($normalizedScope))

		{

			continue

		}



		$SiteName = $null

		$ApplicationPath = [string]::Empty

		# Split SiteName and Virtual Path			

		[int]$siteNameEndIndex = $normalizedScope.IndexOf('/');

		if ($siteNameEndIndex -ne -1)

		{

			$SiteName = $normalizedScope.Substring(0, $siteNameEndIndex)

		}

		else

		{

			$SiteName = $normalizedScope

		}

		

		if (($siteNameEndIndex -ne -1) -and ($siteNameEndIndex -lt $normalizedScope.Length))

		{

			$ApplicationPath = $normalizedScope.Substring($siteNameEndIndex)

		}			       

		

		$connectionInfoObjects = Get-UniqueConnectionInfoObjects -siteName  $SiteName -applicationPath $ApplicationPath	-storeNameFilter $instanceStoreName	

		foreach($connectionInfoObject in $connectionInfoObjects.Keys)

		{

			Create-PersistenceStoreConnectionObjects -connectionString $connectionInfoObjects[$connectionInfoObject][0] -provider 'System.Data.SqlClient' -connectionStringName $connectionInfoObjects[$connectionInfoObject][1] -storeName $connectionInfoObjects[$connectionInfoObject][1] -location $connectionInfoObjects[$connectionInfoObject][2] -discovery $Discovery  -dbInfoCache $existingDbInfoCache

		}

		

	}

}



# Return the discovery data by calling the variable

$script:oAPI.Return($Discovery)

return



trap

{

	if ($script:oAPI -ne $null)

	{	

		foreach($errorDetail in $Error)

		{

			$script:oAPI.LogScriptEvent("Create-PersistenceStoreConnectionInfo.ps1", 100, 1, $errorDetail.Exception)

		}

		

		$script:oAPI.Return($Discovery)

	}	



	return

}



      </Script></Contents>

        <Unicode>1</Unicode>

      </File>

    </Files>

  </DataSource>

</Discovery>