Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

ARM Template Deployments for MySQL and PostgreSQL

DZone's Guide to

ARM Template Deployments for MySQL and PostgreSQL

Microsoft recently announced a preview of MySQL and PostgreSQL databases as a PaaS service running in Azure. These templates can help you begin creating templates.

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

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.

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
database ,mysql ,postgresql ,paas ,arm templates

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}