ARM Template Deployments for MYSQL and PostgreSQl

ARM, Azure, Database

Last week at build 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 here

Template

Below is the full JSON for deploy 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 paramter 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 Passoword – These are obviously the credentials you will use to access the databse

Properties

version – for MYSQL version 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 1 tier is availible, basic. Standard and Premium will be availible 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 perormance and size being determined at the parent. The databsae has only 2 real configuration options, the charset and collation, which can be set as you require.

Connection String

If your creating one of these DBs, chances are you are using it with an Azure web app and you need to hook the two togther with a connection string. If that’s teh 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 here