Deploying to Azure from psake – “No snap-ins have been registered”

Cloud hosting really lends itself to continuous deployment approaches – scripted provisioning models and blue/green deployment are well catered for. In the case of Microsoft Azure, the Powershell Cmdlets integrate easily with our psake build scripts.

Unfortunately, the cmdlets are 64 bit only and most of the popular CI servers are java-based and recommend using the 32 bit JVM for a variety of presumably good reasons. This results in 32 bit powershell kicking off and generating the error message “No snap-ins have been registered for Windows PowerShell version 2”.

Following is a modification to the psake.cmd file that ensures the 64 bit version of powershell is called on 64 bit systems:

@echo off
IF EXIST %windir%\sysnative\WindowsPowershell (
%windir%\sysnative\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy unrestricted -Command "& '%~dp0\psake.ps1' %*"
) ELSE (
powershell -NoProfile -ExecutionPolicy unrestricted -Command "& '%~dp0\psake.ps1' %*"
)
Advertisements

vsdbcmd deployment to SQL Azure

Unless you’re targeting a data-tier application, you can’t generate a create script from a .dbproj (datadude) database project in Visual Studio that’s compatible with SQL Azure. This is a big pain if you’re attempting to implement a continuous deployment/continuous delivery approach.

Following is a snippet of Powershell code (a component of a psake build script) that deploys the dbschema to a local SQL instance, then uses SMO to script out the objects & data in an SQL Azure compatible format. This doesn’t solve the problem of generating schema update statements for an existing database, it’s a big time-saver while your Azure solution is in development.

Write-Host "Generate SQL Create script" -ForegroundColor Green

mkdir "$build_artifacts_dir\Database"

# Do a Prod deployment locally 
Exec { vsdbcmd /manifest:"$build_artifacts_dir\Release\$dbproj.deploymanifest" /a:Deploy /dd /cs:"Data Source=(LOCAL);Integrated Security=true" /p:AlwaysCreateNewDatabase=true /p:TargetDatabase=$dbname  }

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | out-null

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') '(LOCAL)' 
$db = $server.Databases[$dbname]

$transfer = new-object ('Microsoft.SqlServer.Management.Smo.Transfer') ($db)
$transfer.CopyAllObjects = $True
$transfer.CreateTargetDatabase = $False
$transfer.Options.AppendToFile = $True
$transfer.Options.ClusteredIndexes = $True
$transfer.Options.DriAll = $True
$transfer.Options.ScriptDrops = $True
$transfer.Options.IncludeHeaders = $False
$transfer.Options.ToFileOnly = $True
$transfer.Options.Indexes = $True
$transfer.Options.WithDependencies = $True
$transfer.Options.TargetDatabaseEngineType = "SqlAzureDatabase" 
$transfer.Options.TargetServerVersion = "Version105" 
$transfer.Options.FileName = "$build_artifacts_dir\Database\Create.sql"

#Generate Drops
$transfer.ScriptTransfer()
    
#Generate Creates
$transfer.Options.ScriptDrops = $False
$transfer.ScriptTransfer()
    
$scripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($server)    
$scripter.Options.AppendToFile = $True
$scripter.Options.ScriptData = $True
$scripter.Options.ScriptSchema = $False
$scripter.Options.NoCommandTerminator = $True
$scripter.Options.ToFileOnly = $True
$scripter.Options.TargetDatabaseEngineType = "SqlAzureDatabase"
$scripter.Options.TargetServerVersion = "Version105"
$scripter.Options.WithDependencies = $True
$scripter.Options.FileName = "$build_artifacts_dir\Database\Create.sql"

#Generate Inserts
$scripter.EnumScript([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$db.Tables)

From there you can use sqlcmd to execute the script and drop & recreate your Azure database objects and populate them with data.