0

The Situation

I have an Azure Devops build pipeline that is building and deploying to an existing AzureSql Database instance via the outputted .dacpac.

I would like to have the ability to run a script or execute API calls to create new AzureSql database instances based on that project. I have found the New-AzSqlDatabaseImport powershell cmdlet that ALMOST lets me do that, requiring a .bacpac rather than a .dacpac. I attempted to use the .dacpac and naturally the process failed.

The Question

  1. Can I output a .bacpac from my SqlProj build process?
  2. Alternatively is there a way to create a new database and have that database schema imported from the dacpac in a relatively smooth elegant fashion?
CodeWarrior
  • 7,388
  • 7
  • 51
  • 78
  • Did you start by understanding what the difference between a BACPAC and a DACPAC is? Have you looked up how to deploy schema to an blank Azure SQL database from a DACPAC? The documentation and Google should hold answers to all of these questions. – Daniel Mann Feb 26 '22 at 17:32
  • @DanielMann Yes, I understand that dacpac contains schema, and bacpac contains schema and data. I saw articles and blog posts for multi step process for provisioning a new database and deploying a dacpac to it. The above question was effectively a "Is there a better/more accepted way of doing this". Did you get the sense, given the information that I posted above that I had not done ANY reading on the subject? – CodeWarrior Mar 02 '22 at 00:12

1 Answers1

0

What we have gone with is the following:

  1. Host a "template" database alongside the other databases.
  2. Update the "template" database during each update cycle with the dacpac changes.
  3. On new user/organization creation, execute single call powershell script that performs a quick copy of the "template" database. New-AzSqlDatabaseCopy

This appears to go faster than separate provision and dacpac deploy, and is a single call to execute. In the future the powershell execution is likely to be changed to an Azure API call.

CodeWarrior
  • 7,388
  • 7
  • 51
  • 78
  • Have you found any other way of doing this? I was thinking about introducing some sql script that would populate database with some initial entities, have you tried such approach? – J. Krz Mar 13 '23 at 12:55
  • We ended up doing the above. All taken care of with some powershell. It IS all automated and runs fairly quickly. All updates to the "template" database are done via DeployDACPAC during our pipeline deployment which DOES take extra time, but it ends up being worth it. – CodeWarrior Mar 17 '23 at 01:13