SQL to Fabric: Mirroring Databases

Setting the Stage

For organizations heavily reliant on Azure SQL Databases, integrating real-time data into a modern analytics platform like Microsoft Fabric has traditionally involved building complex ETL pipelines. However, with the introduction of Mirroring Azure SQL Database, this process has become significantly more straightforward.

By leveraging mirrored databases, you can seamlessly synchronize your Azure SQL data into Fabric’s OneLake, enabling real-time analytics, AI applications, and data engineering workflows without disrupting your existing database operations.

How Mirrored Databases Work in Fabric

At its core, the mirroring process creates a synchronized copy of your Azure SQL database in Fabric. Unlike traditional replication models that maintain SQL-to-SQL consistency, Fabric takes your SQL data and transparently converts it into a Lakehouse format. A SQL endpoint is then layered on top of this lakehouse, offering a familiar SQL interface for querying and interacting with the mirrored data.

Key Characteristics:

• Transparent Conversion: SQL data is ingested into Fabric’s lakehouse while maintaining the look and feel of a traditional SQL database.
• Real-Time Updates: Changes in the source Azure SQL database (inserts, updates, deletions, schema modifications) are reflected in the mirrored database with minimal latency.
• Integrated Analytics: Once mirrored, the data is ready to use across Fabric services like Power BI, Data Science, and Data Engineering.

Solution

This is the script I use in the video...

Demo Highlights

• Setting up the source database with appropriate permissions and configurations.
• Creating a connection in Fabric and initializing the mirroring process.
• Testing the mirroring functionality by deleting rows in the source database and observing their near real-time removal in the mirrored database and how it also handles column alterations (e.g. adding and removing)

Key Observations:

• Data changes replicated within seconds, making it suitable for most near real-time scenarios.
• Schema modifications (e.g., adding or dropping columns) were handled efficiently.

Step-by-Step

1. Configure the Source Database. Before mirroring can begin, ensure the following prerequisites are met:
   1. Azure SQL Database Requirements. Verify the latest from MSFT Tier and purchasing model support
   2. Create a Login and User. Run the following SQL commands in SQL Server Management Studio (SSMS) to set up permissions
2. Create a Connection in Fabric. Once your database is ready:
   1. Go to Settings > Manage Connections and Gateways in Fabric,
   2. Add a new connection using the SQL Server details and credentials created earlier.
3. Set Up the Mirrored Azure SQL Database
   1. In your Fabric workspace, select New Item > Mirror Azure SQL Database.
   2. Choose the connection created in the previous step.
   3. Select the tables to mirror. Only tables with primary keys can be mirrored. Ensure all necessary tables meet this requirement.
   4. Name the mirrored database and click Create.

Limitations to Consider

While mirrored databases are a powerful feature, they have some current limitations:

• Tenant Restriction: Both the Azure SQL database and the Fabric workspace must reside in the same Microsoft Entra tenant. Cross-tenant mirroring is not supported.
• On-Premises Exclusion: Mirroring is currently limited to Azure SQL databases. On-premises SQL databases cannot be mirrored directly.
• Primary Key Requirement: Only tables with primary keys can be mirrored, which may require schema adjustments.

Conclusion

Fabric Mirrored Databases provide a simple, efficient, and real-time solution for integrating Azure SQL data into Microsoft Fabric. By eliminating the need for complex ETL pipelines, this feature enables organizations to focus on insights rather than infrastructure.

Whether you're enhancing analytics, enabling AI workflows, or streamlining data sharing, mirrored databases are a valuable addition to Fabric’s ecosystem. Ready to get started? Let me know your thoughts!