Contents x
    Azure SQL Database
    • 4 Minutes to read
    • Dark
      Light
    Contents

    Azure SQL Database

    • 4 Minutes to read
    • Dark
      Light
    Article Summary

    Azure SQL Database is a fully managed relational database service offered by Microsoft Azure. It provides a cloud-based platform for hosting and managing SQL Server databases, allowing users to build, scale, and secure applications without the need to manage underlying infrastructure, making it an efficient and flexible solution for various data-driven applications in the cloud.

    Prerequisites

    • You have a running instance of SQL Database.
    • You have configured firewall settings to accept incoming connection from Dataddo IPs.
    • You have created a database user with CREATE, SELECT, UPDATE, INSERT, DELETE permissions.

    Authorize Connection to Azure SQL Database

    In Azure Portal

    Create an Instance of SQL Database

    1. In the Azure portal, select Create a resource from the menu on the left.
    2. Search for SQL Database and click on Create.
    3. Select Subscription, Resource Group, and enter your Database name.
    4. Select your Server (if you already have another SQL Database running) or create a new one.
    5. In the Networking section
      1. Choose Public endpoint for connectivity method.
      2. Select yes for Allow Azure services and resources to access this server. The firewall will be configured in the next steps.
    6. Review the remaining settings, adjust as necessary, and click on Review + create.

    Configure the Firewall Settings

    1. In the Azure portal, select SQL databases and choose the database you created in the previous step.
    2. In the Set server firewall section, add each of Dataddo IPs.

    In Azure SQL Database

    After you've set up your Azure SQL database, you need to create a user who can interact with the database on behalf of Dataddo. This can be done using the SQL Server Management Studio or Azure Data Studio.

    Remember, it's important to regularly review your security settings and follow best practices to ensure your data is protected. Always consider your organization's security policies when setting up users and permissions.

    Create a Database User

    1. Connect to your database with the admin account.
    2. Run the command below to create a new user.
    CREATE USER dataddo WITH PASSWORD = '***';

    Grant Permissions to User

    1. Connect to your database with the admin account.
    2. Run the commands below to grant CREATE, SELECT, INSERT, UPDATE, and DELETE permissions to Dataddo.
    3. Run command to grant ALTER ANY EXTERNAL DATA SOURCE and ADMINISTER BULK OPERATIONS permissions to Dataddo. This is required in order to speed up the data insertion process as Dataddo is using Blob Storage for bulk data insertion.
    GRANT CONNECT SQL TO dataddo;
GRANT SELECT, INSERT, UPDATE, DELETE TO dataddo;
GRANT CREATE TABLE TO dataddo;
GRANT ALTER ANY EXTERNAL DATA SOURCE TO dataddo;
GRANT ADMINISTER BULK OPERATIONS TO dataddo;

    In Dataddo

    1. On the Authorizers page, click on Authorize New Service and select Azure SQL.
    2. You will be asked to fill the following fields.
      1. Server IP or Hostname: Public hostname of the Azure SQL database. You can find this under Server name in the Azure portal.
      2. Database (Catalog): The database you want to use for reading or writing the data. Sometimes this might be referred to as Catalog.
      3. Username: Username of your database user.
      4. Password: Password to your database user.
      5. Port: Port to connect to database. The default is 1433.
    3. After filling-in all the details, click on Save to save the authorization details.

    Create a New Azure SQL Database Destination

    1. On the Destinations page, click on the Create Destination button and select the destination from the list.
    2. Select your authorizer from the drop-down menu.
    3. Name your destination and click on Save.
    Need to authorize another connection?

    Click on Add new Account in drop-down menu during authorizer selection and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.

    Create a Flow to Azure SQL Database

    1. Navigate to Flows and click on Create Flow.
    2. Click on Connect Your Data to add your source(s).
    3. Click on Connect Your Data Destination to add the destination.
    4. Choose the write mode and fill in the other required information.
    5. Check the Data Preview to see if your configuration is correct.
    6. Name your flow and click on Create Flow to finish the setup.

    Table Naming Convention

    When naming your table, please make sure the table name contains only:

    • Letters
    • Numbers
    • Underscores

    Also please keep in mind that when the Microsoft Access driver is used, the table name is limited to 64 characters.

    Troubleshooting

    Truncate Insert Error

    The truncate insert write mode is not possible if your data is

    • Published for replication or
    • Enabled for change data capture (CDC).

    If you try to use truncate insert in these cases, you will receive the following error:

    stream transfer: write data from stream: prepareTx: truncate table if requested: Failed to truncate table: mssql: Cannot truncate table ‘table_name' because it is published for replication or enabled for Change Data Capture.

    For mor information on cases when truncate insert is not possible, refer to Microsoft's official documentation.

    Inserting data takes too long

    Check the permissions and make sure that ALTER ANY EXTERNAL DATA SOURCE was grated. This is required in order to speed up the data insertion process as Dataddo is using Blob Storage for bulk data insertion.

    Was this article helpful?
    What's Next