Azure Resource Manager (ARM) template to deploy SQL along with tables

Question

I have an ARM template to deploy a sql.

{
 "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
......
"resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-05-01-preview",
      "name": "[parameters('serverName')]",
      "location": "[parameters('location')]",
      "properties": {
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-05-01-preview",
      "name": "[format('{0}/{1}', parameters('serverName'), parameters('sqlDBName'))]",
      "location": "[parameters('location')]",
      "sku": {
        "name": "Standard",
        "tier": "Standard"
      },
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
      ]
    }
  ]
}

When deployed it creates an empty sql database. How do we deploy the corresponding schema, tables, procedures?

Answer 1

We tested the below ARM template in our local environment it is working fine, Below statements are based on our analysis.

Here is the ARM template we have used to create a new SQL database, a table that is exported from the existing database.

Here is our template.json file:

{

  "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters":
  {
    "administratorLogin":
    {
      "type": "string"
    },
    "administratorLoginPassword":
    {
      "type": "securestring"
    },
    "databaseName":
    {
      "type": "string"
    },
    "location":
    {
      "type": "string"
    },
    "_artifactsLocationSasToken":
    {
      "type": "securestring"
    }
  },
  "variables":
  {
    "collation": "SQL_Latin1_General_CP1_CI_AS",
    "edition": "Premium",
    "maxSizeBytes": "1073741824",
    "requestedServiceObjectiveName": "P1",
    "storageKeyType": "SharedAccessKey",
    "version": "12.0",
    "serverName": "[concat('sqldemo', uniqueString(resourceGroup().id))]"
  },
  "resources":
  [
    {
      "name": "[variables('serverName')]",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2015-05-01-preview",
      "location": "[parameters('location')]",
      "properties":
      {
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
        "version": "[variables('version')]"
      },
      "resources":
      [
        {
          "name": "AllowAllWindowsAzureIps",
          "type": "firewallrules",
          "apiVersion": "2015-05-01-preview",
          "location": "[parameters('location')]",
          "dependsOn":
          [
            "[concat('Microsoft.Sql/servers/', variables('serverName'))]"
          ],
          "properties":
          {
            "endIpAddress": "0.0.0.0",
            "startIpAddress": "0.0.0.0"
          }
        },
        {
          "name": "[parameters('databaseName')]",
          "type": "databases",
          "apiVersion": "2017-03-01-preview",
          "location": "[parameters('location')]",
          "dependsOn":
          [
            "[concat('Microsoft.Sql/servers/', variables('serverName'))]"
          ],
          "properties":
          {
            "collation": "[variables('collation')]",
            "edition": "[variables('edition')]",
            "maxSizeBytes": "[variables('maxSizeBytes')]",
            "requestedServiceObjectiveName": "[variables('requestedServiceObjectiveName')]"
          },
          "resources":
          [
            {
              "name": "Import",
              "type": "extensions",
              "apiVersion": "2014-04-01-preview",
              "dependsOn":
              [
                "[concat('Microsoft.Sql/servers/', variables('serverName'), '/databases/', parameters('databaseName'))]"
              ],
              "properties":
              {
                "storageKeyType": "[variables('storageKeyType')]",
                "storageKey": "[parameters('_artifactsLocationSasToken')]",
                "storageUri": "<Storageuri_of_exported_bacpacfile>", ##pass the bloburi of the bacpac file 
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "operationMode": "Import"
              }
            }
          ]
        }
      ]
    }
  ],
  "outputs":
  {
    "serverName":
    {
      "type": "object",
      "value": "[reference(variables('serverName'))]"
    }
  }
}

Here is the template.parameters.json file :

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.0",
  "parameters":
  {
    "administratorLogin":
    {
      "value": "<sqlserverusername>"
    },
    "databaseName":
    {
      "value": "<dbtablename>"
    },
    "location": 
    {
      "value": "<location>"
    },
    "_artifactsLocationSasToken":{
        "value": "<SAS token of the exported bacpacfile>"
    }
      
  }
}

Here is the sample output for reference:

Note: You need to create a firewall rule with your Public IP in the SQL database post-creating sql DB with a table using the above ARM template.