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.

Facetime over tethered 3G

John Gruber just confirmed that it’s possible to use Facetime on the iPad 2 while tethered (via Wifi) to an iPhone’s 3G connection. It’s worth noting that Apple’s own Developer Tech Services explicitly recommend to third-party app developers NOT to use the type of network to determine whether it will have sufficient bandwidth to support an application (for exactly this reason – MiFi devices will deliver 3G speed over WiFi). Assuming the Facetime developers aren’t ignoring Apple’s own advice, I would speculate that the WiFi restriction is more a favour to the carriers rather than a technical limitation.

Martin Fowler Presentation

Martin Fowler & Evan BottcherI had the privilege (along with most of Perth’s Agile community, by the look of it) of seeing Martin Fowler speak at the ThoughtWorks quarterly briefing at the Melbourne Hotel last night. Martin gave two short talks (what he calls a ‘Suite of Talks’), one on Continuous Integration & Continuous Delivery, and one covering his concept of different forms of Technical Debt. None of it was particularly new content to a hypothetical attendee who slavishly pores over everything Martin writes, but he has a great presentation style and connection with the audience, which was really enjoyable.

However, I did want to make a comment on Evan Bottcher’s talk on DevOps. Evan’s presentation was spot-on as far as delivering useful, real-world advice on bridging the gap between separate development & operations teams, but I’ve always found the concept of DevOps quite funny. I’ve spent most of my career in small, in-house teams, where I’ve had the admin passwords to all of the production servers & databases, been responsible for all the deployment, and been the first person that users call when something goes wrong. The idea that DevOps is some sort of ground-breaking paradigm shift in application service delivery is wrong — small teams have been doing it for decades.

Afterwards, I managed to get a few quick words with Martin & shake his hand — it’s great to see speakers of that calibre come to Perth.