Deploying Database changes alongside your code with SSDT projects and VSTS

Sign in to queue

The Discussion

  • User profile image
    lestephane

    The SQL Server Database Deploy task is now marked as deprecated in VSTS.

    How are people supposed to deploy DACPACs using private agents on premise? I had to create a monstrous command line task which very long sqlpackage.exe commands to get this to work...

  • User profile image
    aptivadave

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

  • User profile image
    Damovisa

    @lestephane,@aptivadave:

    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: https://docs.microsoft.com/en-us/azure/devops/pipelines/apps/cd/howto-webdeploy-iis-deploygroups.

    Hope that helps!

    Damian

  • User profile image
    Hitek

    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
    AbelSquid​Head

    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
    aptivadave

    @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
    Damovisa

    @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
    aptivadave

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

Add Your 2 Cents