Windows Azure SQL User Databases Discovery

Microsoft.SqlServer.Azure.Discovery.UserDatabases (Discovery)

This workflow discovers user databases hosted in Windows Azure SQL Database Cloud Service.

Knowledge Base article:

Element properties:

Object Discovery Details:

Member Modules:

Source Code:

<Discovery ID="Microsoft.SqlServer.Azure.Discovery.UserDatabases" Target="Microsoft.SqlServer.Azure.Server" Enabled="true" ConfirmDelivery="false" Remotable="true" Priority="Normal">

  <Category>Discovery</Category>

  <DiscoveryTypes>

    <DiscoveryClass TypeID="Microsoft.SqlServer.Azure.Database"/>

    <DiscoveryClass TypeID="Microsoft.SqlServer.Azure.UserDatabase">

      <Property TypeID="Microsoft.SqlServer.Azure.Database" PropertyID="ServerName"/>

      <Property TypeID="Microsoft.SqlServer.Azure.Database" PropertyID="DatabaseId"/>

      <Property TypeID="Microsoft.SqlServer.Azure.Database" PropertyID="DatabaseName"/>

      <Property TypeID="Microsoft.SqlServer.Azure.Database" PropertyID="MaxSize"/>

    </DiscoveryClass>

  </DiscoveryTypes>

  <DataSource ID="DS" TypeID="Microsoft.SqlServer.Azure.Module.BaseScriptedDiscoveryDataSource">

    <IntervalSeconds>14400</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <DiscoveryName>DatabasesDiscovery</DiscoveryName>

    <Script>Param ($ServerName, $Username, $Password)



function Main {

    $_databases = @{}

    $DiscoveryData = $ScriptApi.CreateDiscoveryData(0, '$MPElement$', '$Target/Id$')



    # List Databases

    WithRetry {$connection = (Create-SqlConnection $ServerName 'master' $Username $Password)} {

        Set-Variable $_databases @{}

        With ($command = $connection.CreateCommand()) {

            $command.CommandText = $DiscoveryQuery

            $connection.Open()



            With (,($reader = $command.ExecuteReader())) {

                while ($reader.Read()) {

                    $_databaseName = $reader.get_Item('Name')



                    $_database = $DiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SqlServer.Azure.UserDatabase']$")

                    $_database.AddProperty("$MPElement[Name='Microsoft.SqlServer.Azure.Server']/ServerName$", $ServerName)

                    $_database.AddProperty("$MPElement[Name='Microsoft.SqlServer.Azure.Database']/ServerName$", $ServerName)

	                $_database.AddProperty("$MPElement[Name='Microsoft.SqlServer.Azure.Database']/DatabaseId$", $reader.get_Item('Id'))

                    $_database.AddProperty("$MPElement[Name='Microsoft.SqlServer.Azure.Database']/DatabaseName$", $_databaseName)

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

                    $_databases.Add($_databaseName, $_database)

                }

            }

        }

    }



    # Get Maximum Size for each Database

    foreach ($_databaseName in $_databases.Keys) {

        try {

            WithRetry {$connection = (Create-SqlConnection $ServerName $_databaseName $Username $Password)} {

                With ($command = $connection.CreateCommand()) {

                    $command.CommandText = $MaxSizeQuery

                    $connection.Open()



                    With (,($reader = $command.ExecuteReader())) {

                        if ($reader.Read()) {

                            $_databases[$_databaseName].AddProperty("$MPElement[Name='Microsoft.SqlServer.Azure.Database']/MaxSize$", $reader.get_Item('MaxSize'))

                        }

                    }

                }

            }

        } catch {

            LogMessage 'Quota Size discovery' 2 'Unable to get Quota Size. Database will be discovered, but information may be incomplete.' $_databaseName

        }

    }



    # Add all objects to Discovery Data

    foreach ($_databaseName in $_databases.Keys) {

        $DiscoveryData.AddInstance($_databases[$_databaseName])

    }



    $DiscoveryData

}





            $DiscoveryQuery = @"

SET NOCOUNT ON;

SELECT database_id AS [Id], name AS [Name] FROM sys.databases

WHERE

    name &lt;&gt; 'master' 

    AND source_database_id IS NULL

    AND is_federation_member = 0

"@



            $MaxSizeQuery = @"

  SET NOCOUNT ON;

SELECT

    DB_NAME() AS [DatabaseName],

    CONVERT(real, DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes'))/(1024.0*1024.0) AS [MaxSize]

"@

          </Script>

    <Parameters>

      <Parameter>

        <Name>ServerName</Name>

        <Value>$Target/Property[Type="Microsoft.SqlServer.Azure.Server"]/ServerName$</Value>

      </Parameter>

      <Parameter>

        <Name>Username</Name>

        <Value>$RunAs[Name="Microsoft.SqlServer.Azure.Profile.Sql"]/UserName$</Value>

      </Parameter>

      <Parameter>

        <Name>Password</Name>

        <Value>$RunAs[Name="Microsoft.SqlServer.Azure.Profile.Sql"]/Password$</Value>

      </Parameter>

    </Parameters>

  </DataSource>

</Discovery>