I see questions pretty regularly on stack overflow and the ssdt msdn forum that is some variation on “I am doing a publish through visual studio and it doesn’t quite do what I want” or “I want to publish the database when I press F5 except when I don’t want it to publish”. The second variation I particularly enjoy because I have always though that what I want is a computer that I can think something and it will do it - I guess we are a little way off but one day hopefully that will be possible.

It occurred to me pretty early on in my SSDT usage that publishing through visual studio wasn’t really the way I wanted to do my development so what I do is:

• 1. Write a powershell script that calls sqlpackage.exe to deploy a dacpac to my dev instance
• 2. Write my code in Visual Studio / SSDT (New code and living the refactoring dream)
• 3. Build the project, ensure there are no errors
• 4. Alt+Tab to my powershell session and run the deploy script
• 5. Watch the powershell script and if it deployes successfully, continue my work

This has a number of benefits:

Benefit #1 - Simplicity

I know exactly when a deploy is going to happen and I know exactly what options it will use. If I want to add a variable or change a parameter then I can do it and it is straight forward.

Benefit #2 - Errors + Warnings

I see quickly and brightly any errors or warnings, I don’t really like the publish dialogue in SSDT because it sort of sits at the bottom and doesn’t shout when it is finished. I often sit waiting after it has finished and then have to click around for the errors - this isn’t a moan about the ui, I am just more comfortable with a command line and a bit of console colouring!

Benefit #3 - Monitor redeployments

If there is an issue like you have a default constraint with a definition in SSDT that SQL changes when it store it (such as adding brackets around things) then it will be constantly re-deployed. By running in a command window I get to see exactly what changes are happening and if I see the amount of things being re-deployed every time I can do something about it rather than the changes being hidden, again unless I really go looking for it.

Benefit #4 - Testing the CI process

This one is what I get most excited about (it really really does!). I use the same script to drive sqlpackage.exe for my local instance that I use on the CI server and other environments to deploy changes and generate change scripts. This means that the most well tested part of my development process is that bit that is often seen as the most risky (i.e. deploying T-SQL changes). It also gives me the chance to really build in confidence that sqlpackage.exe will work and that I can trust it.

On trusting sqlpackage.exe I have used it a lot now, I must have done local deploys numbering in the low thousands and I haven’t had any issues that weren’t caused by me - the last few versions have been reliable and I am really excited about the future of SQL Server development with SSDT - no other RDBMS has anything anywhere near the capabilities of SSDT and it is free!

Benefit #5 - Obvious where to put deployment contributors

If you are doing SSDT development seriously then you will likely want to use my deployment contributor at some time or another (http://agilesqlclub.codeplex.com/) or another contributor. Deploying the contributors for Visual Studio is machine wide whereas with sqlpackage.exe I can gather all the files, add an extensions directory and add the filter to that. It makes life so much simpler!

Complexity #1 - Extra effort

It isn’t all chips and gravy (incidentally I had chips and gravy for the first time recently, it was ok but I am sticking to salt, vinegar and a bit of ketchup).

I need to write the powershell script and run it. It could well be a batch file or an app that uses the DacFX api to deploy the dacpac but I prefer good old sqlpackage.exe

Complexity #2 - Context switching from Visual Studio

I need to alt+tab out of visual studio and run something else. It would be nice to run it all from Visual Studio but after the build I am likely going to run the application or tSQLt tests so I would probably be context switching anyway.

I have played around with getting this step into Visual Studio, I have tried:

• Using the command window to call powershell - this gets boring quite quickly, the colours are just so dull
• Using StudioShell - this was the most interesting but ultimately I went back to the simpler approach

Summary

Overall I am really pleased that I use sqlpackage.exe rather than Visual Studio, I can still use the xml profiles and it is a simple way to deploy changes or generate scripts.

Happy sqlpackage’ing!