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 } Write-Verbose("Connection string : $ConnectionString") 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 ) Write-Verbose("sql server : $SqlServer") Write-Verbose("sql version : $SqlServerVersion") Write-Verbose("sql Auth : $SqlAuthType") if($PSBoundParameters.ContainsKey('SqlConnectionCredential')) { Write-Verbose("SQl Auth Detected") $ConnectionString = Construct-ConnectionString -sqlServer $SqlServer -credentials $SqlConnectionCredential -AuthType $SqlAuthType } else { Write-Verbose("Windows Auth Detected") $ConnectionString = Construct-ConnectionString -sqlServer $SqlServer -AuthType $SqlAuthType } Write-Verbose("Connection string : $ConnectionString") if($Ensure -eq "Present") { if($PSBoundParameters.ContainsKey('BacPacPath')) { Write-Verbose("About to Perform Bacpac Restore....") Write-Verbose("bacpac path : $BacPacPath") Write-Verbose("DbName path : $DatabaseName") Write-Verbose("Sql Server Version : $SqlServerVersion") Write-Verbose("Connection String: $ConnectionString") Perform-InPlaceRestore -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" } Write-Verbose("About to Perform Dac Deploy...") Write-Verbose("dacpac path : $DacPacPath") Write-Verbose("Application Name : $DacPacApplicationName") Write-Verbose("Application Version : $DacPacApplicationVersion") DeployDac -databaseName $DatabaseName -connectionString $ConnectionString -sqlserverVersion $SqlServerVersion -dacpacPath $DacPacPath -dacpacApplicationName $DacPacApplicationName -dacpacApplicationVersion $DacPacApplicationVersion -Verbose } else { CreateDb -databaseName $DatabaseName -connectionString $ConnectionString -Verbose Write-Verbose $($LocalizedData.CreateDBSuccess -f ${DatabaseName}) } if($PSBoundParameters.ContainsKey('OwnerName')) { try { Write-Verbose("Owner Name detected....: $OwnerName") [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 = $_.Exception.Message $exception = New-Object System.InvalidOperationException $errorMessage $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $errorCategory, $null $PSCmdlet.ThrowTerminatingError($errorRecord); } } } else { Write-Verbose("Deleteing Db....: $DatabaseName") Write-Verbose $($LocalizedData.RemoveDBInProgress -f $DatabaseName) DeleteDb -databaseName $DatabaseName -connectionString $ConnectionString -sqlServerVersion $SqlServerVersion -Verbose 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 -Verbose 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 } } function Perform-InPlaceRestore([string]$DbName, [string]$connectionString, [string]$sqlserverVersion, [string]$bacpacFilePath) { Write-Verbose("Inside Perform-Restore routine") Write-Verbose("About to perform Bacpac resore for DB:$DbName") Load-DacFx -sqlserverVersion $sqlserverVersion -Verbose $dacServiceInstance = new-object Microsoft.SqlServer.Dac.DacServices ($connectionString) Write-Verbose("dacServiceInstance:$dacServiceInstance") try { $bacpacPackageInstance = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacFilePath) Write-Verbose("bacpacPackageInstance:$bacpacPackageInstance") $dacServiceInstance.ImportBacpac($bacpacPackageInstance, $DbName) }catch { Write-Verbose("BacPac import failed for db: $DbName, BacPac file Path : $bacpacFilePath") Throw "$_.Exception.Message : Resore Failed ----BacPac import failed for db: $DbName, BacPac file Path : $bacpacFilePath" } } Export-ModuleMember -Function *-TargetResource |