Using Power Automate or Logic Apps to Monitor Azure SQL DW

This article shows how you can monitor your Azure SQL DW from inside a logic app or Power Automate with REST API functionality. That way, you can automate tasks, processes, or workflows that manage your SQL DW and resources by creating logic apps or Power Automate.

You can create logic apps that run when triggered by events in your SQL database or in other systems, such as Dynamics CRM Online. Your logic apps can also get, insert, and delete data along with running SQL queries and stored procedures. For example, you can build a logic app that automatically checks for new records in Dynamics CRM Online, adds items to your SQL database for any new records, and then sends email alerts about the added items.

Here is the Power Automate workflow but this is identical in Logic Apps. I would recommend Logic apps if you are a heavy user of Azure Data Factory.

In this example the Power Automate runs every night to check if the SQL DW is running. If it is running it pauses teh SQL DW. This way you are saving on resourse cost during downtime.

Here is the GET request to get get current status

https://management.azure.com/subscriptions/{your subscription id}/resourceGroups/{your resource group}/providers/Microsoft.Sql/servers/{your SQL DW Server}/databases/{your DW name}?api-version=2017-10-01-preview

Here is the post request to pause

https://management.azure.com/subscriptions/{your subscription id}/resourceGroups/{your resource group}/providers/Microsoft.Sql/servers/{your SQL DW Server}/databases/{your DW name}/pause?api-version=2017-10-01-preview

JSON code

{
    "type": "object",
    "properties": {
        "sku": {
            "type": "object",
            "properties": {
                "name": {
                    "type": "string"
                },
                "tier": {
                    "type": "string"
                },
                "capacity": {
                    "type": "integer"
                }
            }
        },
        "kind": {
            "type": "string"
        },
        "properties": {
            "type": "object",
            "properties": {
                "collation": {
                    "type": "string"
                },
                "maxSizeBytes": {
                    "type": "integer"
                },
                "status": {
                    "type": "string"
                },
                "databaseId": {
                    "type": "string"
                },
                "creationDate": {
                    "type": "string"
                },
                "currentServiceObjectiveName": {
                    "type": "string"
                },
                "requestedServiceObjectiveName": {
                    "type": "string"
                },
                "defaultSecondaryLocation": {
                    "type": "string"
                },
                "catalogCollation": {
                    "type": "string"
                },
                "readScale": {
                    "type": "string"
                },
                "readReplicaCount": {
                    "type": "integer"
                },
                "currentSku": {
                    "type": "object",
                    "properties": {
                        "name": {
                            "type": "string"
                        },
                        "tier": {
                            "type": "string"
                        },
                        "capacity": {
                            "type": "integer"
                        }
                    }
                }
            }
        },
        "location": {
            "type": "string"
        },
        "id": {
            "type": "string"
        },
        "name": {
            "type": "string"
        },
        "type": {
            "type": "string"
        }
    }
}