Microsoft.Sql servers/databases 2023-05-01-preview
- Latest
- 2023-05-01-preview
- 2023-02-01-preview
- 2022-11-01-preview
- 2022-08-01-preview
- 2022-05-01-preview
- 2022-02-01-preview
- 2021-11-01
- 2021-11-01-preview
- 2021-08-01-preview
- 2021-05-01-preview
- 2021-02-01-preview
- 2020-11-01-preview
- 2020-08-01-preview
- 2020-02-02-preview
- 2019-06-01-preview
- 2017-10-01-preview
- 2017-03-01-preview
- 2014-04-01
Bicep resource definition
The servers/databases resource type can be deployed with operations that target:
- Resource groups - See resource group deployment commands
For a list of changed properties in each API version, see change log.
Resource format
To create a Microsoft.Sql/servers/databases resource, add the following Bicep to your template.
resource symbolicname 'Microsoft.Sql/servers/databases@2023-05-01-preview' = {
name: 'string'
location: 'string'
tags: {
tagName1: 'tagValue1'
tagName2: 'tagValue2'
}
sku: {
capacity: int
family: 'string'
name: 'string'
size: 'string'
tier: 'string'
}
parent: resourceSymbolicName
identity: {
type: 'string'
userAssignedIdentities: {
{customized property}: {}
}
}
properties: {
autoPauseDelay: int
availabilityZone: 'string'
catalogCollation: 'string'
collation: 'string'
createMode: 'string'
elasticPoolId: 'string'
encryptionProtector: 'string'
encryptionProtectorAutoRotation: bool
federatedClientId: 'string'
freeLimitExhaustionBehavior: 'string'
highAvailabilityReplicaCount: int
isLedgerOn: bool
keys: {
{customized property}: {}
}
licenseType: 'string'
longTermRetentionBackupResourceId: 'string'
maintenanceConfigurationId: 'string'
manualCutover: bool
maxSizeBytes: int
minCapacity: json('decimal-as-string')
performCutover: bool
preferredEnclaveType: 'string'
readScale: 'string'
recoverableDatabaseId: 'string'
recoveryServicesRecoveryPointId: 'string'
requestedBackupStorageRedundancy: 'string'
restorableDroppedDatabaseId: 'string'
restorePointInTime: 'string'
sampleName: 'string'
secondaryType: 'string'
sourceDatabaseDeletionDate: 'string'
sourceDatabaseId: 'string'
sourceResourceId: 'string'
useFreeLimit: bool
zoneRedundant: bool
}
}
Property values
servers/databases
| Name | Description | Value |
|---|---|---|
| name | The resource name See how to set names and types for child resources in Bicep. |
string (required) Character limit: 1-128 Valid characters: Can't use: <>*%&:\/? or control charactersCan't end with period or space. |
| location | Resource location. | string (required) |
| tags | Resource tags. | Dictionary of tag names and values. See Tags in templates |
| sku | The database SKU. The list of SKUs may vary by region and support offer. To determine the SKUs (including the SKU name, tier/edition, family, and capacity) that are available to your subscription in an Azure region, use the Capabilities_ListByLocation REST API or one of the following commands:Azure CLI: az sql db list-editions -l {location} -o table Azure PowerShell: Get-AzSqlServerServiceObjective -Location {location} |
Sku |
| parent | In Bicep, you can specify the parent resource for a child resource. You only need to add this property when the child resource is declared outside of the parent resource. For more information, see Child resource outside parent resource. |
Symbolic name for resource of type: servers |
| identity | The Azure Active Directory identity of the database. | DatabaseIdentity |
| properties | Resource properties. | DatabaseProperties |
DatabaseIdentity
| Name | Description | Value |
|---|---|---|
| type | The identity type | 'None' 'UserAssigned' |
| userAssignedIdentities | The resource ids of the user assigned identities to use | DatabaseIdentityUserAssignedIdentities |
DatabaseIdentityUserAssignedIdentities
| Name | Description | Value |
|---|---|---|
| {customized property} | DatabaseUserIdentity |
DatabaseUserIdentity
This object doesn't contain any properties to set during deployment. All properties are ReadOnly.
DatabaseProperties
| Name | Description | Value |
|---|---|---|
| autoPauseDelay | Time in minutes after which database is automatically paused. A value of -1 means that automatic pause is disabled | int |
| availabilityZone | Specifies the availability zone the database is pinned to. | '1' '2' '3' 'NoPreference' |
| catalogCollation | Collation of the metadata catalog. | 'DATABASE_DEFAULT' 'SQL_Latin1_General_CP1_CI_AS' |
| collation | The collation of the database. | string |
| createMode | Specifies the mode of database creation. Default: regular database creation. Copy: creates a database as a copy of an existing database. sourceDatabaseId must be specified as the resource ID of the source database. Secondary: creates a database as a secondary replica of an existing database. sourceDatabaseId must be specified as the resource ID of the existing primary database. PointInTimeRestore: Creates a database by restoring a point in time backup of an existing database. sourceDatabaseId must be specified as the resource ID of the existing database, and restorePointInTime must be specified. Recovery: Creates a database by restoring a geo-replicated backup. sourceDatabaseId must be specified as the recoverable database resource ID to restore. Restore: Creates a database by restoring a backup of a deleted database. sourceDatabaseId must be specified. If sourceDatabaseId is the database's original resource ID, then sourceDatabaseDeletionDate must be specified. Otherwise sourceDatabaseId must be the restorable dropped database resource ID and sourceDatabaseDeletionDate is ignored. restorePointInTime may also be specified to restore from an earlier point in time. RestoreLongTermRetentionBackup: Creates a database by restoring from a long term retention vault. recoveryServicesRecoveryPointResourceId must be specified as the recovery point resource ID. Copy, Secondary, and RestoreLongTermRetentionBackup are not supported for DataWarehouse edition. |
'Copy' 'Default' 'OnlineSecondary' 'PointInTimeRestore' 'Recovery' 'Restore' 'RestoreExternalBackup' 'RestoreExternalBackupSecondary' 'RestoreLongTermRetentionBackup' 'Secondary' |
| elasticPoolId | The resource identifier of the elastic pool containing this database. | string |
| encryptionProtector | The azure key vault URI of the database if it's configured with per Database Customer Managed Keys. | string |
| encryptionProtectorAutoRotation | The flag to enable or disable auto rotation of database encryption protector AKV key. | bool |
| federatedClientId | The Client id used for cross tenant per database CMK scenario | string Constraints: Min length = 36 Max length = 36 Pattern = ^[0-9a-fA-F]{8}-([0-9a-fA-F]{4}-){3}[0-9a-fA-F]{12}$ |
| freeLimitExhaustionBehavior | Specifies the behavior when monthly free limits are exhausted for the free database. AutoPause: The database will be auto paused upon exhaustion of free limits for remainder of the month. BillForUsage: The database will continue to be online upon exhaustion of free limits and any overage will be billed. |
'AutoPause' 'BillOverUsage' |
| highAvailabilityReplicaCount | The number of secondary replicas associated with the database that are used to provide high availability. Not applicable to a Hyperscale database within an elastic pool. | int |
| isLedgerOn | Whether or not this database is a ledger database, which means all tables in the database are ledger tables. Note: the value of this property cannot be changed after the database has been created. | bool |
| keys | The resource ids of the user assigned identities to use | DatabasePropertiesKeys |
| licenseType | The license type to apply for this database. LicenseIncluded if you need a license, or BasePrice if you have a license and are eligible for the Azure Hybrid Benefit. |
'BasePrice' 'LicenseIncluded' |
| longTermRetentionBackupResourceId | The resource identifier of the long term retention backup associated with create operation of this database. | string |
| maintenanceConfigurationId | Maintenance configuration id assigned to the database. This configuration defines the period when the maintenance updates will occur. | string |
| manualCutover | Whether or not customer controlled manual cutover needs to be done during Update Database operation to Hyperscale tier. This property is only applicable when scaling database from Business Critical/General Purpose/Premium/Standard tier to Hyperscale tier. When manualCutover is specified, the scaling operation will wait for user input to trigger cutover to Hyperscale database. To trigger cutover, please provide 'performCutover' parameter when the Scaling operation is in Waiting state. |
bool |
| maxSizeBytes | The max size of the database expressed in bytes. | int |
| minCapacity | Minimal capacity that database will always have allocated, if not paused To specify a decimal value, use the json() function. | int or json decimal |
| performCutover | To trigger customer controlled manual cutover during the wait state while Scaling operation is in progress. This property parameter is only applicable for scaling operations that are initiated along with 'manualCutover' parameter. This property is only applicable when scaling database from Business Critical/General Purpose/Premium/Standard tier to Hyperscale tier is already in progress. When performCutover is specified, the scaling operation will trigger cutover and perform role-change to Hyperscale database. |
bool |
| preferredEnclaveType | Type of enclave requested on the database i.e. Default or VBS enclaves. | 'Default' 'VBS' |
| readScale | The state of read-only routing. If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica in the same region. Not applicable to a Hyperscale database within an elastic pool. | 'Disabled' 'Enabled' |
| recoverableDatabaseId | The resource identifier of the recoverable database associated with create operation of this database. | string |
| recoveryServicesRecoveryPointId | The resource identifier of the recovery point associated with create operation of this database. | string |
| requestedBackupStorageRedundancy | The storage account type to be used to store backups for this database. | 'Geo' 'GeoZone' 'Local' 'Zone' |
| restorableDroppedDatabaseId | The resource identifier of the restorable dropped database associated with create operation of this database. | string |
| restorePointInTime | Specifies the point in time (ISO8601 format) of the source database that will be restored to create the new database. | string |
| sampleName | The name of the sample schema to apply when creating this database. | 'AdventureWorksLT' 'WideWorldImportersFull' 'WideWorldImportersStd' |
| secondaryType | The secondary type of the database if it is a secondary. Valid values are Geo, Named and Standby. | 'Geo' 'Named' 'Standby' |
| sourceDatabaseDeletionDate | Specifies the time that the database was deleted. | string |
| sourceDatabaseId | The resource identifier of the source database associated with create operation of this database. | string |
| sourceResourceId | The resource identifier of the source associated with the create operation of this database. This property is only supported for DataWarehouse edition and allows to restore across subscriptions. When sourceResourceId is specified, sourceDatabaseId, recoverableDatabaseId, restorableDroppedDatabaseId and sourceDatabaseDeletionDate must not be specified and CreateMode must be PointInTimeRestore, Restore or Recover. When createMode is PointInTimeRestore, sourceResourceId must be the resource ID of the existing database or existing sql pool, and restorePointInTime must be specified. When createMode is Restore, sourceResourceId must be the resource ID of restorable dropped database or restorable dropped sql pool. When createMode is Recover, sourceResourceId must be the resource ID of recoverable database or recoverable sql pool. When source subscription belongs to a different tenant than target subscription, “x-ms-authorization-auxiliary” header must contain authentication token for the source tenant. For more details about “x-ms-authorization-auxiliary” header see /azure/azure-resource-manager/management/authenticate-multi-tenant |
string |
| useFreeLimit | Whether or not the database uses free monthly limits. Allowed on one database in a subscription. | bool |
| zoneRedundant | Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones. | bool |
DatabasePropertiesKeys
| Name | Description | Value |
|---|---|---|
| {customized property} | DatabaseKey |
DatabaseKey
This object doesn't contain any properties to set during deployment. All properties are ReadOnly.
Sku
| Name | Description | Value |
|---|---|---|
| capacity | Capacity of the particular SKU. | int |
| family | If the service has different generations of hardware, for the same SKU, then that can be captured here. | string |
| name | The name of the SKU, typically, a letter + Number code, e.g. P3. | string (required) |
| size | Size of the particular SKU | string |
| tier | The tier or edition of the particular SKU, e.g. Basic, Premium. | string |
Quickstart templates
The following quickstart templates deploy this resource type.
| Template | Description |
|---|---|
Django App with SQL Databases |
This template uses the Azure Linux CustomScript extension to deploy an application. This example creates an Ubuntu VM, does a silent install of Python, Django and Apache, then creates a simple Django app. The template also creates a SQL Database, with a sample table with some sample data which displayed in the web browser using a query |
| EPiserverCMS in Azure |
This template allows you to create resources required for EpiServerCMS deployment in Azure |
| Autoscale LANSA Windows VM ScaleSet with Azure SQL Database |
The template deploys a Windows VMSS with a desired count of VMs in the scale set and a LANSA MSI to install into each VM. Once the VM Scale Set is deployed a custom script extension is used to install the LANSA MSI) |
| Deploy Octopus Deploy 3.0 with a trial license |
This template allows you to deploy a single Octopus Deploy 3.0 server with a trial license. This will deploy on a single Windows Server 2012R2 VM (Standard D2) and SQL DB (S1 tier) into the location specified for the Resource Group. |
| Orchard CMS Video Portal Web App |
This template provides a easy way to deploy Orchard CMS on Azure App Service Web Apps with the Azure Media Services module enabled and configured. |
| Scalable Umbraco CMS Web App |
This template provides a easy way to deploy umbraco CMS web app on Azure App Service Web Apps. |
| Simple Umbraco CMS Web App |
This template provides a easy way to deploy umbraco CMS web app on Azure App Service Web Apps. |
| Enterprise Governance-AppService, SQL DB, AD, OMS, Runbooks |
Cloud adoption for an Enterprise, small or large, require responsible and efficient governance models to derive value from their cloud deployments. CloudWise (a code-name for the solution), is a composite solution available from Azure Partner QuickStarts, is an adoption enabler for Customers, System Integrators and Partners alike, that delivers a self-serviceable, automated governance and operations solution, focused on Optimizing Your Costs, Improve Your Application(s) Reliability, Reducing Business Risk. The solution highlights the core governance pillars of Visibility and Control. |
| eShop Website with ILB ASE |
An App Service Environment is a Premium service plan option of Azure App Service that provides a fully isolated and dedicated environment for securely running Azure App Service apps at high scale, including Web Apps, Mobile Apps, and API Apps. |
| WebApp consuming a Azure SQL Private Endpoint |
This template shows how to create a Web app that consumes a private endpoint pointing to Azure SQL Server |
| Remote Desktop Services with High Availability |
This ARM Template sample code will deploy a Remote Desktop Services 2019 Session Collection lab with high availability. The goal is to deploy a fully redundant, highly available solution for Remote Desktop Services, using Windows Server 2019. |
| Deploy the Sports Analytics on Azure Architecture |
Creates an Azure storage account with ADLS Gen 2 enabled, an Azure Data Factory instance with linked services for the storage account (an the Azure SQL Database if deployed), and an Azure Databricks instance. The AAD identity for the user deploying the template and the managed identity for the ADF instance will be granted the Storage Blob Data Contributor role on the storage account. There are also options to deploy an Azure Key Vault instance, an Azure SQL Database, and an Azure Event Hub (for streaming use cases). When an Azure Key Vault is deployed, the data factory managed identity and the AAD identity for the user deploying the template will be granted the Key Vault Secrets User role. |
| Web App with a SQL Database, Azure Cosmos DB, Azure Search |
This template provisions a Web App, a SQL Database, Azure Cosmos DB, Azure Search and Application Insights. |
| Migrate to Azure SQL database using Azure DMS |
The Azure Database Migration Service (DMS) is designed to streamline the process of migrating on-premises databases to Azure. DMS will simplify the migration of existing on-premises SQL Server and Oracle databases to Azure SQL Database, Azure SQL Managed Instance or Microsoft SQL Server in an Azure Virtual Machine. This template would deploy an instance of Azure Database Migration service, an Azure VM with SQL server installed on it which will act as a Source server with pre created database on it and a Target Azure SQL DB server which will have a pre-created schema of the database to be migrated from Source to Target server. The template will also deploy the required resources like NIC, vnet etc for supporting the Source VM, DMS service and Target server. |
| HDInsight with custom Ambari + Hive Metastore DB in VNET |
This template allows you to create an HDInsight cluster in an existing virtual network with a new SQL DB that serves as both a custom Ambari DB and Hive Metastore. You must have an existing SQL Sever, storage account, and VNET. |
| Deploy a HDInsight cluster and a SQL database |
This template allows you to create a HDInsight cluster and a SQL Database for testing Sqoop. |
| Private Endpoint example |
This template shows how to create a private endpoint pointing to Azure SQL Server |
| Azure SQL Server with Auditing written to Event Hub |
This template allows you to deploy an Azure SQL server with Auditing enabled to write audit logs to Event Hub |
| Azure SQL Server with Auditing written to Log Analytics |
This template allows you to deploy an Azure SQL server with Auditing enabled to write audit logs to Log Analytics (OMS workspace) |
| Dedicated SQL pool with Transparent Encryption |
Creates a SQL Server and a Dedicated SQL pool (formerly SQL DW) with Transparent Data Encryption. |
| Create a SQL Server and Database |
This template allows you to create SQL Database and Server. |
| Provision a SQL Database with TDE |
This template provisions a SQL Server with firewall opened for Azure traffic and a SQL database with Transparent Data Encryption (TDE). |
| Deploy a new SQL Elastic Pool |
This template allows you to deploy a new SQL Elastic Pool with its new associated SQL Server and new SQL Databases to assign to it. |
| Create Azure SQL Servers and Database with Failover Group |
Creates two Azure SQL servers, a database, and a failover group. |
| App Service Environment with Azure SQL backend |
This template creates an App Service Environment with an Azure SQL backend along with private endpoints along with associated resources typically used in an private/isolated environment. |
| Provision a Mobile App with a SQL Database |
This template provisions a Mobile App, SQL Database, and Notification Hub. It configures a connection string in the mobile app for the database and notification hub. |
| Web App with Managed Identity, SQL Server and ΑΙ |
Simple example to deploy Azure infrastructure for app + data + managed identity + monitoring |
| Create a Web App + Redis Cache + SQL DB with a template |
This template creates an Azure Web App with Redis cache and a SQL Database. |
| Provision a Web App with a SQL Database |
This template provisions a Web App, a SQL Database, AutoScale settings, Alert rules, and App Insights. It configures a connection string in the web app for the database. |
| Create, configure and deploy Web Application to an Azure VM |
Create and configure a Windows VM with SQL Azure database, and deploy web application to the environment using PowerShell DSC |
| Sonarqube Docker Web App on Linux with Azure SQL |
This template deploys Sonarqube in an Azure App Service web app Linux container using the official Sonarqube image and backed by an Azure SQL Server. |
ARM template resource definition
The servers/databases resource type can be deployed with operations that target:
- Resource groups - See resource group deployment commands
For a list of changed properties in each API version, see change log.
Resource format
To create a Microsoft.Sql/servers/databases resource, add the following JSON to your template.
{
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2023-05-01-preview",
"name": "string",
"location": "string",
"tags": {
"tagName1": "tagValue1",
"tagName2": "tagValue2"
},
"sku": {
"capacity": "int",
"family": "string",
"name": "string",
"size": "string",
"tier": "string"
},
"identity": {
"type": "string",
"userAssignedIdentities": {
"{customized property}": {}
}
},
"properties": {
"autoPauseDelay": "int",
"availabilityZone": "string",
"catalogCollation": "string",
"collation": "string",
"createMode": "string",
"elasticPoolId": "string",
"encryptionProtector": "string",
"encryptionProtectorAutoRotation": "bool",
"federatedClientId": "string",
"freeLimitExhaustionBehavior": "string",
"highAvailabilityReplicaCount": "int",
"isLedgerOn": "bool",
"keys": {
"{customized property}": {}
},
"licenseType": "string",
"longTermRetentionBackupResourceId": "string",
"maintenanceConfigurationId": "string",
"manualCutover": "bool",
"maxSizeBytes": "int",
"minCapacity": "[json('decimal-as-string')]",
"performCutover": "bool",
"preferredEnclaveType": "string",
"readScale": "string",
"recoverableDatabaseId": "string",
"recoveryServicesRecoveryPointId": "string",
"requestedBackupStorageRedundancy": "string",
"restorableDroppedDatabaseId": "string",
"restorePointInTime": "string",
"sampleName": "string",
"secondaryType": "string",
"sourceDatabaseDeletionDate": "string",
"sourceDatabaseId": "string",
"sourceResourceId": "string",
"useFreeLimit": "bool",
"zoneRedundant": "bool"
}
}
Property values
servers/databases
| Name | Description | Value |
|---|---|---|
| type | The resource type | 'Microsoft.Sql/servers/databases' |
| apiVersion | The resource api version | '2023-05-01-preview' |
| name | The resource name See how to set names and types for child resources in JSON ARM templates. |
string (required) Character limit: 1-128 Valid characters: Can't use: <>*%&:\/? or control charactersCan't end with period or space. |
| location | Resource location. | string (required) |
| tags | Resource tags. | Dictionary of tag names and values. See Tags in templates |
| sku | The database SKU. The list of SKUs may vary by region and support offer. To determine the SKUs (including the SKU name, tier/edition, family, and capacity) that are available to your subscription in an Azure region, use the Capabilities_ListByLocation REST API or one of the following commands:Azure CLI: az sql db list-editions -l {location} -o table Azure PowerShell: Get-AzSqlServerServiceObjective -Location {location} |
Sku |
| identity | The Azure Active Directory identity of the database. | DatabaseIdentity |
| properties | Resource properties. | DatabaseProperties |
DatabaseIdentity
| Name | Description | Value |
|---|---|---|
| type | The identity type | 'None' 'UserAssigned' |
| userAssignedIdentities | The resource ids of the user assigned identities to use | DatabaseIdentityUserAssignedIdentities |
DatabaseIdentityUserAssignedIdentities
| Name | Description | Value |
|---|---|---|
| {customized property} | DatabaseUserIdentity |
DatabaseUserIdentity
This object doesn't contain any properties to set during deployment. All properties are ReadOnly.
DatabaseProperties
| Name | Description | Value |
|---|---|---|
| autoPauseDelay | Time in minutes after which database is automatically paused. A value of -1 means that automatic pause is disabled | int |
| availabilityZone | Specifies the availability zone the database is pinned to. | '1' '2' '3' 'NoPreference' |
| catalogCollation | Collation of the metadata catalog. | 'DATABASE_DEFAULT' 'SQL_Latin1_General_CP1_CI_AS' |
| collation | The collation of the database. | string |
| createMode | Specifies the mode of database creation. Default: regular database creation. Copy: creates a database as a copy of an existing database. sourceDatabaseId must be specified as the resource ID of the source database. Secondary: creates a database as a secondary replica of an existing database. sourceDatabaseId must be specified as the resource ID of the existing primary database. PointInTimeRestore: Creates a database by restoring a point in time backup of an existing database. sourceDatabaseId must be specified as the resource ID of the existing database, and restorePointInTime must be specified. Recovery: Creates a database by restoring a geo-replicated backup. sourceDatabaseId must be specified as the recoverable database resource ID to restore. Restore: Creates a database by restoring a backup of a deleted database. sourceDatabaseId must be specified. If sourceDatabaseId is the database's original resource ID, then sourceDatabaseDeletionDate must be specified. Otherwise sourceDatabaseId must be the restorable dropped database resource ID and sourceDatabaseDeletionDate is ignored. restorePointInTime may also be specified to restore from an earlier point in time. RestoreLongTermRetentionBackup: Creates a database by restoring from a long term retention vault. recoveryServicesRecoveryPointResourceId must be specified as the recovery point resource ID. Copy, Secondary, and RestoreLongTermRetentionBackup are not supported for DataWarehouse edition. |
'Copy' 'Default' 'OnlineSecondary' 'PointInTimeRestore' 'Recovery' 'Restore' 'RestoreExternalBackup' 'RestoreExternalBackupSecondary' 'RestoreLongTermRetentionBackup' 'Secondary' |
| elasticPoolId | The resource identifier of the elastic pool containing this database. | string |
| encryptionProtector | The azure key vault URI of the database if it's configured with per Database Customer Managed Keys. | string |
| encryptionProtectorAutoRotation | The flag to enable or disable auto rotation of database encryption protector AKV key. | bool |
| federatedClientId | The Client id used for cross tenant per database CMK scenario | string Constraints: Min length = 36 Max length = 36 Pattern = ^[0-9a-fA-F]{8}-([0-9a-fA-F]{4}-){3}[0-9a-fA-F]{12}$ |
| freeLimitExhaustionBehavior | Specifies the behavior when monthly free limits are exhausted for the free database. AutoPause: The database will be auto paused upon exhaustion of free limits for remainder of the month. BillForUsage: The database will continue to be online upon exhaustion of free limits and any overage will be billed. |
'AutoPause' 'BillOverUsage' |
| highAvailabilityReplicaCount | The number of secondary replicas associated with the database that are used to provide high availability. Not applicable to a Hyperscale database within an elastic pool. | int |
| isLedgerOn | Whether or not this database is a ledger database, which means all tables in the database are ledger tables. Note: the value of this property cannot be changed after the database has been created. | bool |
| keys | The resource ids of the user assigned identities to use | DatabasePropertiesKeys |
| licenseType | The license type to apply for this database. LicenseIncluded if you need a license, or BasePrice if you have a license and are eligible for the Azure Hybrid Benefit. |
'BasePrice' 'LicenseIncluded' |
| longTermRetentionBackupResourceId | The resource identifier of the long term retention backup associated with create operation of this database. | string |
| maintenanceConfigurationId | Maintenance configuration id assigned to the database. This configuration defines the period when the maintenance updates will occur. | string |
| manualCutover | Whether or not customer controlled manual cutover needs to be done during Update Database operation to Hyperscale tier. This property is only applicable when scaling database from Business Critical/General Purpose/Premium/Standard tier to Hyperscale tier. When manualCutover is specified, the scaling operation will wait for user input to trigger cutover to Hyperscale database. To trigger cutover, please provide 'performCutover' parameter when the Scaling operation is in Waiting state. |
bool |
| maxSizeBytes | The max size of the database expressed in bytes. | int |
| minCapacity | Minimal capacity that database will always have allocated, if not paused To specify a decimal value, use the json() function. | int or json decimal |
| performCutover | To trigger customer controlled manual cutover during the wait state while Scaling operation is in progress. This property parameter is only applicable for scaling operations that are initiated along with 'manualCutover' parameter. This property is only applicable when scaling database from Business Critical/General Purpose/Premium/Standard tier to Hyperscale tier is already in progress. When performCutover is specified, the scaling operation will trigger cutover and perform role-change to Hyperscale database. |
bool |
| preferredEnclaveType | Type of enclave requested on the database i.e. Default or VBS enclaves. | 'Default' 'VBS' |
| readScale | The state of read-only routing. If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica in the same region. Not applicable to a Hyperscale database within an elastic pool. | 'Disabled' 'Enabled' |
| recoverableDatabaseId | The resource identifier of the recoverable database associated with create operation of this database. | string |
| recoveryServicesRecoveryPointId | The resource identifier of the recovery point associated with create operation of this database. | string |
| requestedBackupStorageRedundancy | The storage account type to be used to store backups for this database. | 'Geo' 'GeoZone' 'Local' 'Zone' |
| restorableDroppedDatabaseId | The resource identifier of the restorable dropped database associated with create operation of this database. | string |
| restorePointInTime | Specifies the point in time (ISO8601 format) of the source database that will be restored to create the new database. | string |
| sampleName | The name of the sample schema to apply when creating this database. | 'AdventureWorksLT' 'WideWorldImportersFull' 'WideWorldImportersStd' |
| secondaryType | The secondary type of the database if it is a secondary. Valid values are Geo, Named and Standby. | 'Geo' 'Named' 'Standby' |
| sourceDatabaseDeletionDate | Specifies the time that the database was deleted. | string |
| sourceDatabaseId | The resource identifier of the source database associated with create operation of this database. | string |
| sourceResourceId | The resource identifier of the source associated with the create operation of this database. This property is only supported for DataWarehouse edition and allows to restore across subscriptions. When sourceResourceId is specified, sourceDatabaseId, recoverableDatabaseId, restorableDroppedDatabaseId and sourceDatabaseDeletionDate must not be specified and CreateMode must be PointInTimeRestore, Restore or Recover. When createMode is PointInTimeRestore, sourceResourceId must be the resource ID of the existing database or existing sql pool, and restorePointInTime must be specified. When createMode is Restore, sourceResourceId must be the resource ID of restorable dropped database or restorable dropped sql pool. When createMode is Recover, sourceResourceId must be the resource ID of recoverable database or recoverable sql pool. When source subscription belongs to a different tenant than target subscription, “x-ms-authorization-auxiliary” header must contain authentication token for the source tenant. For more details about “x-ms-authorization-auxiliary” header see /azure/azure-resource-manager/management/authenticate-multi-tenant |
string |
| useFreeLimit | Whether or not the database uses free monthly limits. Allowed on one database in a subscription. | bool |
| zoneRedundant | Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones. | bool |
DatabasePropertiesKeys
| Name | Description | Value |
|---|---|---|
| {customized property} | DatabaseKey |
DatabaseKey
This object doesn't contain any properties to set during deployment. All properties are ReadOnly.
Sku
| Name | Description | Value |
|---|---|---|
| capacity | Capacity of the particular SKU. | int |
| family | If the service has different generations of hardware, for the same SKU, then that can be captured here. | string |
| name | The name of the SKU, typically, a letter + Number code, e.g. P3. | string (required) |
| size | Size of the particular SKU | string |
| tier | The tier or edition of the particular SKU, e.g. Basic, Premium. | string |
Quickstart templates
The following quickstart templates deploy this resource type.
| Template | Description |
|---|---|
| Django App with SQL Databases |
This template uses the Azure Linux CustomScript extension to deploy an application. This example creates an Ubuntu VM, does a silent install of Python, Django and Apache, then creates a simple Django app. The template also creates a SQL Database, with a sample table with some sample data which displayed in the web browser using a query |
| EPiserverCMS in Azure |
This template allows you to create resources required for EpiServerCMS deployment in Azure |
| Autoscale LANSA Windows VM ScaleSet with Azure SQL Database |
The template deploys a Windows VMSS with a desired count of VMs in the scale set and a LANSA MSI to install into each VM. Once the VM Scale Set is deployed a custom script extension is used to install the LANSA MSI) |
| Deploy Octopus Deploy 3.0 with a trial license |
This template allows you to deploy a single Octopus Deploy 3.0 server with a trial license. This will deploy on a single Windows Server 2012R2 VM (Standard D2) and SQL DB (S1 tier) into the location specified for the Resource Group. |
| Orchard CMS Video Portal Web App |
This template provides a easy way to deploy Orchard CMS on Azure App Service Web Apps with the Azure Media Services module enabled and configured. |
| Scalable Umbraco CMS Web App |
This template provides a easy way to deploy umbraco CMS web app on Azure App Service Web Apps. |
| Simple Umbraco CMS Web App |
This template provides a easy way to deploy umbraco CMS web app on Azure App Service Web Apps. |
| Enterprise Governance-AppService, SQL DB, AD, OMS, Runbooks |
Cloud adoption for an Enterprise, small or large, require responsible and efficient governance models to derive value from their cloud deployments. CloudWise (a code-name for the solution), is a composite solution available from Azure Partner QuickStarts, is an adoption enabler for Customers, System Integrators and Partners alike, that delivers a self-serviceable, automated governance and operations solution, focused on Optimizing Your Costs, Improve Your Application(s) Reliability, Reducing Business Risk. The solution highlights the core governance pillars of Visibility and Control. |
| eShop Website with ILB ASE |
An App Service Environment is a Premium service plan option of Azure App Service that provides a fully isolated and dedicated environment for securely running Azure App Service apps at high scale, including Web Apps, Mobile Apps, and API Apps. |
| WebApp consuming a Azure SQL Private Endpoint |
This template shows how to create a Web app that consumes a private endpoint pointing to Azure SQL Server |
| Remote Desktop Services with High Availability |
This ARM Template sample code will deploy a Remote Desktop Services 2019 Session Collection lab with high availability. The goal is to deploy a fully redundant, highly available solution for Remote Desktop Services, using Windows Server 2019. |
| Deploy the Sports Analytics on Azure Architecture |
Creates an Azure storage account with ADLS Gen 2 enabled, an Azure Data Factory instance with linked services for the storage account (an the Azure SQL Database if deployed), and an Azure Databricks instance. The AAD identity for the user deploying the template and the managed identity for the ADF instance will be granted the Storage Blob Data Contributor role on the storage account. There are also options to deploy an Azure Key Vault instance, an Azure SQL Database, and an Azure Event Hub (for streaming use cases). When an Azure Key Vault is deployed, the data factory managed identity and the AAD identity for the user deploying the template will be granted the Key Vault Secrets User role. |
| Web App with a SQL Database, Azure Cosmos DB, Azure Search |
This template provisions a Web App, a SQL Database, Azure Cosmos DB, Azure Search and Application Insights. |
| Migrate to Azure SQL database using Azure DMS |
The Azure Database Migration Service (DMS) is designed to streamline the process of migrating on-premises databases to Azure. DMS will simplify the migration of existing on-premises SQL Server and Oracle databases to Azure SQL Database, Azure SQL Managed Instance or Microsoft SQL Server in an Azure Virtual Machine. This template would deploy an instance of Azure Database Migration service, an Azure VM with SQL server installed on it which will act as a Source server with pre created database on it and a Target Azure SQL DB server which will have a pre-created schema of the database to be migrated from Source to Target server. The template will also deploy the required resources like NIC, vnet etc for supporting the Source VM, DMS service and Target server. |
| HDInsight with custom Ambari + Hive Metastore DB in VNET |
This template allows you to create an HDInsight cluster in an existing virtual network with a new SQL DB that serves as both a custom Ambari DB and Hive Metastore. You must have an existing SQL Sever, storage account, and VNET. |
| Deploy a HDInsight cluster and a SQL database |
This template allows you to create a HDInsight cluster and a SQL Database for testing Sqoop. |
| Private Endpoint example |
This template shows how to create a private endpoint pointing to Azure SQL Server |
| Azure SQL Server with Auditing written to Event Hub |
This template allows you to deploy an Azure SQL server with Auditing enabled to write audit logs to Event Hub |
| Azure SQL Server with Auditing written to Log Analytics |
This template allows you to deploy an Azure SQL server with Auditing enabled to write audit logs to Log Analytics (OMS workspace) |
| Dedicated SQL pool with Transparent Encryption |
Creates a SQL Server and a Dedicated SQL pool (formerly SQL DW) with Transparent Data Encryption. |
| Create a SQL Server and Database |
This template allows you to create SQL Database and Server. |
| Provision a SQL Database with TDE |
This template provisions a SQL Server with firewall opened for Azure traffic and a SQL database with Transparent Data Encryption (TDE). |
| Deploy a new SQL Elastic Pool |
This template allows you to deploy a new SQL Elastic Pool with its new associated SQL Server and new SQL Databases to assign to it. |
| Create Azure SQL Servers and Database with Failover Group |
Creates two Azure SQL servers, a database, and a failover group. |
| App Service Environment with Azure SQL backend |
This template creates an App Service Environment with an Azure SQL backend along with private endpoints along with associated resources typically used in an private/isolated environment. |
| Provision a Mobile App with a SQL Database |
This template provisions a Mobile App, SQL Database, and Notification Hub. It configures a connection string in the mobile app for the database and notification hub. |
| Web App with Managed Identity, SQL Server and ΑΙ |
Simple example to deploy Azure infrastructure for app + data + managed identity + monitoring |
| Create a Web App + Redis Cache + SQL DB with a template |
This template creates an Azure Web App with Redis cache and a SQL Database. |
| Provision a Web App with a SQL Database |
This template provisions a Web App, a SQL Database, AutoScale settings, Alert rules, and App Insights. It configures a connection string in the web app for the database. |
| Create, configure and deploy Web Application to an Azure VM |
Create and configure a Windows VM with SQL Azure database, and deploy web application to the environment using PowerShell DSC |
| Sonarqube Docker Web App on Linux with Azure SQL |
This template deploys Sonarqube in an Azure App Service web app Linux container using the official Sonarqube image and backed by an Azure SQL Server. |
Terraform (AzAPI provider) resource definition
The servers/databases resource type can be deployed with operations that target:
- Resource groups
For a list of changed properties in each API version, see change log.
Resource format
To create a Microsoft.Sql/servers/databases resource, add the following Terraform to your template.
resource "azapi_resource" "symbolicname" {
type = "Microsoft.Sql/servers/databases@2023-05-01-preview"
name = "string"
location = "string"
parent_id = "string"
tags = {
tagName1 = "tagValue1"
tagName2 = "tagValue2"
}
identity {
type = "UserAssigned"
identity_ids = []
}
body = jsonencode({
properties = {
autoPauseDelay = int
availabilityZone = "string"
catalogCollation = "string"
collation = "string"
createMode = "string"
elasticPoolId = "string"
encryptionProtector = "string"
encryptionProtectorAutoRotation = bool
federatedClientId = "string"
freeLimitExhaustionBehavior = "string"
highAvailabilityReplicaCount = int
isLedgerOn = bool
keys = {
{customized property} = {}
}
licenseType = "string"
longTermRetentionBackupResourceId = "string"
maintenanceConfigurationId = "string"
manualCutover = bool
maxSizeBytes = int
minCapacity = "decimal-as-string"
performCutover = bool
preferredEnclaveType = "string"
readScale = "string"
recoverableDatabaseId = "string"
recoveryServicesRecoveryPointId = "string"
requestedBackupStorageRedundancy = "string"
restorableDroppedDatabaseId = "string"
restorePointInTime = "string"
sampleName = "string"
secondaryType = "string"
sourceDatabaseDeletionDate = "string"
sourceDatabaseId = "string"
sourceResourceId = "string"
useFreeLimit = bool
zoneRedundant = bool
}
sku = {
capacity = int
family = "string"
name = "string"
size = "string"
tier = "string"
}
})
}
Property values
servers/databases
| Name | Description | Value |
|---|---|---|
| type | The resource type | "Microsoft.Sql/servers/databases@2023-05-01-preview" |
| name | The resource name | string (required) Character limit: 1-128 Valid characters: Can't use: <>*%&:\/? or control charactersCan't end with period or space. |
| location | Resource location. | string (required) |
| parent_id | The ID of the resource that is the parent for this resource. | ID for resource of type: servers |
| tags | Resource tags. | Dictionary of tag names and values. |
| sku | The database SKU. The list of SKUs may vary by region and support offer. To determine the SKUs (including the SKU name, tier/edition, family, and capacity) that are available to your subscription in an Azure region, use the Capabilities_ListByLocation REST API or one of the following commands:Azure CLI: az sql db list-editions -l {location} -o table Azure PowerShell: Get-AzSqlServerServiceObjective -Location {location} |
Sku |
| identity | The Azure Active Directory identity of the database. | DatabaseIdentity |
| properties | Resource properties. | DatabaseProperties |
DatabaseIdentity
| Name | Description | Value |
|---|---|---|
| type | The identity type | "UserAssigned" |
| identity_ids | The resource ids of the user assigned identities to use | Array of user identity IDs. |
DatabaseIdentityUserAssignedIdentities
| Name | Description | Value |
|---|---|---|
| {customized property} | DatabaseUserIdentity |
DatabaseUserIdentity
This object doesn't contain any properties to set during deployment. All properties are ReadOnly.
DatabaseProperties
| Name | Description | Value |
|---|---|---|
| autoPauseDelay | Time in minutes after which database is automatically paused. A value of -1 means that automatic pause is disabled | int |
| availabilityZone | Specifies the availability zone the database is pinned to. | "1" "2" "3" "NoPreference" |
| catalogCollation | Collation of the metadata catalog. | "DATABASE_DEFAULT" "SQL_Latin1_General_CP1_CI_AS" |
| collation | The collation of the database. | string |
| createMode | Specifies the mode of database creation. Default: regular database creation. Copy: creates a database as a copy of an existing database. sourceDatabaseId must be specified as the resource ID of the source database. Secondary: creates a database as a secondary replica of an existing database. sourceDatabaseId must be specified as the resource ID of the existing primary database. PointInTimeRestore: Creates a database by restoring a point in time backup of an existing database. sourceDatabaseId must be specified as the resource ID of the existing database, and restorePointInTime must be specified. Recovery: Creates a database by restoring a geo-replicated backup. sourceDatabaseId must be specified as the recoverable database resource ID to restore. Restore: Creates a database by restoring a backup of a deleted database. sourceDatabaseId must be specified. If sourceDatabaseId is the database's original resource ID, then sourceDatabaseDeletionDate must be specified. Otherwise sourceDatabaseId must be the restorable dropped database resource ID and sourceDatabaseDeletionDate is ignored. restorePointInTime may also be specified to restore from an earlier point in time. RestoreLongTermRetentionBackup: Creates a database by restoring from a long term retention vault. recoveryServicesRecoveryPointResourceId must be specified as the recovery point resource ID. Copy, Secondary, and RestoreLongTermRetentionBackup are not supported for DataWarehouse edition. |
"Copy" "Default" "OnlineSecondary" "PointInTimeRestore" "Recovery" "Restore" "RestoreExternalBackup" "RestoreExternalBackupSecondary" "RestoreLongTermRetentionBackup" "Secondary" |
| elasticPoolId | The resource identifier of the elastic pool containing this database. | string |
| encryptionProtector | The azure key vault URI of the database if it's configured with per Database Customer Managed Keys. | string |
| encryptionProtectorAutoRotation | The flag to enable or disable auto rotation of database encryption protector AKV key. | bool |
| federatedClientId | The Client id used for cross tenant per database CMK scenario | string Constraints: Min length = 36 Max length = 36 Pattern = ^[0-9a-fA-F]{8}-([0-9a-fA-F]{4}-){3}[0-9a-fA-F]{12}$ |
| freeLimitExhaustionBehavior | Specifies the behavior when monthly free limits are exhausted for the free database. AutoPause: The database will be auto paused upon exhaustion of free limits for remainder of the month. BillForUsage: The database will continue to be online upon exhaustion of free limits and any overage will be billed. |
"AutoPause" "BillOverUsage" |
| highAvailabilityReplicaCount | The number of secondary replicas associated with the database that are used to provide high availability. Not applicable to a Hyperscale database within an elastic pool. | int |
| isLedgerOn | Whether or not this database is a ledger database, which means all tables in the database are ledger tables. Note: the value of this property cannot be changed after the database has been created. | bool |
| keys | The resource ids of the user assigned identities to use | DatabasePropertiesKeys |
| licenseType | The license type to apply for this database. LicenseIncluded if you need a license, or BasePrice if you have a license and are eligible for the Azure Hybrid Benefit. |
"BasePrice" "LicenseIncluded" |
| longTermRetentionBackupResourceId | The resource identifier of the long term retention backup associated with create operation of this database. | string |
| maintenanceConfigurationId | Maintenance configuration id assigned to the database. This configuration defines the period when the maintenance updates will occur. | string |
| manualCutover | Whether or not customer controlled manual cutover needs to be done during Update Database operation to Hyperscale tier. This property is only applicable when scaling database from Business Critical/General Purpose/Premium/Standard tier to Hyperscale tier. When manualCutover is specified, the scaling operation will wait for user input to trigger cutover to Hyperscale database. To trigger cutover, please provide 'performCutover' parameter when the Scaling operation is in Waiting state. |
bool |
| maxSizeBytes | The max size of the database expressed in bytes. | int |
| minCapacity | Minimal capacity that database will always have allocated, if not paused Specify a decimal value as a string. | int or json decimal |
| performCutover | To trigger customer controlled manual cutover during the wait state while Scaling operation is in progress. This property parameter is only applicable for scaling operations that are initiated along with 'manualCutover' parameter. This property is only applicable when scaling database from Business Critical/General Purpose/Premium/Standard tier to Hyperscale tier is already in progress. When performCutover is specified, the scaling operation will trigger cutover and perform role-change to Hyperscale database. |
bool |
| preferredEnclaveType | Type of enclave requested on the database i.e. Default or VBS enclaves. | "Default" "VBS" |
| readScale | The state of read-only routing. If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica in the same region. Not applicable to a Hyperscale database within an elastic pool. | "Disabled" "Enabled" |
| recoverableDatabaseId | The resource identifier of the recoverable database associated with create operation of this database. | string |
| recoveryServicesRecoveryPointId | The resource identifier of the recovery point associated with create operation of this database. | string |
| requestedBackupStorageRedundancy | The storage account type to be used to store backups for this database. | "Geo" "GeoZone" "Local" "Zone" |
| restorableDroppedDatabaseId | The resource identifier of the restorable dropped database associated with create operation of this database. | string |
| restorePointInTime | Specifies the point in time (ISO8601 format) of the source database that will be restored to create the new database. | string |
| sampleName | The name of the sample schema to apply when creating this database. | "AdventureWorksLT" "WideWorldImportersFull" "WideWorldImportersStd" |
| secondaryType | The secondary type of the database if it is a secondary. Valid values are Geo, Named and Standby. | "Geo" "Named" "Standby" |
| sourceDatabaseDeletionDate | Specifies the time that the database was deleted. | string |
| sourceDatabaseId | The resource identifier of the source database associated with create operation of this database. | string |
| sourceResourceId | The resource identifier of the source associated with the create operation of this database. This property is only supported for DataWarehouse edition and allows to restore across subscriptions. When sourceResourceId is specified, sourceDatabaseId, recoverableDatabaseId, restorableDroppedDatabaseId and sourceDatabaseDeletionDate must not be specified and CreateMode must be PointInTimeRestore, Restore or Recover. When createMode is PointInTimeRestore, sourceResourceId must be the resource ID of the existing database or existing sql pool, and restorePointInTime must be specified. When createMode is Restore, sourceResourceId must be the resource ID of restorable dropped database or restorable dropped sql pool. When createMode is Recover, sourceResourceId must be the resource ID of recoverable database or recoverable sql pool. When source subscription belongs to a different tenant than target subscription, “x-ms-authorization-auxiliary” header must contain authentication token for the source tenant. For more details about “x-ms-authorization-auxiliary” header see /azure/azure-resource-manager/management/authenticate-multi-tenant |
string |
| useFreeLimit | Whether or not the database uses free monthly limits. Allowed on one database in a subscription. | bool |
| zoneRedundant | Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones. | bool |
DatabasePropertiesKeys
| Name | Description | Value |
|---|---|---|
| {customized property} | DatabaseKey |
DatabaseKey
This object doesn't contain any properties to set during deployment. All properties are ReadOnly.
Sku
| Name | Description | Value |
|---|---|---|
| capacity | Capacity of the particular SKU. | int |
| family | If the service has different generations of hardware, for the same SKU, then that can be captured here. | string |
| name | The name of the SKU, typically, a letter + Number code, e.g. P3. | string (required) |
| size | Size of the particular SKU | string |
| tier | The tier or edition of the particular SKU, e.g. Basic, Premium. | string |