I'm setting up Build Automation for a MS-SQL project. One of the requirements is to initialize the database with data. I have them as SQL scripts. I'm trying to figure out what the best way to inizialize a database after it is deployed using VSTS build and release automation.
Depends on where the source data is when considering how you want to initialise the database.
- If the data pre-exists in another SQL-Azure Database you could create a BACPAC and export the file to a blob store in Azure. Then using PowerShell in your pipeline import the data into your created database. Bare in mind this will be time costly dependent on the amount of data and the size of the database.
- If the data is only small, an approach we have used before to build and populate tables is a DACPAC. Here we had the create scripts for our tables and then stored procedures with static reference data stored in insert statements which were run after the DAC built the tables & indexes etc. Here is an example in how to create a DACPAC project in Visual Studio and populate from an exisiting database. Alternatively if you're building from scratch you can add the required database objects to the solution and create them using SQL Statements.
- The third option would be streaming data from a source using an Azure Function and blob storage however without knowing more about your set up I can't advise more.
Hope that helps