Domain 4 of 5 · Chapter 2 of 3

Automate deployment of database resources

Unlock the complete study guide + 25 practice exams totaling 1,500 questions.

Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.

14-day refund guarantee on one-time purchases and your first subscription charge.

Included in this chapter:

  • Templates and scripts: two routes to Resource Manager
  • Who may deploy: Azure RBAC, not database roles
  • Declaring a logical server and database in Bicep
  • Dependencies decide deployment order
  • Parameters and secure values
  • Modules: reuse one SQL definition
  • Loops: a collection of databases
  • Deployment modes: what a redeploy changes
  • Previewing with what-if
  • Azure PowerShell: build the SQL resource hierarchy
  • Azure PowerShell: change an existing resource
  • Azure CLI: build the SQL resource hierarchy
  • Azure CLI: change an existing resource
  • Azure CLI scripts: --no-wait and read-back
  • Azure CLI scripts: --query and output formats
  • Three failure stages: validation, preflight, deployment
  • Reading past DeploymentFailed
  • Policy and locks: limits that permissions can't lift
  • Registration, SKU, quota, and retryable errors
  • After a failure: what stays deployed
  • Exam-pattern recognition

Template versus script: the same Azure SQL resources from each route

AspectTemplate (Bicep or ARM JSON)Azure PowerShell scriptAzure CLI script
ModelDeclarative: one file states the final state; Resource Manager orders and parallelizesImperative: cmdlets run in the order writtenImperative: commands run in the order written
Logical serverResource Microsoft.Sql/serversNew-AzSqlServeraz sql server create
DatabaseResource Microsoft.Sql/servers/databases with parent set to the serverNew-AzSqlDatabaseaz sql db create --server
Change an existing databaseRedeploy the file with every intended non-default propertySet-AzSqlDatabaseaz sql db update
Submit a template (template route, from either tool)Submitted by either tool's deployment commandNew-AzResourceGroupDeployment -TemplateFileaz deployment group create --template-file
Preview a template's changes (from either tool)What-if operationNew-AzResourceGroupDeployment -WhatIfaz deployment group what-if
AuthorizationAzure RBAC write access plus Microsoft.Resources/deployments operationsAzure RBAC role that covers each resource written, such as SQL DB Contributor for databasesAzure RBAC role that covers each resource written, such as SQL DB Contributor for databases

Decision tree

Deployment failed:did the Bicepfile compile?NoValidationerrorbicep buildYesListed indeployment history?NoValidation orpreflight errorActivity logYesOnly theDeploymentFailedwrapper shown?YesFaileddeploymentoperationholds the codeNoDeployment erroract on its code

Cheat sheet

  • Bicep expresses the same Azure resource model as an ARM JSON template
  • Bicep references establish dependencies while unrelated resources deploy in parallel
  • Template parameters make environments reusable but secrets require secure parameter types
  • Bicep modules package reusable deployments and expose explicit outputs
  • Bicep loops create named resource collections and can limit deployment concurrency
  • Incremental deployment preserves unlisted resources but reapplies each declared resource's properties
  • What-if previews resource changes without applying them
  • Resource deployment authorization is separate from permission to run SQL statements
  • PowerShell provisioning selects the subscription before creating the SQL resource hierarchy
  • Managed Instance provisioning and updates use instance-specific Az.Sql cmdlets
  • PowerShell database settings and shared pool capacity use different cmdlets
  • Azure CLI uses separate commands for the resource group, logical server, and database
  • Azure CLI changes an existing database's service configuration with db update
  • CLI automation should distinguish submission from the resulting resource state
  • CLI queries select result fields before the output format renders them
  • Managed Instance CLI updates use separate options for tier, hardware, compute, and storage
  • Deployment errors must be diagnosed at the stage where they occur
  • The failed resource operation is more diagnostic than the outer DeploymentFailed message
  • Policy and management locks constrain deployments independently of the caller's resource permissions
  • Resource availability failures must be separated from subscription registration and quota failures
  • A failed deployment can leave successfully created resources in place
  • Deployment retries should respect throttling and operations already in progress

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. https://learn.microsoft.com/en-us/azure/azure-sql/database/logical-servers
  2. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/overview
  3. https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/create-and-configure-database-powershell
  4. https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/create-and-configure-database-cli
  5. https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-create-bicep-quickstart
  6. https://learn.microsoft.com/en-us/azure/azure-monitor/fundamentals/activity-log
  7. https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/control-plane-and-data-plane
  8. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  9. https://learn.microsoft.com/en-us/azure/azure-resource-manager/templates/deploy-what-if
  10. https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles/databases
  11. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/common-deployment-errors
  12. https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/resource-providers-and-types
  13. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/deploy-powershell
  14. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/resource-dependencies
  15. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/error-not-found
  16. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/parameters
  17. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/find-error-code
  18. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/deploy-cli
  19. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/modules
  20. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/loops
  21. https://learn.microsoft.com/en-us/azure/azure-resource-manager/templates/deployment-modes
  22. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/deployment-stacks
  23. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/deploy-what-if
  24. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/bicep-functions-resource
  25. https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqlelasticpool
  26. https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqldatabase
  27. https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqlinstance
  28. https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabase
  29. https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqlelasticpool
  30. https://learn.microsoft.com/en-us/azure/azure-sql/database/purchasing-models
  31. https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqlinstance
  32. https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-create-configure
  33. https://learn.microsoft.com/en-us/cli/azure/account
  34. https://learn.microsoft.com/en-us/cli/azure/sql/server
  35. https://learn.microsoft.com/en-us/cli/azure/sql/db
  36. https://learn.microsoft.com/en-us/cli/azure/sql/elastic-pool
  37. https://learn.microsoft.com/en-us/cli/azure/sql/mi
  38. https://learn.microsoft.com/en-us/cli/azure/deployment/group
  39. https://learn.microsoft.com/en-us/cli/azure/format-output-azure-cli
  40. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/quickstart-troubleshoot-bicep-deployment
  41. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/error-sku-not-available
  42. https://learn.microsoft.com/en-us/cli/azure/monitor/activity-log
  43. https://learn.microsoft.com/en-us/azure/azure-monitor/platform/activity-log-schema
  44. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/error-policy-requestdisallowedbypolicy
  45. https://learn.microsoft.com/en-us/azure/governance/policy/concepts/exemption-structure
  46. https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/lock-resources
  47. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/error-register-resource-provider
  48. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/error-resource-quota
  49. https://learn.microsoft.com/en-us/azure/azure-resource-manager/management/request-limits-and-throttling
  50. https://learn.microsoft.com/en-us/azure/azure-resource-manager/troubleshooting/quickstart-troubleshoot-arm-deployment
  51. https://learn.microsoft.com/en-us/azure/azure-resource-manager/templates/rollback-on-error