Hello!

It is my birthday: another year older, another year not necessarily wiser. I’m planning on spending the evening watching the BBC and eating pizza. There are worse ways to spend a birthday…

Using PoshSSDTBuildDeploy: The Script

My SSDT build/deploy PowerShell module has had a bit of a reboot. People have actually been using it and even been asking me questions about it. The download numbers are growing, and I’m feeling a bit of responsibility! And so I thought it’d make sense to include a script I’ve been using in a CI/CD pipeline for reference on how to use.

[cmdletbinding()]
param (
	[parameter(Mandatory = $true)] $WorkingFolder,
	[parameter(Mandatory = $false)] $serverName,
	[parameter(Mandatory=$false)] [string] $sqlAdministratorLogin,
	[parameter(Mandatory=$false)] [String] $sqlAdministratorLoginPassword,
	[parameter(Mandatory=$false)] [String] $connectionString,
	[parameter(Mandatory = $true)] [string] $DatabaseName,
	[parameter(Mandatory = $true)] [string] $DacpacPath,
	[parameter(Mandatory = $true)] [string] $PublishProfile 
)

if ($PSBoundParameters.ContainsKey('connectionString') -eq $false) {
[string] $connectionString = "Server=tcp:$($serverName),1433;Initial Catalog=$($DatabaseName);Persist Security Info=False;User ID=$($sqlAdministratorLogin);Password=$($sqlAdministratorLoginPassword);MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
}
try {
	Find-Module -Name "PoshSSDTBuildDeploy"
	Install-PackageProvider -Name NuGet -Force -Scope CurrentUser
    Install-Module PoshSSDTBuildDeploy -Force -Scope CurrentUser
}
catch {
	Write-Host "No PoshSSDTBuildDeploy, Installing from PSGallery."
    Install-PackageProvider -Name NuGet -Force -Scope CurrentUser
    Install-Module PoshSSDTBuildDeploy -Force -Scope CurrentUser
}
finally {
	Write-Host "Importing PoshSSDTBuildDeploy."
	Import-Module PoshSSDTBuildDeploy -Force
}

$dacFxFolder = Install-MicrosoftDataToolsMSBuild -WorkingFolder $WorkingFolder 

$dacFX = Join-Path -Path $dacFxFolder -ChildPath "\Microsoft.SqlServer.Dac.dll"
New-Item -ItemType Directory -Force -Path "$WorkingFolder\deployScripts"

$PublishParams = @{
        dacfxPath                = $dacFX
        dacpac                   = (Resolve-Path $DacpacPath)
        publishXml               = (Resolve-Path $PublishProfile)
		targetConnectionString   = $connectionString
		targetDatabaseName       = $databaseName
		scriptPath				 = $WorkingFolder
	}
	if ($UserName) {
	$PublishParams.Add("SqlCredential", (New-Object System.Management.Automation.PSCredential ($UserName, $Password)))
}

    Publish-DatabaseDeployment  @PublishParams

More Changes to PoshSSDTBuildDeploy

Whilst on the subject of PoshSSDTBuildDeploy, I’ve made a minor change to how it handles overwriting SqlCmdVars. It used to be an all or nothing deal: if you wanted to overwrite the values of one SqlCmdVar in the publish.xml, you had to overwrite them all. Now there is a switch on Publish-DatabaseDeployment called FailOnMissingVars. If this is included, and there are any missing PowerShell variables then the deployment will not continue. If however you do not include this flag then the deploy will continue. This makes things a little more flexible. The default behaviour is now to continue on missing variables. AssistDeploy has similar non-optional funcitonality, and I should really update it to make itoptional. But it might be a breaking change. Will have to dig out the code and check again.

Transient Errors

If you’re using Azure then you’re probably aware of transient errors: we’ve been hit by these lately (and again, it;s something that maybe PoshSSDTBuildDeploy and AssistDeploy should handle.) Transient errors are basically a consequence of using cloud-based technologies that reconfigure themselves online. It’s well worth the 15 minutes reading time.

I’m Going To Disneyland (Paris)

I’m off on holiday for a week with the family to Disneyland. We get to do a bit of sightseeing round Paris proper. And hope to squeeze in a visit to Versailles. I’ve never been to Versailles nor Disneyland, but been to Paris several times. Am looking forward to seeing it all over again with the whole family. In fact I’m hoping to run the Paris marathon next April. We shall see…