Migrate Citation Tool Database

[jose-perales]

• currently exists as a function app, likely keep as funciton app but consider other options.

Task List

• Bicep deployment for the SQL Server DB
• run schema migration on db for citation schema
• [ ]

[raymond-nassar]

@audols picking this up

[audols]

Started on this by drafting and running the SQL Server/DB Bicep script. On the customer side, we spun up tables by starting the Azure Function locally and setting syncing to "TRUE", with subsequent scripts run for template/question/form creation. Therefore, this user story requires (a) SQL Server & DB Bicep deployment and (b) storage account bicep deployment (this script already exists on the excitation tool), since the function app needs a storage account to point to in order to run.

I'm currently trying to figure out how to set up the correct permissions here, such that a locally run or Azure-based function app has the necessary db_datawriter and db_datareader access. Not sure if this is possible through bicep alone as these are database-level roles and not server-level role assignments.

I'm also wondering if I'll need to set this up within a v-net, since current approach just allows for access from individual IP addresses.

[jose-perales]

nsg vs allowlisting individual ip's. considering managed identity as an alternate approach