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
Introduction
Prerequisites
Setting Up Azure SQL Database
Configuring Microsoft Fabric
Enabling Mirroring Feature
Querying Mirrored Data
Monitoring Replication
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
Navigate to "Create a resource" > "Databases" > "SQL Database".
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
In the Networking tab, configure the connectivity method (Public endpoint or Private endpoint).
Configure other necessary settings under the Additional Settings tab.
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
Go to "Settings" > "Data Connections".
Click on "Add New Connection".
Step 3: Connect to Azure SQL Database
Select "Azure SQL Database" from the list of available data sources.
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
Navigate to "Data Connections" > your connected Azure SQL Database.
Go to the "Settings" tab and locate the "Mirroring" section.
Step 2: Enable Mirroring
Turn on the mirroring feature.
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.
In the Azure Portal, go to your SQL Database.
Navigate to "Monitoring" > "Metrics".
Step 2: Configure Alerts
Set up alerts to notify you of any issues with replication or performance.
Go to "Monitoring" > "Alerts".
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.
In Microsoft Fabric, go to "Data Connections" > your connected Azure SQL Database.
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!