Deploying Database changes alongside your code with SSDT projects and VSTS

Play Deploying Database changes alongside your code with SSDT projects and VSTS
Sign in to queue


Damian is joined by Abel Wang to show us one way of tracking and deploying database changes alongside your code. The SQL Server Data Tools (SSDT) project type in Visual Studio allows you to keep an up to date version of your code in source control. Visual Studio Team Services (VSTS) natively supports deploying any changes to an SSDT project to your SQL Server instance.





The Discussion

  • User profile image

    @lestephane:Has anyone answered your question?  I am sorta in the same boat as you and need some help as well.

  • User profile image


    Sorry I missed this!

    The answer is that the SQL Server DACPAC deployment task is deprecated, but only for an agent deployment. The current guidance is to use Deployment Groups and the new SQL Server Database Deploy task, running it inside a Deployment Group Job.

    The reasoning is it's easier (and safer) to run it on the database server itself for an on-premises database.

    You can see more details in the links above, as well as here:

    Hope that helps!


  • User profile image

    Along with deployment groups, what are preferred practices for branching and releasing for DB projects?  We are wanting to do CICD on SSDT with Azure Dev Ops, but it seems like the rules and suggestions are changing.  If this is a poor example of how to do it now, do you have more recent videos?


  • User profile image

    Independent of the tools you end up using, your changes to your DB should sit alongside your code changes so everything gets PR'ed together and versioned together. SSDT uses state/model based to manage db changes. Some other tools, like RedGate uses migrations based (from one version to the next). Either one of these approaches work. I'm kind of a fan of migrations based changes as it gives me finer grained control over how I manage my data from one version change to the next.

  • User profile image

    @Damovisa: Thanks for pointing out that it is the deployment group job that has this task in it.  I went back and rewatched the video and saw that was what was being used, so I must have overlooked it.  

    One other question I have is on the deployment task and using Windows Authentication for the SQL Server Connection.  I just wasn't sure which account to put there.  Is it the account running the deployment pool agent on the server or can it be another account we have set up on our domain as a service account?

  • User profile image

    @aptivadave: Yes, the agent that runs on the machine will run as a specific account. If you start it in interactive mode (i.e. run it manually) it'll be the account you're running as (I think). If you run it as a service, which is more common, you can change the user in Services in Windows directly.

  • User profile image

    @Damovisa: Thanks for answering my question(s).  This helps out a lot!

  • User profile image

    Hi Abel and Damian,

    I found this video very helpful, but I want to deploy to an Azure SQL Database. What would I need to do for that?

    Thank you!

Add Your 2 Cents