Automate database workflows by using Azure Logic Apps

Data Integration

You may have data spread across multiple systems and need to integrate it into your Azure SQL Database. Using Logic Apps, you can create a workflow that retrieves data from different sources, such as FTP servers, web services, or cloud storage, and seamlessly integrates it into your database.

Here is an example of how you can configure Logic Apps to integrate data into Azure SQL Database:

{
"$connections": {
"value": {
"azure_sql": {
"connectionId": "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Web/connections/azure-sql",
"connectionName": "azure-sql",
"id": "/subscriptions/{subscriptionId}/providers/Microsoft.Web/locations/{location}/managedApis/azure-sql"
},
"azure_blob_storage": {
"connectionId": "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Web/connections/azure-blob-storage",
"connectionName": "azure-blob-storage",
"id": "/subscriptions/{subscriptionId}/providers/Microsoft.Web/locations/{location}/managedApis/azure-blob-storage"
}
}
},
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Compose": {
"inputs": "This is the data to be integrated",
"runAfter": {},
"type": "Compose"
},
"Insert_row": {
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azure_sql']['connectionId']"
}
},
"method": "post",
"path": "/databases/{database_id}/rows?api-version=2019-06-01-preview",
"body": {
"tableId": "{table_id}",
"rows": [
{
"column1": "@{body('Compose')}"
}
]
}
},
"runAfter": {
"Compose": [
"Succeeded"
]
},
"type": "ApiConnection"
}
},
"parameters": {
"$connections": {
"defaultValue": {},
"type": "Object"
}
},
"triggers": {
"manual": {
"inputs": {
"headers": {}
},
"kind": "Http",
"type": "Request"
}
}
}
}

In this example, we create a Logic App workflow that uses an HTTP trigger to start the workflow manually. The workflow retrieves data from an external source using the “Compose” action and then inserts the data into an Azure SQL Database table using the “Insert_row” action. You can customize the workflow based on your specific data integration requirements.

Data Synchronization

Keeping data synchronized between different databases or systems can be a complex task. With Azure Logic Apps, you can automate the process of data synchronization between Azure SQL databases or between Azure SQL databases and other data sources.

Here is an example of how you can configure Logic Apps to synchronize data between Azure SQL databases:

{
"$connections": {
"value": {
"azure_sql_source": {
"connectionId": "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Web/connections/azure-sql-source",
"connectionName": "azure-sql-source",
"id": "/subscriptions/{subscriptionId}/providers/Microsoft.Web/locations/{location}/managedApis/azure-sql"
},
"azure_sql_destination": {
"connectionId": "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Web/connections/azure-sql-destination",
"connectionName": "azure-sql-destination",
"id": "/subscriptions/{subscriptionId}/providers/Microsoft.Web/locations/{location}/managedApis/azure-sql"
}
}
},
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Get_rows": {
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azure_sql_source']['connectionId']"
}
},
"method": "get",
"path": "/databases/{database_id}/tables/{table_id}/rows?api-version=2019-06-01-preview"
},
"runAfter": {},
"type": "ApiConnection"
},
"Insert_rows": {
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azure_sql_destination']['connectionId']"
}
},
"method": "post",
"path": "/databases/{database_id}/tables/{table_id}/rows?api-version=2019-06-01-preview",
"body": "@{body('Get_rows')}"
},
"runAfter": {
"Get_rows": [
"Succeeded"
]
},
"type": "ApiConnection"
}
},
"parameters": {
"$connections": {
"defaultValue": {},
"type": "Object"
}
},
"triggers": {
"recurrence": {
"recurrence": {
"frequency": "Hour",
"interval": 1
},
"type": "Recurrence"
}
}
}
}

In this example, we create a Logic App workflow that uses a recurrence trigger to synchronize data between two Azure SQL databases. The workflow retrieves rows from a source database using the “Get_rows” action and then inserts the rows into a destination database using the “Insert_rows” action. The workflow is configured to run every hour, but you can adjust the recurrence based on your synchronization requirements.

Data Manipulation

Sometimes, you may need to manipulate data before storing it in your database. Azure Logic Apps allows you to easily manipulate data using connectors and actions. For example, you can extract data from a source system, transform it using functions or expressions, and then store it in your Azure SQL Database.

Here is an example of how you can configure Logic Apps to manipulate data before storing it in an Azure SQL Database:

{
"$connections": {
"value": {
"azure_sql": {
"connectionId": "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Web/connections/azure-sql",
"connectionName": "azure-sql",
"id": "/subscriptions/{subscriptionId}/providers/Microsoft.Web/locations/{location}/managedApis/azure-sql"
},
"http": {
"connectionId": "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Web/connections/http",
"connectionName": "http",
"id": "/subscriptions/{subscriptionId}/providers/Microsoft.Web/locations/{location}/managedApis/http"
}
}
},
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Get_data": {
"inputs": {
"method": "get",
"uri": "https://api.example.com/data"
},
"runAfter": {},
"type": "Http"
},
"Transform_data": {
"inputs": "@{json(convertFromBase64ToString(body('Get_data')))}",
"runAfter": {
"Get_data": [
"Succeeded"
]
},
"type": "Compose"
},
"Insert_row": {
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azure_sql']['connectionId']"
}
},
"method": "post",
"path": "/databases/{database_id}/rows?api-version=2019-06-01-preview",
"body": {
"tableId": "{table_id}",
"rows": [
{
"column1": "@{body('Transform_data')}"
}
]
}
},
"runAfter": {
"Transform_data": [
"Succeeded"
]
},
"type": "ApiConnection"
}
},
"parameters": {
"$connections": {
"defaultValue": {},
"type": "Object"
}
},
"triggers": {
"recurrence": {
"recurrence": {
"frequency": "Hour",
"interval": 1
},
"type": "Recurrence"
}
}
}
}

In this example, we create a Logic App workflow that uses a recurrence trigger to periodically retrieve data from an external API using the “Get_data” action. The retrieved data is then transformed using the “Transform_data” action. Finally, the transformed data is inserted into an Azure SQL Database table using the “Insert_row” action. The workflow is configured to run every hour, but you can adjust the recurrence and data transformation logic based on your requirements.

Azure Logic Apps provides a wealth of possibilities for automating your database workflows. Whether you need to integrate data, synchronize databases, or manipulate data before storage, Logic Apps can help simplify and streamline your processes. Start leveraging the power of Azure Logic Apps to automate your database workflows and improve efficiency in your Azure SQL solutions.