Ability to execute SQL statements in Resource Manager template
Hello, here are few automation use cases
Copy and overwrite existing database (or delete existing database). Database DB1 on server A is restored from database DB2 on server B. Currently we can copy databases using Resource Manager template but we cannot overwrite the existing database. We have to use powershell or connect using SQL Management studio to first delete the target database and then run an ARM template to copy a database.
Create contained user for a database after it is copied. Just like in the previous use case. We copy database via ARM template but afterwards we have to connect to that database and run custom SQL script to create contained user.
For both of these use cases it would be helpful to execute SQL statement on a database in an ARM template. It would be similar to running a powershell in a VM extension.
Gary Gallaher commented
I deploy an ARM template with 2 databases. Then I have to run a custom script after to 'configure' roles, etc. Any way to do this in the ARM template deployment?
Rajinder Singh commented
We have a need to execute sql script to load reference data after provisioning SQL DB in an ARM template. Terraform has null provisioner which allows ability to execute a script. Something similar in ARM would be helpful
I have the same case. I want to run sql script from my arm template as a part of deployment.
Mike Webber commented
Use case 2 is similar to ours. We would like the ability to have VSTS perform the ARM deployment to create the database resource. Then create a contained database user for the service principal used in the VSTS service endpoint. Then VSTS would be able to deploy the SQL scripts in a subsequent task without requiring VSTS to have or use the SA credentials. This is very important when sharing the same logical server across multiple applications and teams.