Automate workflows for SQL Server or Azure SQL Database by using Azure Logic Apps

This article shows how you can access data in your SQL database from inside a logic app with the SQL Server connector. That way, you can automate tasks, processes, or workflows that manage your SQL data and resources by creating logic apps. The SQL Server connector works for both on-premises SQL Server and for cloud-based Azure SQL Database.

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.

If you're new to logic apps, review What is Azure Logic Apps and Quickstart: Create your first logic app. For connector-specific technical information, limitations, and known issues, see the SQL Server connector reference page.

Prerequisites

  • An Azure subscription. If you don't have a subscription, sign up for a free Azure account.

  • An SQL Server database or Azure SQL database

    Your tables must have data so that your logic app can return results when calling operations. If you create an Azure SQL Database, you can use sample databases, which are included.

  • Your SQL server name, database name, your user name, and your password. You need these credentials so that you can authorize your logic to access your SQL server.

    • For SQL Server, you can find these details in the connection string:

      Server={your-server-address};Database={your-database-name};User Id={your-user-name};Password={your-password};

    • For Azure SQL Database, you can find these details in the connection string, or in the Azure portal under the SQL Database properties:

      Server=tcp:{your-server-name}.database.windows.net,1433;Initial Catalog={your-database-name};Persist Security Info=False;User ID={your-user-name};Password={your-password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

  • The on-premises data gateway installed on a local computer and an Azure data gateway resource created in the Azure portal for these scenarios:

    • Your logic apps don't run in an integration service environment (ISE).

    • Your logic apps do run in an integration service environment, but you have to use Windows authentication for your SQL Server connection. For this scenario, use the SQL Server connector's non-ISE version along with the data gateway because the ISE version doesn't support Windows authentication.

  • The logic app where you need access to your SQL database. To start your logic app with a SQL trigger, you need a blank logic app.

Add a SQL trigger

In Azure Logic Apps, every logic app must start with a trigger, which fires when a specific event happens or when a specific condition is met. Each time that the trigger fires, the Logic Apps engine creates a logic app instance and starts running your logic app's workflow.

  1. In the Azure portal or Visual Studio, create a blank logic app, which opens Logic Apps Designer. This example uses the Azure portal.

  2. On the designer, in the search box, enter "sql server" as your filter. From the triggers list, select the SQL trigger that you want.

    This example uses the When an item is created trigger.

    Select "When an item is created" trigger

  3. If you are prompted to create a connection, create your SQL connection now. If your connection exists, select a Table name.

    Select the table that you want

  4. Set the Interval and Frequency properties, which specify how often your logic app checks the table.

    This trigger returns only one row from the selected table, nothing else. To perform other tasks, add other actions that perform the tasks you want. For example, to view the data in this row, you can add other actions that create a file that includes the fields from the returned row, and then send email alerts. To learn about other available actions for this connector, see the connector's reference page.

  5. When you're done, on the designer toolbar, select Save.

    This step automatically enables and publishes your logic app live in Azure.

Add a SQL action

In Azure Logic Apps, an action is a step in your workflow that follows a trigger or another action. In this example, the logic app starts with the Recurrence trigger, and calls an action that gets a row from a SQL database.

  1. In the Azure portal or Visual Studio, open your logic app in Logic Apps Designer. This example uses the Azure portal.

  2. Under the trigger or action where you want to add the SQL action, select New step.

    Add new step to your logic app

    To add an action between existing steps, move your mouse over the connecting arrow. Select the plus sign (+) that appears, and then select Add an action.

  3. Under Choose an action, in the search box, enter "sql server" as your filter. From the actions list, select the SQL action that you want.

    This example uses the Get row action, which gets a single record.

    Find and select SQL "Get row" action

    This action returns only one row from the selected table, nothing else. To view the data in this row, you might add other actions that create a file that includes the fields from the returned row, and store that file in a cloud storage account. To learn about other available actions for this connector, see the connector's reference page.

  4. If you are prompted to create a connection, create your SQL connection now. If your connection exists, select a Table name, and enter the Row ID for the record that you want.

    Enter the table name and row ID

  5. When you're done, on the designer toolbar, select Save.

    This step automatically enables and publishes your logic app live in Azure.

Connect to your database

When you use a trigger or action that accesses a service for the first time, the Logic Apps Designer prompts you to create a connection to that service. You can then provide the necessary connection information directly from your logic app inside the designer.

Connect to Azure SQL Database

When the SQL trigger or action prompts you for connection information, follow these steps, which work for both triggers and actions.

  1. For Connection Name, create a name for your connection.

  2. Under SQL Server Name, select your Azure SQL server. When the SQL Database Name list appears, select your database. Provide the user name and password for your Azure SQL server.

    You can also find this information either in the Azure portal under your SQL database properties or in your connection string:

    • User ID=<your-user-name>
    • Password=<your-password>

    Create connection to Azure SQL Database

  3. When you're done, select Create.

  4. After you create your connection, continue with Add a SQL trigger or Add a SQL action.

Connect to SQL Server

When the SQL trigger or action prompts you for connection information, follow these steps, which work for both triggers and actions. For scenarios that require that you install the on-premises data gateway on a local computer and create the Azure data gateway resource, make sure that you complete these requirements first. Otherwise, your gateway resource won't appear in the gateways list when you create your connection.

Also, to use Windows authentication with the SQL Server connector in an integration service environment (ISE), use the connector's non-ISE version and the on-premises data gateway. The ISE-labeled version doesn't support Windows authentication.

  1. For Connection Name, create a name for your connection.

  2. In the trigger or action, select Connect via on-premises data gateway so that the SQL server options appear.

  3. For SQL server Name and SQL database name, provide the address for your SQL server and name for your database. For Username and Password, provide the user name and password for your server.

    You can also find this information in your connection string:

    • Server=<your-server-address>
    • Database=<your-database-name>
    • User ID=<your-user-name>
    • Password=<your-password>

    Create connection to SQL Server

  4. If your SQL server uses Windows or Basic authentication, select the Authentication Type.

  5. Under Gateways, select the Azure subscription that's associated with your previously created on-premises data gateway, and select the name for your on-premises data gateway.

    If your gateway doesn't appear in the list, check that you correctly set up your gateway.

    Create SQL Server connection completed

  6. When you're done, select Create.

  7. After you create your connection, continue with Add SQL trigger or Add SQL action.

Handle bulk data

Sometimes, you have to work with result sets so large that the connector doesn't return all the results at the same time, or you want better control over the size and structure for your result sets. Here's some ways that you can handle such large result sets:

  • To help you manage results as smaller sets, turn on pagination. For more information, see Get bulk data, records, and items by using pagination.

  • Create a stored procedure that organizes the results the way you want.

    When getting or inserting multiple rows, your logic app can iterate through these rows by using an until loop within these limits. However, when your logic app has to work with record sets so large, for example, thousands or millions of rows, that you want to minimize the costs resulting from calls to the database.

    To organize the results in the way that you want, you can create a stored procedure that runs in your SQL instance and uses the SELECT - ORDER BY statement. This solution gives you more control over the size and structure of your results. Your logic app calls the stored procedure by using the SQL Server connector's Execute stored procedure action.

    For more solution details, see these articles:

Connector-specific details

For technical information about this connector's triggers, actions, and limits, see the connector's reference page.

Next steps