DSCResources/DSC_SqlDatabase/DSC_SqlDatabase.psm1

$script:sqlServerDscHelperModulePath = Join-Path -Path $PSScriptRoot -ChildPath '..\..\Modules\SqlServerDsc.Common'
$script:resourceHelperModulePath = Join-Path -Path $PSScriptRoot -ChildPath '..\..\Modules\DscResource.Common'

Import-Module -Name $script:sqlServerDscHelperModulePath
Import-Module -Name $script:resourceHelperModulePath

$script:localizedData = Get-LocalizedData -DefaultUICulture 'en-US'

$script:supportedCompatibilityLevels = @{
    8  = @('Version80')
    9  = @('Version80', 'Version90')
    10 = @('Version80', 'Version90', 'Version100')
    11 = @('Version90', 'Version100', 'Version110')
    12 = @('Version100', 'Version110', 'Version120')
    13 = @('Version100', 'Version110', 'Version120', 'Version130')
    14 = @('Version100', 'Version110', 'Version120', 'Version130', 'Version140')
    15 = @('Version100', 'Version110', 'Version120', 'Version130', 'Version140', 'Version150')
    16 = @('Version100', 'Version110', 'Version120', 'Version130', 'Version140', 'Version150', 'Version160')
}

<#
    .SYNOPSIS
        This function gets the sql database.
 
    .PARAMETER Ensure
        When set to 'Present', the database will be created.
        When set to 'Absent', the database will be dropped.
 
    .PARAMETER Name
      The name of database to be created or dropped.
 
    .PARAMETER ServerName
       The host name of the SQL Server to be configured. Default value is the
       current computer name.
 
    .PARAMETER InstanceName
       The name of the SQL instance to be configured.
 
    .PARAMETER Collation
        The name of the SQL collation to use for the new database.
        Default value is server collation.
#>


function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Name,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $ServerName = (Get-ComputerName),

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $InstanceName
    )

    Write-Verbose -Message (
        $script:localizedData.GetDatabase -f $Name, $InstanceName
    )

    $returnValue = @{
        Name               = $Name
        Ensure             = 'Absent'
        ServerName         = $ServerName
        InstanceName       = $InstanceName
        Collation          = $null
        CompatibilityLevel = $null
        RecoveryModel      = $null
        OwnerName          = $null
    }

    $sqlServerObject = Connect-SQL -ServerName $ServerName -InstanceName $InstanceName

    if ($sqlServerObject)
    {
        # Check database exists
        $sqlDatabaseObject = $sqlServerObject.Databases[$Name]

        if ($sqlDatabaseObject)
        {
            $returnValue['Ensure'] = 'Present'
            $returnValue['Collation'] = $sqlDatabaseObject.Collation
            $returnValue['CompatibilityLevel'] = $sqlDatabaseObject.CompatibilityLevel
            $returnValue['RecoveryModel'] = $sqlDatabaseObject.RecoveryModel
            $returnValue['OwnerName'] = $sqlDatabaseObject.Owner

            Write-Verbose -Message (
                $script:localizedData.DatabasePresent -f $Name
            )
        }
        else
        {
            Write-Verbose -Message (
                $script:localizedData.DatabaseAbsent -f $Name
            )
        }
    }

    return $returnValue
}

<#
    .SYNOPSIS
        This function create or delete a database in the SQL Server instance provided.
 
    .PARAMETER Ensure
        When set to 'Present', the database will be created.
        When set to 'Absent', the database will be dropped.
 
    .PARAMETER Name
        The name of database to be created or dropped.
 
    .PARAMETER ServerName
       The host name of the SQL Server to be configured. Default value is the
       current computer name.
 
    .PARAMETER InstanceName
       The name of the SQL instance to be configured.
 
    .PARAMETER Collation
        The name of the SQL collation to use for the new database.
        Default value is server collation.
 
    .PARAMETER CompatibilityLevel
        The version of the SQL compatibility level to use for the new database.
        Default value is server version.
 
    .PARAMETER RecoveryModel
        The recovery model to be used for the new database.
        Default value is Full.
 
    .PARAMETER OwnerName
        Specifies the name of the login that should be the owner of the database.
#>

function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [Parameter()]
        [ValidateSet('Present', 'Absent')]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Ensure = 'Present',

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Name,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $ServerName = (Get-ComputerName),

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $InstanceName,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Collation,

        [Parameter()]
        [ValidateSet('Version80', 'Version90', 'Version100', 'Version110', 'Version120', 'Version130', 'Version140', 'Version150')]
        [System.String]
        $CompatibilityLevel,

        [Parameter()]
        [ValidateSet('Simple', 'Full', 'BulkLogged')]
        [System.String]
        $RecoveryModel,

        [Parameter()]
        [System.String]
        $OwnerName
    )

    $sqlServerObject = Connect-SQL -ServerName $ServerName -InstanceName $InstanceName

    if ($sqlServerObject)
    {
        if ($PSBoundParameters.ContainsKey('CompatibilityLevel'))
        {
            # Verify that a correct compatibility level is specified.
            if ($CompatibilityLevel -notin $supportedCompatibilityLevels.$($sqlServerObject.VersionMajor))
            {
                $errorMessage = $script:localizedData.InvalidCompatibilityLevel -f $CompatibilityLevel, $InstanceName

                New-ObjectNotFoundException -Message $errorMessage
            }
        }

        if ($PSBoundParameters.ContainsKey('Collation'))
        {
            # Verify that the correct collation is used.
            if ($Collation -notin $sqlServerObject.EnumCollations().Name)
            {
                $errorMessage = $script:localizedData.InvalidCollation -f $Collation, $InstanceName

                New-ObjectNotFoundException -Message $errorMessage
            }
        }

        if ($Ensure -eq 'Present')
        {
            $sqlDatabaseObject = $sqlServerObject.Databases[$Name]

            if ($sqlDatabaseObject)
            {
                Write-Verbose -Message (
                    $script:localizedData.SetDatabase -f $Name, $InstanceName
                )

                $wasUpdate = $false

                if ($PSBoundParameters.ContainsKey('Collation'))
                {
                    Write-Verbose -Message (
                        $script:localizedData.UpdatingCollation -f $Collation
                    )

                    $sqlDatabaseObject.Collation = $Collation

                    $wasUpdate = $true
                }

                if ($PSBoundParameters.ContainsKey('CompatibilityLevel'))
                {
                    Write-Verbose -Message (
                        $script:localizedData.UpdatingCompatibilityLevel -f $CompatibilityLevel
                    )

                    $sqlDatabaseObject.CompatibilityLevel = $CompatibilityLevel

                    $wasUpdate = $true
                }

                if ($PSBoundParameters.ContainsKey('RecoveryModel'))
                {
                    Write-Verbose -Message (
                        $script:localizedData.UpdatingRecoveryModel -f $RecoveryModel
                    )

                    $sqlDatabaseObject.RecoveryModel = $RecoveryModel

                    $wasUpdate = $true
                }

                if ($PSBoundParameters.ContainsKey('OwnerName'))
                {
                    Write-Verbose -Message (
                        $script:localizedData.UpdatingOwner -f $OwnerName
                    )

                    try
                    {
                        $sqlDatabaseObject.SetOwner($OwnerName)
                    }
                    catch
                    {
                        $errorMessage = $script:localizedData.FailedToUpdateOwner -f $OwnerName, $Name

                        New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
                    }

                    $wasUpdate = $true
                }

                try
                {
                    if ($wasUpdate)
                    {
                        $sqlDatabaseObject.Alter()
                    }
                }
                catch
                {
                    $errorMessage = $script:localizedData.FailedToUpdateDatabase -f $Name

                    New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
                }
            }
            else
            {
                try
                {
                    $sqlDatabaseObjectToCreate = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Database' -ArgumentList $sqlServerObject, $Name

                    if ($sqlDatabaseObjectToCreate)
                    {
                        Write-Verbose -Message (
                            $script:localizedData.CreateDatabase -f $Name
                        )

                        if ($PSBoundParameters.ContainsKey('RecoveryModel'))
                        {
                            $sqlDatabaseObjectToCreate.RecoveryModel = $RecoveryModel
                        }

                        if ($PSBoundParameters.ContainsKey('Collation'))
                        {
                            $sqlDatabaseObjectToCreate.Collation = $Collation
                        }

                        if ($PSBoundParameters.ContainsKey('CompatibilityLevel'))
                        {
                            $sqlDatabaseObjectToCreate.CompatibilityLevel = $CompatibilityLevel
                        }

                        $sqlDatabaseObjectToCreate.Create()

                        <#
                            This must be run after the object is created because
                            the owner property is read-only and the method cannot
                            be call until the object has been created.
                        #>

                        if ($PSBoundParameters.ContainsKey('OwnerName'))
                        {
                            $sqlDatabaseObjectToCreate.SetOwner($OwnerName)
                        }
                    }
                }
                catch
                {
                    $errorMessage = $script:localizedData.FailedToCreateDatabase -f $Name

                    New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
                }
            }
        }
        else
        {
            try
            {
                $sqlDatabaseObjectToDrop = $sqlServerObject.Databases[$Name]

                if ($sqlDatabaseObjectToDrop)
                {
                    Write-Verbose -Message (
                        $script:localizedData.DropDatabase -f $Name
                    )

                    $sqlDatabaseObjectToDrop.Drop()
                }
            }
            catch
            {
                $errorMessage = $script:localizedData.FailedToDropDatabase -f $Name

                New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
            }
        }
    }
}

<#
    .SYNOPSIS
      This function tests if the sql database is already created or dropped.
 
    .PARAMETER Ensure
        When set to 'Present', the database will be created.
        When set to 'Absent', the database will be dropped.
 
    .PARAMETER Name
       The name of database to be created or dropped.
 
    .PARAMETER ServerName
       The host name of the SQL Server to be configured. Default value is the
       current computer name.
 
    .PARAMETER InstanceName
        The name of the SQL instance to be configured.
 
    .PARAMETER Collation
        The name of the SQL collation to use for the new database.
        Default value is server collation.
 
    .PARAMETER CompatibilityLevel
        The version of the SQL compatibility level to use for the new database.
        Default value is server version.
 
    .PARAMETER RecoveryModel
        The recovery model to be used for the new database.
        Default value is Full.
 
    .PARAMETER OwnerName
        Specifies the name of the login that should be the owner of the database.
#>

function Test-TargetResource
{
    [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('SqlServerDsc.AnalyzerRules\Measure-CommandsNeededToLoadSMO', '', Justification='The command Connect-Sql is called when Get-TargetResource is called')]
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [Parameter()]
        [ValidateSet('Present', 'Absent')]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Ensure = 'Present',

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Name,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $ServerName = (Get-ComputerName),

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $InstanceName,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Collation,

        [Parameter()]
        [ValidateSet('Version80', 'Version90', 'Version100', 'Version110', 'Version120', 'Version130', 'Version140', 'Version150')]
        [System.String]
        $CompatibilityLevel,

        [Parameter()]
        [ValidateSet('Simple', 'Full', 'BulkLogged')]
        [System.String]
        $RecoveryModel,

        [Parameter()]
        [System.String]
        $OwnerName
    )

    Write-Verbose -Message (
        $script:localizedData.TestingConfiguration -f $Name, $InstanceName
    )

    $getTargetResourceParameters = @{
        Name         = $Name
        ServerName   = $ServerName
        InstanceName = $InstanceName
    }

    $getTargetResourceResult = Get-TargetResource @getTargetResourceParameters

    $isDatabaseInDesiredState = $true

    switch ($Ensure)
    {
        'Absent'
        {
            if ($getTargetResourceResult.Ensure -ne 'Absent')
            {
                Write-Verbose -Message (
                    $script:localizedData.NotInDesiredStateAbsent -f $Name
                )

                $isDatabaseInDesiredState = $false
            }
        }

        'Present'
        {
            if ($getTargetResourceResult.Ensure -ne 'Present')
            {
                Write-Verbose -Message (
                    $script:localizedData.NotInDesiredStatePresent -f $Name
                )

                $isDatabaseInDesiredState = $false
            }
            else
            {
                if ($PSBoundParameters.ContainsKey('Collation') -and $getTargetResourceResult.Collation -ne $Collation)
                {
                    Write-Verbose -Message (
                        $script:localizedData.CollationWrong -f $Name, $getTargetResourceResult.Collation, $Collation
                    )

                    $isDatabaseInDesiredState = $false
                }

                if ($PSBoundParameters.ContainsKey('CompatibilityLevel') -and $getTargetResourceResult.CompatibilityLevel -ne $CompatibilityLevel)
                {
                    Write-Verbose -Message (
                        $script:localizedData.CompatibilityLevelWrong -f $Name, $getTargetResourceResult.CompatibilityLevel, $CompatibilityLevel
                    )

                    $isDatabaseInDesiredState = $false
                }

                if ($PSBoundParameters.ContainsKey('RecoveryModel') -and $getTargetResourceResult.RecoveryModel -ne $RecoveryModel)
                {
                    Write-Verbose -Message (
                        $script:localizedData.RecoveryModelWrong -f $Name, $getTargetResourceResult.RecoveryModel, $RecoveryModel
                    )

                    $isDatabaseInDesiredState = $false
                }

                if ($PSBoundParameters.ContainsKey('OwnerName') -and $getTargetResourceResult.OwnerName -ne $OwnerName)
                {
                    Write-Verbose -Message (
                        $script:localizedData.OwnerNameWrong -f $Name, $getTargetResourceResult.OwnerName, $OwnerName
                    )

                    $isDatabaseInDesiredState = $false
                }
            }
        }
    }

    return $isDatabaseInDesiredState
}