Azure Synapse SQL Pool

This page shows how to write Terraform and Azure Resource Manager for Synapse SQL Pool and write them securely.

azurerm_synapse_sql_pool (Terraform)

The SQL Pool in Synapse can be configured in Terraform with the resource name azurerm_synapse_sql_pool. The following sections describe 10 examples of how to use the resource and its parameters.

Example Usage from GitHub

synapse_sql_pool_test.tf#L46
resource "azurerm_synapse_sql_pool" "default" {
  name                 = "examplesqlpool"
  synapse_workspace_id = azurerm_synapse_workspace.example.id
  sku_name             = "DW200c"
  create_mode          = "Default"
}
synapse_sql_pool.tf#L1
resource "azurerm_synapse_sql_pool" "synapseSqlPool001" {
  name                 = "SqlPool001"
  synapse_workspace_id = azurerm_synapse_workspace.synapseProduct001.id
  sku_name             = "DW300c"
  create_mode          = "Default"
  data_encrypted       = true
main.tf#L1
resource "azurerm_synapse_sql_pool" "sqlpool" {
  name                 = var.name
  synapse_workspace_id = var.synapse_workspace_id
  sku_name             = var.sku_name
  create_mode          = var.create_mode
  data_encrypted       = var.data_encrypted
sql_pool.tf#L11
resource "azurerm_synapse_sql_pool" "sql_pool" {
  name                 = azurecaf_name.sqlpool.result
  synapse_workspace_id = var.synapse_workspace_id
  sku_name             = try(var.settings.sku_name, "DW100c")
  create_mode          = try(var.settings.create_mode, "Default")
  tags                 = local.tags
database_pools.tf#L8
resource "azurerm_synapse_sql_pool" "this" {
  for_each              = local.sql
  name                  = each.value.name
  synapse_workspace_id = azurerm_synapse_workspace.ws.id
  sku_name             = each.value.sku_name
  create_mode          = each.value.create_mode
sql_pool.tf#L11
resource "azurerm_synapse_sql_pool" "sql_pool" {
  name                 = azurecaf_name.sqlpool.result
  synapse_workspace_id = var.synapse_workspace_id
  sku_name             = try(var.settings.sku_name, "DW100c")
  create_mode          = try(var.settings.create_mode, "Default")
  tags                 = local.tags
sql_pool.tf#L11
resource "azurerm_synapse_sql_pool" "sql_pool" {
  name                 = azurecaf_name.sqlpool.result
  synapse_workspace_id = var.synapse_workspace_id
  sku_name             = try(var.settings.sku_name, "DW100c")
  create_mode          = try(var.settings.create_mode, "Default")
  tags                 = local.tags
sql_pool.tf#L11
resource "azurerm_synapse_sql_pool" "sql_pool" {
  name                 = azurecaf_name.sqlpool.result
  synapse_workspace_id = var.synapse_workspace_id
  sku_name             = try(var.settings.sku_name, "DW100c")
  create_mode          = try(var.settings.create_mode, "Default")
  tags                 = local.tags
SQLpool.tf#L2
resource "azurerm_synapse_sql_pool" "sql_pool" {
    depends_on = [
      azurerm_synapse_workspace.synapse_workspace
    ]

  name                 = var.sql_pool_name
sql_pool.tf#L11
resource "azurerm_synapse_sql_pool" "sql_pool" {
  name                 = azurecaf_name.sqlpool.result
  synapse_workspace_id = var.synapse_workspace_id
  sku_name             = try(var.settings.sku_name, "DW100c")
  create_mode          = try(var.settings.create_mode, "Default")
  tags                 = local.tags

Review your Terraform file for Azure best practices

Shisho Cloud, our free checker to make sure your Terraform configuration follows best practices, is available (beta).

Parameters

Explanation in Terraform Registry

Manages a Synapse Sql Pool.

Tips: Best Practices for The Other Azure Synapse Resources

In addition to the azurerm_synapse_workspace, Azure Synapse has the other resources that should be configured for security reasons. Please check some examples of those resources and precautions.

risk-label

azurerm_synapse_workspace

Ensure to enable the managed virtual network

It is better to enable the managed virtual network, which is disabled as the default.

Review your Azure Synapse settings

In addition to the above, there are other security points you should be aware of making sure that your .tf files are protected in Shisho Cloud.

Microsoft.Synapse/workspaces/sqlPools (Azure Resource Manager)

The workspaces/sqlPools in Microsoft.Synapse can be configured in Azure Resource Manager with the resource name Microsoft.Synapse/workspaces/sqlPools. The following sections describe how to use the resource and its parameters.

Example Usage from GitHub

Security_-_SQL_DB_-_Synapse_SQL_pools_-_DeploySqlDBTransparentDataEncryption.json#L22
            "equals": "Microsoft.Synapse/workspaces/sqlPools"
          },
          {
            "field": "name",
            "notEquals": "master"
          }
ListSqlPoolsInWorkspace.json#L15
            "type": "Microsoft.Synapse/workspaces/sqlPools",
            "location": "Japan East",
            "properties": {
              "status": "Online",
              "collation": "SQL_Latin1_General_CP1_CI_AS",
              "maxSizeBytes": 32212254720,
params.policyDefinition.Deploy-Synapse-Sql-TransparentDataEncryption.json#L36
                            "equals": "Microsoft.Synapse/workspaces/sqlPools"
                        }
                    ]
                },
                "then": {
                    "effect": "[parameters('effect')]",
params.policyDefinition.Deploy-Synapse-Sql-MetadataSynch.json#L36
                            "equals": "Microsoft.Synapse/workspaces/sqlPools"
                        }
                    ]
                },
                "then": {
                    "effect": "[parameters('effect')]",
template_synapsesqlpool.json#L13
            "type": "Microsoft.Synapse/workspaces/sqlPools",
            "apiVersion": "2019-06-01-preview",
            "name": "[concat(parameters('workspaces_sujoysynapse1982_name'), '/sentiasqlpool')]",
            "location": "westeurope",
            "sku": {
                "name": "DW100c",
params.policyDefinition.Deploy-Synapse-Sql-TransparentDataEncryption.json#L36
                            "equals": "Microsoft.Synapse/workspaces/sqlPools"
                        }
                    ]
                },
                "then": {
                    "effect": "[parameters('effect')]",
params.policyDefinition.Deploy-Synapse-Sql-MetadataSynch.json#L36
                            "equals": "Microsoft.Synapse/workspaces/sqlPools"
                        }
                    ]
                },
                "then": {
                    "effect": "[parameters('effect')]",
params.policyDefinition.Deploy-Synapse-Sql-AuditingSettings.json#L36
                            "equals": "Microsoft.Synapse/workspaces/sqlPools"
                        }
                    ]
                },
                "then": {
                    "effect": "[parameters('effect')]",
params.policyDefinition.Deploy-Synapse-Sql-TransparentDataEncryption.json#L36
                            "equals": "Microsoft.Synapse/workspaces/sqlPools"
                        }
                    ]
                },
                "then": {
                    "effect": "[parameters('effect')]",
params.policyDefinition.Deploy-Synapse-Sql-MetadataSynch.json#L36
                            "equals": "Microsoft.Synapse/workspaces/sqlPools"
                        }
                    ]
                },
                "then": {
                    "effect": "[parameters('effect')]",

Parameters

  • apiVersion required - string
  • location required - string

    The geo-location where the resource lives

  • name required - string

    SQL pool name

  • properties required
      • collation optional - string

        Collation mode

      • createMode optional - string

        Specifies the mode of sql pool creation. Default: regular sql pool creation. PointInTimeRestore: Creates a sql pool by restoring a point in time backup of an existing sql pool. sourceDatabaseId must be specified as the resource ID of the existing sql pool, and restorePointInTime must be specified. Recovery: Creates a sql pool by a geo-replicated backup. sourceDatabaseId must be specified as the recoverableDatabaseId to restore. Restore: Creates a sql pool by restoring a backup of a deleted sql pool. SourceDatabaseId should be the sql pool's original resource ID. SourceDatabaseId and sourceDatabaseDeletionDate must be specified.

      • creationDate optional - string

        Date the SQL pool was created

      • maxSizeBytes optional - integer

        Maximum size in bytes

      • provisioningState optional - string

        Resource state

      • recoverableDatabaseId optional - string

        Backup database to restore from

      • restorePointInTime optional - string

        Snapshot time to restore

      • sourceDatabaseDeletionDate optional - string

        Specifies the time that the sql pool was deleted

      • sourceDatabaseId optional - string

        Source database to create from

      • status optional - string

        Resource status

      • storageAccountType optional - string

        The storage account type used to store backups for this sql pool.

  • sku optional
      • capacity optional - integer

        If the SKU supports scale out/in then the capacity integer should be included. If scale out/in is not possible for the resource this may be omitted.

      • name optional - string

        The SKU name

      • tier optional - string

        The service tier

  • tags optional - string

    Resource tags.

  • type required - string

Frequently asked questions

What is Azure Synapse SQL Pool?

Azure Synapse SQL Pool is a resource for Synapse of Microsoft Azure. Settings can be wrote in Terraform.

Where can I find the example code for the Azure Synapse SQL Pool?

For Terraform, the infracost/infracost, tschwarz01/tf-caf-data-landing-zone and AnikG-Org/devops-practice source code examples are useful. See the Terraform Example section for further details.

For Azure Resource Manager, the azsk/AzTS-docs, debhol/azuredocs and markransome/AzureDataManagementZone source code examples are useful. See the Azure Resource Manager Example section for further details.