MSBuild / PowerShell: Copy SQL Server 2012 database to SQL Azure via BACPAC (for Continuous Integration)
- by giveme5minutes
I'm creating a continuous integration MSBuild script which copies a database in on-premise SQL Server 2012 to SQL Azure.
Easy right?
Methods
After a fair bit of research I've come across the following methods:
Use PowerShell to access the DAC library directly, then use the MSBuild PowerShell extension to wrap the script. This would require installing PowerShell 3 and working out how to make the MSBuild PowerShell extension work with it, as apparently MS moved the DAC API to a different namespace in the latest version of the library. PowerShell would give direct access to the API, but may require quite a bit of boilerplate.
Use the sample DAC Framework Client Side Tools, which requires compiling them myself, as the downloads available from Codeplex only include the Hosted version. It would also require fixing them to use DAC 3.0 classes as they appear to currently use an earlier version of DAC. I could then call these tools from an <Exec Command="" /> in the MSBuild script. Less boilerplate and if I hit any bumps in the road I can just make changes to the source.
Processes
Using whichever method, the process could be either:
Export from on-premise SQL Server 2012 to local BACPAC
Upload BACPAC to blog storage
Import BACPAC to SQL Azure via Hosted DAC
Or:
Export from on-premise SQL Server 2012 to local BACPAC
Import BACPAC to SQL Azure via Client DAC
Question
All of the above seems to be quite a lot of effort for something that seems to be a standard feature... so before I start reinventing the wheel and documenting the results for all to see, is there something really obvious that I've missed here? Is there pre-written script that MS has released that I have not yet uncovered?
There's an command in the GUI of SQL Server Management Studio 2012 that does EXACTLY what I'm trying to do (right click on local database, click "Tasks", click "Deploy Database to SQL Azure"). Surely if it's a few clicks in the GUI it must be a single command on the command line somewhere??
