How to integrate data into Azure SQL with Windsor.ai

Azure SQL is a managed, cloud-based database from Microsoft designed to simplify database management. It can be easily integrated with other Azure services for secure and scalable data solutions.

The key benefits of Azure SQL include its cost-effectiveness, as it eliminates the need for managing physical hardware, excellent performance optimization with features like automatic tuning and query performance insights, enhanced security with built-in transparent data encryption, advanced threat protection, and compliance with global standards. It also supports high availability and disaster recovery, ensuring minimal downtime and data loss. 

By integrating Azure SQL with the Windsor.ai data movement platform, you can:

• Consolidate data from multiple marketing and business platforms (e.g., Facebook Ads, Google Ads, Google Analytics, Salesforce) into a single, secure Azure SQL database.
• Automate data synchronization to ensure the latest metrics are always available.
• Automate data workflows, saving time and reducing manual effort.

Explore our video tutorial and a step-by-step guide to automatically integrate your data into Azure SQL with the Windsor.ai no-code ELT connector.

1. Create a Windsor.ai account and log in.

2. Select the data source which you want to stream data from, e.g., Google Analytics 4 (GA4). Sign in with your associated Google account and select the next step, “Data preview.”

3. You’ll see your Google Analytics 4 data displayed in your Windsor.ai account. 

4. At this step, you need to copy the IP address of Windsor.ai, as it will be used in the Azure SQL setup process to get access to the database. 

For this, scroll down to data destinations, select Azure SQL, and click “Add Destination Task.” Copy the IP address listed at the bottom of the instructions and save it for future use.

Now, let’s proceed with setting up Azure SQL for data integration.

1. First of all, make sure you have an active Azure subscription. 

Go to Azure Portal and create a resource group. Select your Azure subscription and region, and enter the name of the resource group (anything you wish). 

2. In the search tab, find and select “Azure SQL.”

3. Now, you have to create a database server.

On the Azure SQL page, click “Create.” In the SQL Databases section, open the Resource type dropdown and select “Database Server” -> “Create.”

4. Select the resource group you’ve created previously and enter the name of the server (anything you wish). 

In the Authentication Method, select Use SQL Authentication, enter the server admin login name (anything you want to), and enter a password. Make sure to remember this password, as you’ll use it when connecting Windsor.ai with Azure SQL. 

Then click “Review + create.”

5. Let’s change the network configuration of the server so that it can be accessed from Windsor.ai.

• Go to your created server, open the Security dropdown in the sidebar, and click “Networking.”
• Open the Public Access tab. In the Public Network Access options, choose “Selected networks.”
• In the Firewall Rules section, click “Add a firewall rule,” enter the rule name (anything you wish), and enter the Windsor IP Address that you’ve saved previously in the start IP and end IP fields.
• Also, click “Add your client IPv4 address” to allow your machine to connect to the server to view database content later. Complete with the “Save” button.

Your Azure SQL server is now created and configured. Let’s proceed with creating a database for uploading connected Google Analytics 4 data from Windsor.ai.

1. Go to your SQL server’s overview page and click “Create Database.” Enter the name of your database, select the compute and storage you want, and click “Review + create.” Your database is now created and running.

2. Get the required fields from the Azure portal to create the connection between Azure SQL and Windsor.ai:

• Get the Azure SQL Server, Azure SQL User Name (Admin Login), and Database Name. Go to your created database, and in the sidebar, open the Settings dropdown and click “Properties.”

• Get the Azure SQL Port. Go to your created database. In the sidebar, open the Settings dropdown, click “Connection Strings,” and select the Go tab. Copy the port number (Azure SQL uses “1433” by default).

Here we go, you have set up the Azure SQL server and database on the Azure portal side. 

Now, let’s import your data from Windsor.ai into this Azure SQL database.

1. Return to your destination task for Azure SQL data integration in the Windsor.ai dashboard.

Enter all the required credentials in the destination task form: 

• Task name (you can provide any based on the data integration purpose).
• User name, server, port and database name that you got from Azure Portal.
• The password you set while creating the Azure SQL server.
• Table name (you can provide any based on the data integration purpose); it will be created in your database automatically. If you already have a table for your Google Analytics data, you can enter that table name.

Click “Test Connection.”

If the connection is set properly, you’ll see a success message at the bottom; otherwise, an error message will appear. When successful, click “Save” in the lower right corner of the form. The data stream to the Azure SQL database has started.

2. You can now see the task running in the selected data destination section. The green ‘upload‘ button with the status ‘ok‘ indicates that the task is active and running successfully.

3. Verify that your data is being added to the Azure SQL database. 

Go to your Azure portal database and select “Query Editor” from the sidebar. Enter your Azure SQL login and password, and you’ll see the new table there. You can preview the data by querying that table. 

Cheers! Your Google Analytics 4 data is now integrated into the Azure SQL and ready for detailed analysis.