4

I have a web application that is set up with Azure DevOps Pipelines, and the building of the actual app works just fine, but we need to be able to deploy database change scripts (e.g. alter procedure, create table, that sort of thing). This is not an Azure SQL database; it's a regular SQL database - actually multiple databases as each of our clients who use the application gets their own copy of the database on our server. So I might have 5 SQL scripts needing to each be deployed to 8 databases, or whatever, for some particular version, and I'm wondering how I can automate that using Pipelines.

I've been trying to read about how to do this but I haven't found much information on deploying to non-Azure databases, and I also need to know how to have the scripts run on multiple databases sequentially or simultaneously. Also I'm a bit confused as to how to specify to the build task which scripts actually need to be run - I have a project in Visual Studio full of SQL scripts and only the ones that have been added or changed need to run. Is there any way I can maybe create a text file with a list of script paths that would need to be run? Or only run the ones with a modified date greater than the date of the previous build, perhaps?

Improve this question
7
  • Ugh! I am having the exact same issue! Did you ever find a solution to this? The answer below was not super helpful. Commented Mar 11, 2020 at 22:03
  • I'm not all that familiar with this, but it looks like we're switching over to something called DACPAC at some point... might want to look into that? Commented Mar 12, 2020 at 20:06
  • 1
    We do use dacpacs, the problem is as you laid out however. We have several databases that need to be deployed to (using those dacpacs) and it seems like all the tasks in Pipelines assume a single target and do not provide a looping structure. Commented Mar 25, 2020 at 17:21
  • Is it possible to create a custom task, perhaps? Or just duplicate the task once for each database that needs a deployment? Commented Mar 25, 2020 at 19:57
  • You can clone tasks, and I don't know what your situation entails, but we have a variable number of client databases that can change over time and per environment, so just cloning tasks would be far too manual of a process. I may look into custom tasks. I know that there is a marketplace where you can use tasks others made, so maybe we can simply make one that does this. Commented Mar 26, 2020 at 20:48

1 Answer 1

Reset to default
2

There isn't build-in task to do it, but there are many extensions in Marketplace can do it, so choose some of them that meet your requirement.

https://marketplace.visualstudio.com/search?term=sql&target=AzureDevOps&category=All%20categories&visibilityQuery=all&sortBy=Relevance

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.