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.