xDatabase

1.1

DSCResources/MSFT_xDatabase/MSFT_xDatabase.psm1

data LocalizedData
{
    # culture="en-US"e
    ConvertFrom-StringData @'
        SettingDBOwnerError=Failed to set the owner of the database to {0}. Make sure that the user exists and the account used to connect to the database has permission to set the owner.
        SettingDBOwnerSuccess=Successfully set the owner of database {0} to {1}.
        CreateDBSuccess=Successfully created database '{0}'.
        RemoveDBInProgress=Removing database '{0}...'
        RemoveDBSuccess=Successfully removed database '{0}'.
'@
}

$SmoServerLocation = "${env:ProgramFiles(x86)}\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

Import-Module $PSScriptRoot\..\xDatabase_Common

function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [System.Management.Automation.PSCredential]
        $SqlConnectionCredential,

        [System.String]
        [ValidateSet("SQL","Windows")]
        $SqlAuthType = "Windows",
        
        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure = "Present",

        [System.String]
        $SqlServer,

        [ValidateSet("2008-R2","2012","2014")]
        [System.String]
        $SqlServerVersion,

        [parameter(Mandatory = $true)]
        [System.String]
        $DatabaseName,
        
        [System.String]
        $OwnerName
    )

    if($PSBoundParameters.ContainsKey('SqlConnectionCredential'))
    {
        $ConnectionString = Construct-ConnectionString -sqlServer $SqlServer -credentials $SqlConnectionCredential -AuthType $SqlAuthType
    }
    else
    {
        $ConnectionString = Construct-ConnectionString -sqlServer $SqlServer -AuthType $SqlAuthType
    }

    if(CheckIfDbExists -ConnectionString $ConnectionString -DatabaseName $DatabaseName)
    {
        $ActualEnsure = "Present"
    }
    else
    {
        $ActualEnsure = "Absent"
    }
    
    $Owner = Get-SqlDatabaseOwner -DatabaseName $DatabaseName -connectionString $ConnectionString 

    $returnValue = @{
        Ensure = [System.String] $ActualEnsure 
        DatabaseName = [system.string] $DatabaseName
        OwnerName = [System.String] $Owner
    }

    $returnValue
}

function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [System.Management.Automation.PSCredential] 
        $SqlConnectionCredential,
        
        [System.String]
        [ValidateSet("SQL","Windows")]
        $SqlAuthType = "Windows",
        
        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure = "Present",

        [System.String]
        $SqlServer = "LocalHost",

        [ValidateSet("2008-R2","2012","2014")]
        [System.String]
        $SqlServerVersion,

        [System.String]
        $BacPacPath,

        [parameter(Mandatory = $true)]
        [System.String]
        $DatabaseName,
        
        [System.String]
        $OwnerName,

        [System.String]
        $DacPacPath,

        [System.String]
        $DacPacApplicationName,

        [System.String]
        $DacPacApplicationVersion
    )

    if($PSBoundParameters.ContainsKey('SqlConnectionCredential'))
    {
        $ConnectionString = Construct-ConnectionString -sqlServer $SqlServer -credentials $SqlConnectionCredential -AuthType $SqlAuthType
    }
    else
    {
        $ConnectionString = Construct-ConnectionString -sqlServer $SqlServer -AuthType $SqlAuthType
    }

   if($Ensure -eq "Present")
    {
        if($PSBoundParameters.ContainsKey('BacPacPath'))
        {
            Perform-Restore -DbName $DatabaseName -connectionString $ConnectionString -sqlserverVersion $SqlServerVersion -bacpacFilePath $BacPacPath
        }
        elseif($PSBoundParameters.ContainsKey('DacPacPath'))
        {
            if(!$PSBoundParameters.ContainsKey('DacPacApplicationName'))
            {
                Throw "Application Name Needed for DAC Registration, else upgrade is unsupported"
            }
            DeployDac -databaseName $DatabaseName -connectionString $ConnectionString -sqlserverVersion $SqlServerVersion -dacpacPath $DacPacPath -dacpacApplicationName $DacPacApplicationName -dacpacApplicationVersion $DacPacApplicationVersion
        }
        else
        {
            CreateDb -databaseName $DatabaseName -connectionString $ConnectionString 

            Write-Verbose $($LocalizedData.CreateDBSuccess -f ${DatabaseName})
        }

        if($PSBoundParameters.ContainsKey('OwnerName'))
        {
            try
            {

                [string]$SqlQuery = "use $DatabaseName; exec sp_changedbowner '$OwnerName'"

                $sqlConnection = new-object system.data.SqlClient.SQLConnection($connectionString)

                ExecuteSqlQuery -sqlConnection $sqlConnection -SqlQuery $SqlQuery

                Write-Verbose $($LocalizedData.SettingDBOwnerSuccess -f ${DatabaseName}, ${OwnerName})
            }
            catch
            {
                $errorId = "SetDatabaseOwner";
                $errorCategory = [System.Management.Automation.ErrorCategory]::InvalidResult
                $errorMessage = $($LocalizedData.SettingDBOwnerError -f ${OwnerName})
                $exception = New-Object System.InvalidOperationException $errorMessage 
                $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $errorCategory, $null

                $PSCmdlet.ThrowTerminatingError($errorRecord);
            }
        }
    }
    else
    {
        Write-Verbose $($LocalizedData.RemoveDBInProgress -f $DatabaseName)
        
        DeleteDb -databaseName $DatabaseName -connectionString $ConnectionString -sqlServerVersion $SqlServerVersion

        Write-Verbose $($LocalizedData.RemoveDBSuccess -f $DatabaseName)
    }
}

function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [System.Management.Automation.PSCredential]
        $SqlConnectionCredential,
        
        [System.String]
        [ValidateSet("SQL","Windows")]
        $SqlAuthType = "Windows",
        
        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure = "Present",

        [System.String]
        $SqlServer,

        [ValidateSet("2008-R2","2012","2014")]
        [System.String]
        $SqlServerVersion,

        [System.String]
        $BacPacPath,

        [parameter(Mandatory = $true)]
        [System.String]
        $DatabaseName,
        
        [System.String]
        $OwnerName,

        [System.String]
        $DacPacPath,

        [System.String]
        $DacPacApplicationName,

        [System.String]
        $DacPacApplicationVersion
    )

    if($PSBoundParameters.ContainsKey('DacPacPath') -and $PSBoundParameters.ContainsKey('BacPacPath'))
    {
        throw "Specify only one out of dacpac or bacpac"
    }

    if($PSBoundParameters.ContainsKey('SqlConnectionCredential'))
    {
        $ConnectionString = Construct-ConnectionString -sqlServer $SqlServer -credentials $SqlConnectionCredential -AuthType $SqlAuthType
    }
    else
    {
        $ConnectionString = Construct-ConnectionString -sqlServer $SqlServer -AuthType $SqlAuthType
    }

    $dbExists = CheckIfDbExists -ConnectionString $ConnectionString -DatabaseName $DatabaseName

    if($Ensure -eq "Present")
    {
        if($PSBoundParameters.ContainsKey('BacPacPath'))
        {
            if($dbExists)
            {
                return $true
            }
  
            return $false
        }
        if($dbExists -eq $false)
        {
            return $false
        }
        if($dbExists -eq $true -and $PSBoundParameters.ContainsKey('OwnerName'))
        {
            if ($OwnerName -ne (Get-SqlDatabaseOwner -DatabaseName $DatabaseName -connectionString $ConnectionString ))
            {
                return $false
            }
        }
        if($dbExists -eq $true -and !$PSBoundParameters.ContainsKey('DacPacPath'))
        {
            return $true
        }
        else
        {
            return $false
        }
    }
    else
    {
        if($dbExists)
        {
            return $false
        }

        return $true
    }
}

Export-ModuleMember -Function *-TargetResource