{"id":112337,"date":"2026-09-07T12:00:00","date_gmt":"2026-09-07T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=112337"},"modified":"2026-08-26T15:57:25","modified_gmt":"2026-08-26T15:57:25","slug":"how-to-mirror-data-from-sql-server-to-microsoft-fabric-complete-guide","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/how-to-mirror-data-from-sql-server-to-microsoft-fabric-complete-guide\/","title":{"rendered":"How to mirror data from SQL Server to Microsoft Fabric: complete guide"},"content":{"rendered":"\n

Mirroring in Microsoft Fabric<\/a> is useful when you need to make SQL Server<\/a> data available in Fabric. Perhaps in OneLake<\/a>, for example, if you need to use the data for reporting, analytics, notebooks<\/a>, or another Fabric workload<\/a>.<\/strong><\/p>\n\n\n\n

However, it’s not suitable for everything<\/em>, as I’ll explain in this complete guide to mirroring data from SQL Server to Microsoft Fabric.<\/strong><\/p>\n\n\n\n

Before we get started, a note about compatibility. Since SQL Server 2005, there’s been a type of database mirroring totally unrelated to Microsoft Fabric mirroring<\/a>. I wish these types of terms weren’t so overloaded but, in this article, when I say mirroring<\/em>, I’m talking only<\/em> about Fabric mirroring.<\/p>\n\n\n\n

Can I use Microsoft Fabric mirroring for my pre-2025 version of SQL Server?<\/h2>\n\n\n\n

The way Microsoft Fabric mirroring works has changed in SQL Server 2025 compared to earlier versions. <\/strong><\/p>\n\n\n\n

For earlier versions that are supported (SQL Server 2016 to SQL Server 2022), mirroring uses Change Data Capture (CDC)<\/a>. The source database must<\/em> support CDC as the mirroring setup either uses CDC that’s already enabled, or<\/em> helps configure it as part of the setup process.<\/p>\n\n\n\n

On Windows, Fabric mirroring is supported in Standard, Enterprise, and Developer editions. It’s also supported on Linux, but only from SQL Server 2017 CU18<\/a> onwards.<\/p>\n\n\n\n

Mirroring also works with a variety of hosted environments. You can work with mirroring for SQL Server on-premises, SQL Server in either Azure virtual machines<\/a>, and even non-Azure clouds like AWS<\/a> and Google Cloud Platform (GCP)<\/a>.<\/p>\n\n\n\n

Should I use Microsoft Fabric mirroring for SQL Server data?<\/h2>\n\n\n\n

Microsoft Fabric mirroring works well in near real-time analytical replication<\/a> scenarios. The insert, update, and delete replication latency is low, keeping the data close to the current state. Mirroring is a good fit when you or your organization wants current or near-current analytical copies without building a more traditional batch pipeline.<\/strong><\/p>\n\n\n\n

However, mirroring is not the best choice for every<\/em> SQL Server integration need. <\/strong><\/p>\n\n\n\n

For a start, mirroring doesn’t guarantee 100% fresh data in the way that readable synchronous replicas in availability groups<\/a> do. Instead, it’s designed to reduce ETL (extract, transform, load)<\/a> complexity for the right workloads.*<\/p>\n\n\n\n

So, you’ll be better off taking another approach if the main requirement is heavy reshaping before the data becomes useful, the source configuration is unsupported, or the solution depends on features that mirroring doesn’t support.<\/p>\n\n\n\n

*Data transformation<\/a> still needs to happen somewhere in the system. Mirroring fits when the immediate need is simply to replicate operational data into Fabric and make it available quickly<\/em><\/em>.<\/p>\n\n\n\n

How does Fabric mirroring work for SQL Server 2025?<\/h2>\n\n\n\n

SQL Server 2025 uses a different replication mechanism for mirroring compared to earlier versions. This means design, setup, and troubleshooting assumptions need to change as well.<\/strong> The key technical change is that SQL Server 2025 uses the Fabric mirroring change feed feature. <\/strong><\/p>\n\n\n\n

This alters which source features are required, which are incompatible, and which troubleshooting tools you use to solve potential replication issues.<\/p>\n\n\n\n

I do think this functionality is better (and this is a better direction for mirroring overall), but there is currently one key limitation: SQL Server 2025 mirroring is currently only supported for on-premises instances. I’m sure this will change over time but, for now, it’s not ideal.<\/p>\n\n\n\n

For example, mirroring is not currently supported for SQL Server 2025 instances running in an Azure Virtual Machine, and there’s no Linux support at all<\/em>.<\/strong><\/p>\n\n\n\n

With this change, you’ll also use different change-feed-specific dynamic management views (DMVs)<\/a> and stored procedures<\/a> to the ones used in previous versions.<\/p>\n\n\n\n

\n
\n
\n
\n

Fast, reliable and consistent SQL Server development…<\/h3>\n
\n …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows. <\/div>\n <\/div>\n Learn more & try for free<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

What do I need for Fabric mirroring for SQL Server 2025?<\/h2>\n\n\n\n

Mirroring for SQL Server 2025 requires Azure Arc<\/a> – and the server must<\/em> be connected to it, so you need Azure Extension for SQL Server as well. <\/strong><\/p>\n\n\n\n

You can either set it up during installation of SQL Server or add it later.<\/p>\n\n\n\n

Gateway and network connectivity also need to be in place. You need either an on-premises data gateway<\/a> or a virtual network data gateway<\/a>. The gateway\u2019s network must be able to connect to the SQL Server instance, either through a private endpoint<\/a> or<\/em> because the firewall rule allows it.<\/p>\n\n\n\n

What you need before adding Azure Arc to an existing server<\/h3>\n\n\n\n

Before attempting to add Azure Arc to an existing server, you need PowerShell<\/a> installed on your system and an appropriate Azure Arc user account. You’ll also need to have registered the required resource providers.<\/strong><\/p>\n\n\n\n

These are the most important things to get right first time. Once everything’s in place, the GUI (graphical user interface) in the portal for setting up mirroring is simple.<\/p>\n\n\n\n

SQL Server managed identities<\/h3>\n\n\n\n

Over the years, I’ve been disappointed by the lack of support for managed identities<\/a> across the SQL Server product line, including in Azure Analysis Services (AAS)<\/a>.<\/p>\n\n\n\n

For SQL Server 2025 specifically, the identity story relies on Azure Arc integration. This means that mirroring sits in a hybrid-management model rather than a simple direct-database pattern. Mirroring then makes use of a managed identity in conjunction with Azure Arc.<\/strong><\/p>\n\n\n\n

Even so, source access still needs secure authentication and least privilege. Microsoft\u2019s security guidance<\/a> recommends creating a dedicated database user with limited permissions, following the principle of least privilege.<\/p>\n\n\n\n

Supported source authentication methods are currently SQL authentication<\/a> with username and password, and Microsoft Entra ID<\/a>.<\/p>\n\n\n\n

Once Arc is in place, you should be able to check the managed identities by querying:<\/p>\n\n\n\n

USE [master];\nSELECT *\nFROM sys.dm_server_managed_identities;<\/pre><\/div>\n\n\n\n
The managed identity requires read\/write permission on the workspace. If you use the portal to configure mirroring, it will automatically assign this for you. <\/p>\n\n\n\n
Also note that, once Arc is in place, the user configured for mirroring doesn’t also need to be an admin. <\/p>\n\n\n\n
Here’s an example for a SQL login. You can use this or<\/em> an Entra ID:<\/p>\n\n\n\n
CREATE USER [Fabric User Name] \nFOR LOGIN [Fabric User Name];\nGRANT SELECT, \n      ALTER ANY EXTERNAL MIRROR, \n      VIEW DATABASE PERFORMANCE STATE, \n      VIEW DATABASE SECURITY STATE \nTO [Fabric User Name];<\/pre><\/div>\n\n\n\n
The user will also need permissions in the source database.<\/p>\n\n\n\n
How do Always On Availability groups work with Fabric mirroring for SQL Server?<\/h2>\n\n\n\n
When using Always On Availability groups<\/a> with Fabric mirroring for SQL Server, you must<\/em> ensure you connect to the listener during configuration – not<\/em> directly to the primary node.<\/strong><\/p>\n\n\n\n
Mirroring will then continue to work on failover<\/a> as long as:<\/p>\n\n\n
\n