Skip to content

azurermMssqlDatabase

Manages a MS SQL Database.

Example Usage

/*Provider bindings are generated by running cdktf get.
See https://cdk.tf/provider-generation for more details.*/
import * as azurerm from "./.gen/providers/azurerm";
/*The following providers are missing schema information and might need manual adjustments to synthesize correctly: azurerm.
For a more precise conversion please use the --provider flag in convert.*/
new azurerm.provider.AzurermProvider(this, "azurerm", {
  features: [{}],
});
const azurermResourceGroupExample = new azurerm.resourceGroup.ResourceGroup(
  this,
  "example",
  {
    location: "West Europe",
    name: "example-resources",
  }
);
const azurermStorageAccountExample = new azurerm.storageAccount.StorageAccount(
  this,
  "example_2",
  {
    account_replication_type: "LRS",
    account_tier: "Standard",
    location: azurermResourceGroupExample.location,
    name: "examplesa",
    resource_group_name: azurermResourceGroupExample.name,
  }
);
/*This allows the Terraform resource name to match the original name. You can remove the call if you don't need them to match.*/
azurermStorageAccountExample.overrideLogicalId("example");
const azurermMssqlServerExample = new azurerm.mssqlServer.MssqlServer(
  this,
  "example_3",
  {
    administrator_login: "4dm1n157r470r",
    administrator_login_password: "4-v3ry-53cr37-p455w0rd",
    location: azurermResourceGroupExample.location,
    name: "example-sqlserver",
    resource_group_name: azurermResourceGroupExample.name,
    version: "12.0",
  }
);
/*This allows the Terraform resource name to match the original name. You can remove the call if you don't need them to match.*/
azurermMssqlServerExample.overrideLogicalId("example");
new azurerm.mssqlDatabase.MssqlDatabase(this, "test", {
  collation: "SQL_Latin1_General_CP1_CI_AS",
  license_type: "LicenseIncluded",
  max_size_gb: 4,
  name: "acctest-db-d",
  read_scale: true,
  server_id: azurermMssqlServerExample.id,
  sku_name: "S0",
  tags: {
    foo: "bar",
  },
  zone_redundant: true,
});

Argument Reference

The following arguments are supported:

  • name - (Required) The name of the MS SQL Database. Changing this forces a new resource to be created.

  • serverId - (Required) The id of the MS SQL Server on which to create the database. Changing this forces a new resource to be created.

\~> Note: This setting is still required for "Serverless" SKUs

  • autoPauseDelayInMinutes - (Optional) Time in minutes after which database is automatically paused. A value of 1 means that automatic pause is disabled. This property is only settable for General Purpose Serverless databases.

  • createMode - (Optional) The create mode of the database. Possible values are copy, default, onlineSecondary, pointInTimeRestore, recovery, restore, restoreExternalBackup, restoreExternalBackupSecondary, restoreLongTermRetentionBackup and secondary. Mutually exclusive with import. Changing this forces a new resource to be created.

  • import - (Optional) A Database Import block as documented below. Mutually exclusive with createMode.

  • creationSourceDatabaseId - (Optional) The ID of the source database from which to create the new database. This should only be used for databases with createMode values that use another database as reference. Changing this forces a new resource to be created.

-> Note: When configuring a secondary database, please be aware of the constraints for the skuName property, as noted below, for both the primary and secondary databases. The skuName of the secondary database may be inadvertently changed to match that of the primary when an incompatible combination of SKUs is detected by the provider.

  • collation - (Optional) Specifies the collation of the database. Changing this forces a new resource to be created.

  • elasticPoolId - (Optional) Specifies the ID of the elastic pool containing this database.

  • geoBackupEnabled - (Optional) A boolean that specifies if the Geo Backup Policy is enabled. Defaults to true.

\~> Note: geoBackupEnabled is only applicable for DataWarehouse SKUs (DW*). This setting is ignored for all other SKUs.

  • maintenanceConfigurationName - (Optional) The name of the Public Maintenance Configuration window to apply to the database. Valid values include sqlDefault, sqlEastUsDb1, sqlEastUs2Db1, sqlSoutheastAsiaDb1, sqlAustraliaEastDb1, sqlNorthEuropeDb1, sqlSouthCentralUsDb1, sqlWestUs2Db1, sqlUkSouthDb1, sqlWestEuropeDb1, sqlEastUsDb2, sqlEastUs2Db2, sqlWestUs2Db2, sqlSoutheastAsiaDb2, sqlAustraliaEastDb2, sqlNorthEuropeDb2, sqlSouthCentralUsDb2, sqlUkSouthDb2, sqlWestEuropeDb2, sqlAustraliaSoutheastDb1, sqlBrazilSouthDb1, sqlCanadaCentralDb1, sqlCanadaEastDb1, sqlCentralUsDb1, sqlEastAsiaDb1, sqlFranceCentralDb1, sqlGermanyWestCentralDb1, sqlCentralIndiaDb1, sqlSouthIndiaDb1, sqlJapanEastDb1, sqlJapanWestDb1, sqlNorthCentralUsDb1, sqlUkWestDb1, sqlWestUsDb1, sqlAustraliaSoutheastDb2, sqlBrazilSouthDb2, sqlCanadaCentralDb2, sqlCanadaEastDb2, sqlCentralUsDb2, sqlEastAsiaDb2, sqlFranceCentralDb2, sqlGermanyWestCentralDb2, sqlCentralIndiaDb2, sqlSouthIndiaDb2, sqlJapanEastDb2, sqlJapanWestDb2, sqlNorthCentralUsDb2, sqlUkWestDb2, sqlWestUsDb2, sqlWestCentralUsDb1, sqlFranceSouthDb1, sqlWestCentralUsDb2, sqlFranceSouthDb2, sqlSwitzerlandNorthDb1, sqlSwitzerlandNorthDb2, sqlBrazilSoutheastDb1, sqlUaeNorthDb1, sqlBrazilSoutheastDb2, sqlUaeNorthDb2. Defaults to sqlDefault.

\~> Note: maintenanceConfigurationName is only applicable if elasticPoolId is not set.

  • ledgerEnabled - (Optional) A boolean that specifies if this is a ledger database. Defaults to false. Changing this forces a new resource to be created.

  • licenseType - (Optional) Specifies the license type applied to this database. Possible values are licenseIncluded and basePrice.

  • longTermRetentionPolicy - (Optional) A longTermRetentionPolicy block as defined below.

  • maxSizeGb - (Optional) The max size of the database in gigabytes.

\~> Note: This value should not be configured when the createMode is secondary or onlineSecondary, as the sizing of the primary is then used as per Azure documentation.

  • minCapacity - (Optional) Minimal capacity that database will always have allocated, if not paused. This property is only settable for General Purpose Serverless databases.

  • restorePointInTime - (Optional) Specifies the point in time (ISO8601 format) of the source database that will be restored to create the new database. This property is only settable for createMode= pointInTimeRestore databases.

  • recoverDatabaseId - (Optional) The ID of the database to be recovered. This property is only applicable when the createMode is recovery.

  • restoreDroppedDatabaseId - (Optional) The ID of the database to be restored. This property is only applicable when the createMode is restore.

  • readReplicaCount - (Optional) The number of readonly secondary replicas associated with the database to which readonly application intent connections may be routed. This property is only settable for Hyperscale edition databases.

  • readScale - (Optional) If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica. This property is only settable for Premium and Business Critical databases.

  • sampleName - (Optional) Specifies the name of the sample schema to apply when creating this database. Possible value is adventureWorksLt.

  • shortTermRetentionPolicy - (Optional) A shortTermRetentionPolicy block as defined below.

  • skuName - (Optional) Specifies the name of the SKU used by the database. For example, gpSGen52,hsGen41,bcGen52, elasticPool, basic,s0, p2 ,dw100C, ds100. Changing this from the HyperScale service tier to another service tier will create a new resource.

\~> Note: The default skuName value may differ between Azure locations depending on local availability of Gen4/Gen5 capacity. When databases are replicated using the creationSourceDatabaseId property, the source (primary) database cannot have a higher SKU service tier than any secondary databases. When changing the skuName of a database having one or more secondary databases, this resource will first update any secondary databases as necessary. In such cases it's recommended to use the same skuName in your configuration for all related databases, as not doing so may cause an unresolvable diff during subsequent plans.

  • storageAccountType - (Optional) Specifies the storage account type used to store backups for this database. Possible values are geo, local and zone. The default value is geo.

  • threatDetectionPolicy - (Optional) Threat detection policy configuration. The threatDetectionPolicy block supports fields documented below.

  • transparentDataEncryptionEnabled - (Optional) If set to true, Transparent Data Encryption will be enabled on the database. Defaults to true.

-> NOTE: TDE cannot be disabled on servers with SKUs other than ones starting with DW.

  • zoneRedundant - (Optional) Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones. This property is only settable for Premium and Business Critical databases.

  • tags - (Optional) A mapping of tags to assign to the resource.

a import block supports the following:

  • storageUri - (Required) Specifies the blob URI of the .bacpac file.
  • storageKey - (Required) Specifies the access key for the storage account.
  • storageKeyType - (Required) Specifies the type of access key for the storage account. Valid values are storageAccessKey or sharedAccessKey.
  • administratorLogin - (Required) Specifies the name of the SQL administrator.
  • administratorLoginPassword - (Required) Specifies the password of the SQL administrator.
  • authenticationType - (Required) Specifies the type of authentication used to access the server. Valid values are sql or adPassword.
  • storageAccountId - (Optional) The resource id for the storage account used to store BACPAC file. If set, private endpoint connection will be created for the storage account. Must match storage account used for storage_uri parameter.

a threatDetectionPolicy block supports the following:

  • state - (Optional) The State of the Policy. Possible values are enabled, disabled or new.
  • disabledAlerts - (Optional) Specifies a list of alerts which should be disabled. Possible values include accessAnomaly, sqlInjection and sqlInjectionVulnerability.
  • emailAccountAdmins - (Optional) Should the account administrators be emailed when this alert is triggered? Possible values are disabled and enabled.
  • emailAddresses - (Optional) A list of email addresses which alerts should be sent to.
  • retentionDays - (Optional) Specifies the number of days to keep in the Threat Detection audit logs.
  • storageAccountAccessKey - (Optional) Specifies the identifier key of the Threat Detection audit storage account. Required if state is enabled.
  • storageEndpoint - (Optional) Specifies the blob storage endpoint (e.g. https://example.blob.core.windows.net). This blob storage will hold all Threat Detection audit logs. Required if state is enabled.

A longTermRetentionPolicy block supports the following:

  • weeklyRetention - (Optional) The weekly retention policy for an LTR backup in an ISO 8601 format. Valid value is between 1 to 520 weeks. e.g. p1Y, p1M, p1W or p7D.
  • monthlyRetention - (Optional) The monthly retention policy for an LTR backup in an ISO 8601 format. Valid value is between 1 to 120 months. e.g. p1Y, p1M, p4W or p30D.
  • yearlyRetention - (Optional) The yearly retention policy for an LTR backup in an ISO 8601 format. Valid value is between 1 to 10 years. e.g. p1Y, p12M, p52W or p365D.
  • weekOfYear - (Optional) The week of year to take the yearly backup. Value has to be between 1 and 52.

A shortTermRetentionPolicy block supports the following:

  • retentionDays - (Required) Point In Time Restore configuration. Value has to be between 7 and 35.
  • backupIntervalInHours - (Optional) The hours between each differential backup. This is only applicable to live databases but not dropped databases. Value has to be 12 or 24. Defaults to 12 hours.

Attributes Reference

The following attributes are exported:

  • id - The ID of the MS SQL Database.

Timeouts

The timeouts block allows you to specify timeouts for certain actions:

  • create - (Defaults to 60 minutes) Used when creating the MS SQL Database.
  • update - (Defaults to 60 minutes) Used when updating the MS SQL Database.
  • read - (Defaults to 5 minutes) Used when retrieving the MS SQL Database.
  • delete - (Defaults to 60 minutes) Used when deleting the MS SQL Database.

Import

SQL Database can be imported using the resourceId, e.g.

terraform import azurerm_mssql_database.example /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/group1/providers/Microsoft.Sql/servers/server1/databases/example1