Connecting Azure SQL Database with Microsoft Fabric: Step-by-Step Guide with Mirroring Feature

In this blog post, we will walk you through the process of connecting an Azure SQL Database to Microsoft Fabric, utilizing the mirroring feature for high availability and disaster recovery. By the end of this guide, you'll have a robust setup ready for production use.

Table of Contents

1. Introduction

2. Prerequisites

3. Setting Up Azure SQL Database

4. Configuring Microsoft Fabric

5. Enabling Mirroring Feature

6. Querying Mirrored Data

7. Monitoring Replication

8. Conclusion

1. Introduction

Microsoft Fabric offers a seamless integration with Azure SQL Database, providing enhanced data management capabilities. The mirroring feature ensures data redundancy and high availability, critical for business continuity.

2. Prerequisites

Before we begin, make sure you have the following:

• An active Azure subscription

• An Azure SQL Database instance

• Microsoft Fabric account

• Basic understanding of SQL and cloud services

3. Setting Up Azure SQL Database

I assume that you already set up SQL server in Azure and correctly configured it by allowing Firewall rules to your Azure resources.

If you don't have an Azure SQL Database set up yet, follow these steps to create one.

Step 1: Log in to Azure Portal

Go to the Azure Portal and log in with your credentials.

Step 2: Create a New SQL Database

1. Navigate to "Create a resource" > "Databases" > "SQL Database".

2. Fill in the necessary details:

   • Subscription: Select your subscription

   • Resource Group: Create a new resource group or select an existing one

   • Database Name: Choose a name for your database

   • Server: Create a new server or select an existing one

Compute + Storage: Configure based on your needs

I am using WorldWideImporters Standard DB as my database & have selected Pricing tier as "General Purpose - Serverless: Gen5, 1 vCore" for the demo/test purpose.

When I first selected DTU-based pricing tier (basic), it doesn't allow me to start replication.Make sure to select vCore-based pricing model if you tryout with mirroring for smoother experience.

Step 3: Configure Networking and Additional Settings

1. In the Networking tab, configure the connectivity method (Public endpoint or Private endpoint).

2. Configure other necessary settings under the Additional Settings tab.

3. Review and create the database.

After following the steps successfully, you can query the DB like below.

4. Configuring Microsoft Fabric

Next, let's configure Microsoft Fabric to connect to the Azure SQL Database.

Step 1: Log in to Microsoft Fabric & Select the workload

Log in to your Microsoft Fabric account. Select the Data Warehouse Persona and click the following.

• Select "Azure SQL Database"

Step 2: Navigate to Data Connections

1. Go to "Settings" > "Data Connections".

2. Click on "Add New Connection".

Step 3: Connect to Azure SQL Database

1. Select "Azure SQL Database" from the list of available data sources.

2. Fill in the necessary connection details:

   • Server Name: Your Azure SQL Database server name

   • Database Name: Your database name

   • Authentication Type: SQL Server Authentication or Azure AD Authentication

   • Username and Password: Credentials for the database

5. Enabling Mirroring Feature

To ensure high availability, enable the mirroring feature in Microsoft Fabric.

Step 1: Access Mirroring Settings

1. Navigate to "Data Connections" > your connected Azure SQL Database.

2. Go to the "Settings" tab and locate the "Mirroring" section.

Step 2: Enable Mirroring

1. Turn on the mirroring feature.

2. Configure the secondary database (mirror) settings. Ensure the secondary database is in a different region for disaster recovery.

Step 3: Save and Apply

Save the settings and apply the configuration.

6. Querying Mirrored Data

Once mirroring is enabled, you can query the mirrored data to ensure it's synchronized correctly.

Step 1: Connect to the Secondary Database

Use SQL Server Management Studio (SSMS) or any SQL client to connect to the secondary database. You'll need the server name and authentication details.

Step 2: Run Queries

Run your usual SQL queries to ensure the data is being mirrored properly.

sqlCopy code-- Example query to check data in the secondary database
SELECT TOP 100 * FROM [YourDatabase].[dbo].[YourTable];

Step 3: Compare Data

Ensure the data in the secondary database matches the primary database. This confirms that the mirroring setup is working as expected.

7. Monitoring Replication

Monitoring replication is crucial to ensure data integrity and availability.

Step 1: Use Azure Portal Metrics

Azure provides built-in monitoring tools to track the health and performance of your SQL databases.

1. In the Azure Portal, go to your SQL Database.

2. Navigate to "Monitoring" > "Metrics".

Step 2: Configure Alerts

Set up alerts to notify you of any issues with replication or performance.

1. Go to "Monitoring" > "Alerts".

2. Click on "New alert rule" and configure based on the metrics you want to monitor.

Step 3: Review Replication Health in Microsoft Fabric

Microsoft Fabric also provides tools to monitor the health of your data connections.

1. In Microsoft Fabric, go to "Data Connections" > your connected Azure SQL Database.

2. Check the replication status and health metrics.

8. Conclusion

Connecting Azure SQL Database with Microsoft Fabric using the mirroring feature is a powerful way to ensure high availability and disaster recovery for your data. With this setup, you can be confident that your data is safe and accessible at all times.

Feel free to reach out with any questions or feedback in the comments below. Happy connecting!