Jaa

Microsoft.Sql servers/databases 2021-02-01-preview

  • Artikkeli

Bicep resource definition

The servers/databases resource type can be deployed with operations that target:

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@2021-02-01-preview' = {
  parent: resourceSymbolicName
  location: 'string'
  name: 'string'
  properties: {
    autoPauseDelay: int
    catalogCollation: 'string'
    collation: 'string'
    createMode: 'string'
    elasticPoolId: 'string'
    highAvailabilityReplicaCount: int
    isLedgerOn: bool
    licenseType: 'string'
    longTermRetentionBackupResourceId: 'string'
    maintenanceConfigurationId: 'string'
    maxSizeBytes: int
    minCapacity: int
    readScale: 'string'
    recoverableDatabaseId: 'string'
    recoveryServicesRecoveryPointId: 'string'
    requestedBackupStorageRedundancy: 'string'
    restorableDroppedDatabaseId: 'string'
    restorePointInTime: 'string'
    sampleName: 'string'
    secondaryType: 'string'
    sourceDatabaseDeletionDate: 'string'
    sourceDatabaseId: 'string'
    zoneRedundant: bool
  }
  sku: {
    capacity: int
    family: 'string'
    name: 'string'
    size: 'string'
    tier: 'string'
  }
  tags: {
    {customized property}: 'string'
  }
}

Property values

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
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
highAvailabilityReplicaCount The number of secondary replicas associated with the database that are used to provide high availability. 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
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
maxSizeBytes The max size of the database expressed in bytes. int
minCapacity Minimal capacity that database will always have allocated, if not paused int
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. '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'
'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 and Named. 'Geo'
'Named'
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
zoneRedundant Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones. bool

Microsoft.Sql/servers/databases

Name Description Value
location Resource location. string (required)
name The resource name string (required)
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
properties Resource properties. DatabaseProperties
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:

azurecli<br />az sql db list-editions -l &lt;location&gt; -o table<br />````<br /><br />powershell
Get-AzSqlServerServiceObjective -Location <location>
````		 Sku
tags Resource tags Dictionary of tag names and values. See Tags in templates

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

TrackedResourceTags

Name Description Value

Quickstart samples

The following quickstart samples deploy this resource type.

Bicep File Description
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)
Create a SQL Server and Database This template allows you to create SQL Database and Server.
Dedicated SQL pool with Transparent Encryption Creates a SQL Server and a Dedicated SQL pool (formerly SQL DW) with Transparent Data Encryption.
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.
Private Endpoint example This template shows how to create a private endpoint pointing to Azure SQL Server
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.
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.
Web App with Managed Identity, SQL Server and ΑΙ Simple example to deploy Azure infrastructure for app + data + managed identity + monitoring

ARM template resource definition

The servers/databases resource type can be deployed with operations that target:

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": "2021-02-01-preview",
  "name": "string",
  "location": "string",
  "properties": {
    "autoPauseDelay": "int",
    "catalogCollation": "string",
    "collation": "string",
    "createMode": "string",
    "elasticPoolId": "string",
    "highAvailabilityReplicaCount": "int",
    "isLedgerOn": "bool",
    "licenseType": "string",
    "longTermRetentionBackupResourceId": "string",
    "maintenanceConfigurationId": "string",
    "maxSizeBytes": "int",
    "minCapacity": "int",
    "readScale": "string",
    "recoverableDatabaseId": "string",
    "recoveryServicesRecoveryPointId": "string",
    "requestedBackupStorageRedundancy": "string",
    "restorableDroppedDatabaseId": "string",
    "restorePointInTime": "string",
    "sampleName": "string",
    "secondaryType": "string",
    "sourceDatabaseDeletionDate": "string",
    "sourceDatabaseId": "string",
    "zoneRedundant": "bool"
  },
  "sku": {
    "capacity": "int",
    "family": "string",
    "name": "string",
    "size": "string",
    "tier": "string"
  },
  "tags": {
    "{customized property}": "string"
  }
}

Property values

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
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
highAvailabilityReplicaCount The number of secondary replicas associated with the database that are used to provide high availability. 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
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
maxSizeBytes The max size of the database expressed in bytes. int
minCapacity Minimal capacity that database will always have allocated, if not paused int
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. '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'
'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 and Named. 'Geo'
'Named'
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
zoneRedundant Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones. bool

Microsoft.Sql/servers/databases

Name Description Value
apiVersion The api version '2021-02-01-preview'
location Resource location. string (required)
name The resource name string (required)
properties Resource properties. DatabaseProperties
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:

azurecli<br />az sql db list-editions -l &lt;location&gt; -o table<br />````<br /><br />powershell
Get-AzSqlServerServiceObjective -Location <location>
````		 Sku
tags Resource tags Dictionary of tag names and values. See Tags in templates
type The resource type 'Microsoft.Sql/servers/databases'

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

TrackedResourceTags

Name Description Value

Quickstart templates

The following quickstart templates deploy this resource type.

Template Description
Azure SQL Server with Auditing written to Log Analytics

Deploy to Azure		 This template allows you to deploy an Azure SQL server with Auditing enabled to write audit logs to Log Analytics (OMS workspace)
Create a SQL Server and Database

Deploy to Azure		 This template allows you to create SQL Database and Server.
Dedicated SQL pool with Transparent Encryption

Deploy to Azure		 Creates a SQL Server and a Dedicated SQL pool (formerly SQL DW) with Transparent Data Encryption.
Deploy a new SQL Elastic Pool

Deploy to Azure		 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.
Deploy the Sports Analytics on Azure Architecture

Deploy to Azure		 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.
HDInsight with custom Ambari + Hive Metastore DB in VNET

Deploy to Azure		 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.
Private Endpoint example

Deploy to Azure		 This template shows how to create a private endpoint pointing to Azure SQL Server
Provision a Web App with a SQL Database

Deploy to Azure		 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.
Remote Desktop Services with High Availability

Deploy to Azure		 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.
Sonarqube Docker Web App on Linux with Azure SQL

Deploy to Azure		 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.
Web App with Managed Identity, SQL Server and ΑΙ

Deploy to Azure		 Simple example to deploy Azure infrastructure for app + data + managed identity + monitoring
WebApp consuming a Azure SQL Private Endpoint

Deploy to Azure		 This template shows how to create a Web app that consumes a private endpoint pointing to 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@2021-02-01-preview"
  name = "string"
  location = "string"
  sku = {
    capacity = int
    family = "string"
    name = "string"
    size = "string"
    tier = "string"
  }
  tags = {
    {customized property} = "string"
  }
  body = jsonencode({
    properties = {
      autoPauseDelay = int
      catalogCollation = "string"
      collation = "string"
      createMode = "string"
      elasticPoolId = "string"
      highAvailabilityReplicaCount = int
      isLedgerOn = bool
      licenseType = "string"
      longTermRetentionBackupResourceId = "string"
      maintenanceConfigurationId = "string"
      maxSizeBytes = int
      minCapacity = int
      readScale = "string"
      recoverableDatabaseId = "string"
      recoveryServicesRecoveryPointId = "string"
      requestedBackupStorageRedundancy = "string"
      restorableDroppedDatabaseId = "string"
      restorePointInTime = "string"
      sampleName = "string"
      secondaryType = "string"
      sourceDatabaseDeletionDate = "string"
      sourceDatabaseId = "string"
      zoneRedundant = bool
    }
  })
}

Property values

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
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
highAvailabilityReplicaCount The number of secondary replicas associated with the database that are used to provide high availability. 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
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
maxSizeBytes The max size of the database expressed in bytes. int
minCapacity Minimal capacity that database will always have allocated, if not paused int
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. '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'
'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 and Named. 'Geo'
'Named'
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
zoneRedundant Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones. bool

Microsoft.Sql/servers/databases

Name Description Value
location Resource location. string (required)
name The resource name string (required)
parent_id The ID of the resource that is the parent for this resource. ID for resource of type: servers
properties Resource properties. DatabaseProperties
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:

azurecli<br />az sql db list-editions -l &lt;location&gt; -o table<br />````<br /><br />powershell
Get-AzSqlServerServiceObjective -Location <location>
````		 Sku
tags Resource tags Dictionary of tag names and values.
type The resource type "Microsoft.Sql/servers/databases@2021-02-01-preview"

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

TrackedResourceTags

Name Description Value