1

Is there a way to automate the task of copying all the data from a database on one server to an identical database on another server using Azure DevOps?

All our web application and desktop application code that interacts with the SQL server database is already in CI/CD pipelines in Azure DevOps.

The scenario is we have a development server that has application + database. Azure DevOps auto deploys everything to the dev server first. Then a process runs to populate the database on dev - this takes long because of the amount of data to process. We verify then approve it for deployment to a test server, and then eventually to staging/production.

I wanted to use the DACPAC file from the database and use that to create a SQL Server Database Project in Visual Studio this way we can source control it in DevOps and then use the SQL Server Database Deploy task in Azure DevOps to update the database whenever there is a change. So that is supposed to take care of any schema changes.

However I'm stuck on the next step which is what to do with the data after the deployment is complete? I need to migrate all the updated data from dev server to test. I could rerun the tool we use to populate the data again on test, but it could take up to a day to run.

I found there are tools in SQL Management Studio like

  • SQL Server Import and Export Wizard
  • Restore task
  • Trigger an SQL Agent Job from DevOps (maybe from PowerShell?) that it programmed to do the data migration?

But I'm not sure how I can access these from an Azure DevOps pipeline. Maybe Azure DevOps isn't the right tool for this task?

I'm open to alternatives to anything mentioned above in case I'm on the wrong track.

EDIT: The data takes a long time to generate and it needs to be used for both dev and test (i.e. it is ok that the dev data is the same as the test data, in fact that is preferred). The reason I ask the above question is to save time, because the process used to generate the data takes very long time. It would be faster to copy the data between databases instead of running the data generation process on each server whenever we have an update.

Improve this question
2
  • This is an excellent question. The thing is.... that actual data you need to migrate might be different. Surely you don't want a full copy of the records that you have? Surely your dev environment has dev data that shouldn't be in prod (and isn't up to date).
    – Nick.Mc
    Nov 9, 2021 at 6:28
  • @Nick.McDermaid Thats a good point. However imagine the data like a library, maybe a collection of articles from an online source and some data extracted from those articles. Articles get deleted or updated from time to time, so the processing needs to happen regularly to keep our data updated. To generate the data takes many hours to a full day. So my idea to speed things up was to run the data generation only once and then copy the data between instances. One other option might be to generate a subset of the data for development, and only run the full data generation on the test server.
    – erotavlas
    Nov 9, 2021 at 15:59

1 Answer 1

Reset to default
1

Maybe you could try to copy database using dbatools

https://docs.dbatools.io/Copy-DbaDatabase

calling a powershell script within AzureDevops but in this way you will copy everything and I don't think it's what you want.

You could eventually place the insert/update scripts in a post-deploy script in the database project so you can apply the same script to production during dacpac deploy.

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.