ARM Template Deployments for MySQL and PostgreSQL

A few weeks ago, Microsoft announced a preview of MySQL and PostgreSQL databases as a PaaS service running in Azure. Since then, we’ve not yet seen full documentation of the ARM templates required to deploy these; however, some example templates did appear on GitHub a few days ago that provide enough information to begin creating templates that use these two database services.

Both databases look to follow the same design, with just a variation in type name. They also both follow a similar layout to Azure SQL, with a top-level server object then child objects for databases and firewall rules.

At present, to be able to deploy these resources using ARM templates, you do need to use the 2016-02-01-privatepreview API version. Whilst this does state private preview, it is published publically on GitHub and works without being on the private preview.

A full example of a web app and MySQL deployment can be found here and a PostgreSQL one can be found here.

Template

Below is the full JSON for deploying a database server, database, and firewall rule. We’ll break this down and look at the parameters you need to provide.

{
    "apiVersion": "2016-02-01-privatepreview",
    "kind": "",
    "location": "[resourceGroup().location]",
    "name": "[variables('serverName')]",
    "properties": {
        "version": "[parameters('postgresqlVersion')]",
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
        "storageMB": "[parameters('databaseSkuSizeMB')]"
    },
    "sku": {
        "name": "[parameters('databaseSkuName')]",
        "tier": "[parameters('databaseSkuTier')]",
        "capacity": "[parameters('databaseDTU')]",
        "size": "[parameters('databaseSkuSizeMB')]",
        "family": "SkuFamily"
    },
    "type": "Microsoft.DBforPostgreSQL/servers",
    "resources": [
        {
            "type": "firewallrules",
            "apiVersion": "2016-02-01-privatepreview",
            "dependsOn": [
                "[concat('Microsoft.DBforPostgreSQL/servers/', variables('serverName'))]"
            ],
            "location": "[resourceGroup().location]",
            "name": "[concat(variables('serverName'),'firewall')]",
            "properties": {
                "startIpAddress": "0.0.0.0",
                "endIpAddress": "255.255.255.255"
            }
        },
        {
            "name": "[variables('databaseName')]",
            "type": "databases",
            "apiVersion": "2016-02-01-privatepreview",
            "properties": {
                "charset": "utf8",
                "collation": "English_United States.1252"
            },
            "dependsOn": [
                "[concat('Microsoft.DBforPostgreSQL/servers/', variables('serverName'))]"
            ]
        }
    ]
}

• apiVersion: As mentioned, this needs to be “2016-02-01-privatepreview”.

• kind: It’s not clear what this parameter is for. By the looks of the schema, it accepts two values, storage or blobstorage, but in all the examples, this is left blank.

• type: For MySQL, this will be Microsoft.DBforMySQL/servers and for PostgreSQL, Microsoft.DBforPostgreSQL/servers.

• administrationLogin and Password: These are obviously the credentials you will use to access the database.

Properties

• version: For MySQL, versions 5.6 and 5.7 are supported. For PostgreSQL, this is 9.5 and 9.6.

• storageMB: At present, only two sizes are supported, so your value must be exactly 102400 or 51200 (100GB or 50GB).

Sku

• tier: At present, only one tier is available. Standard and Premium will be available later.

• capacity: This is the performance capacity in DTU; can be 50 or 100 DTU.

• size: This is the DB size in MB again; 102400 or 51200.

• name: This combines the tier, capacity, and size, can be MYSQLB100 or MYSQLB50 and PostgreSQLB50 or PostgreSQLB100.

• family: Unclear what this is for; set to SKUFamily.

Firewall Rules

The firewall rules child resource is the same as we have seen in Azure SQL, but using the preview API.

Database

The database child resource is a bit simpler than the Azure SQL one, with the performance and size being determined by the parent. The database has only two real configuration options, charset and collation , which can be set as you require.

Connection String

If you're creating one of these DBs, chances are you are using it with an Azure web app and you need to hook the two together with a connection string. If that’s the case, you can use the parameters provided in your template to build this connection string in the web app section of your template:

MySQL

"resources": [
    {
        "apiVersion": "2015-04-01",
        "name": "connectionstrings",
        "type": "config",
        "dependsOn": [
            "[concat('Microsoft.Web/sites/', parameters('siteName'))]"
        ],
        "properties": {
            "defaultConnection": {
                "value": "[concat('Database=', variables('databaseName'), ';Data Source=', variables('serverName'), '.mysql.database.azure.com;User Id=', parameters('dbadministratorLogin'),'@', variables('serverName'),';Password=', parameters('dbadministratorLoginPassword'))]",
                "type": "MySQL"
            }
        }
    }
]

PostgreSQL

"resources": [
    {
        "apiVersion": "2015-04-01",
        "name": "connectionstrings",
        "type": "config",
        "dependsOn": [
            "[concat('Microsoft.Web/sites/', parameters('siteName'))]"
        ],
        "properties": {
            "defaultConnection": {
                "value": "[concat('Database=', variables('databaseName'), ';Server=', variables('serverName'), '.postgres.database.azure.com;User Id=', parameters('administratorLogin'),'@', variables('serverName'),';Password=', parameters('administratorLoginPassword'))]",
                "type": "PostgreSQL"
            }
        }
    }
]

As mentioned, a full example of a web app and MYSQL deployment can be found here and a PostgreSQL one can be found here.