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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s