Damian Brady

@Liero

The pre and post deployment scripts are really the only way you can manage the process (or "tell it how to handle the migration").

Because it's a bit of a black box, it's going to do whatever it thinks is correct for updating the schema, but you can control things with these pre and post stages. For example, temporarily store data in a table then copy and remove it at the end.

Long story short, if you're not comfortable letting the tool make schema changes for you, then the model-based option is probably not right for you.

Hi @Liero,

SSDT can definitely be used in those scenarios, but it's not a magic bullet - you need to tell it how to handle the migration in cases like this.

SSDT has a data loss safeguard which will fail the deployment in cases where there will be data loss. If turned on, the deployment will fail.

It's generally good practice to run the migrations in earlier environments - you _never_ want the first time you run a migration to be in Production! In fact, that was the point of Houssem's last demo. He pulls down the production database into a pre-prod environment and runs the migration against it (then any tests to verify success), before going to production.

Of course, you can disable the data loss safeguard and provide pre and post deployment scripts to define what should happen to migrate data safely. There's a great article by Dan Nolan from Redgate about this: https://www.red-gate.com/simple-talk/sql/database-delivery/fix-ten-ssdt-deployment-snags-without-readyroll/

I hope that helps!
Damian

@liuning0820:No, you can use Docker Hub if you want!

Azure Container Registry is just a private registry you can run from Azure. A private alternative :)

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

@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

@Martin Thornalley:Ah, those are just the internal editors of the show rather than who's in it :)

@Doug: These tasks are now only available for Deployment Groups - i.e. you need an agent on the machine to do the deployment. The advantage is no potentially tricky remote connection issues! You can find info here: 

• https://github.com/Microsoft/vsts-tasks/blob/master/Tasks/SqlDacpacDeploymentOnMachineGroupV0/README.md
• https://github.com/Microsoft/vsts-tasks/blob/master/Tasks/IISWebAppDeploymentOnMachineGroupV0/README.md

@Noe: Good question!

The part we didn't really show (and probably should have) was that all of this testing can run in your pipeline. Just as you should run your code unit tests in your IDE before commiting, you can do the same with database tests.

Are you talking about using Release Management in TFS rather than VSTS? If so - it will work if you're on a recent-enough version (the docs say TFS 2018).

If you're talking about whether you can do this from VSTS/TFS Build (rather than Release), the answer is no. Deployments should really be handled with Release Management (or another tool if you like) rather than Build - separating the creation of an artifact and actually deploying and promoting it between environments is really important in a good DevOps strategy. There's some good information about why on the 12 Factor App page.

@Chris Eelmaa:

Hi Chris,

The version number for the release is tied to the version number of the Nuget Package pushed to Octopus. Using OctoPack means the Nuget Package version (in this case) is given the version of the assembly in the project being compiled.

You can set the Release numbering format in the Project Settings page in Octopus Deploy.

I hope that helps,

Damian Brady