Edit

Share via

Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Database

  • Article

Mirroring in Fabric is an enterprise, cloud-based, zero-ETL, SaaS technology. In this section, you learn how to create a mirrored Azure SQL Database, which creates a read-only, continuously replicated copy of your Azure SQL Database data in OneLake.

Prerequisites

  • The Fabric capacity needs to be active and running. A paused or deleted capacity will affect Mirroring and no data will be replicated.
  • Enable the Fabric tenant setting Service principals can use Fabric APIs. To learn how to enable tenant settings, see Fabric Tenant settings.
  • Networking requirements for Fabric to access your Azure SQL Database:
    • Currently, Mirroring doesn't support Azure SQL Database logical servers behind an Azure Virtual Network or private networking. If you have your Azure SQL logical server behind a private network, you can't enable Azure SQL Database mirroring.
    • You need to update your Azure SQL logical server firewall rules to Allow public network access, and enable the Allow Azure services option to connect to your Azure SQL Database logical server.

Enable System Assigned Managed Identity (SAMI) of your Azure SQL logical server

The System Assigned Managed Identity (SAMI) of your Azure SQL logical server must be enabled, and must be the primary identity, to publish data to Fabric OneLake.

  1. To configure or verify that the SAMI is enabled, go to your logical SQL Server in the Azure portal. Under Security in the resource menu, select Identity.
  2. Under System assigned managed identity, select Status to On.
  3. The SAMI must be the primary identity. Verify the SAMI is the primary identity with the following T-SQL query: SELECT * FROM sys.dm_server_managed_identities;

Database principal for Fabric

Next, you need to create a way for the Fabric service to connect to your Azure SQL Database.

You can accomplish this with a login and mapped database user.

Use a login and mapped database user

  1. Connect to your Azure SQL logical server using SQL Server Management Studio (SSMS) or the mssql extension with Visual Studio Code. Connect to the master database.

  2. Create a server login and assign the appropriate permissions.

    • Create a SQL Authenticated login named fabric_login. You can choose any name for this login. Provide your own strong password. Run the following T-SQL script in the master database:
    CREATE LOGIN fabric_login WITH PASSWORD = '<strong password>';
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER fabric_login;
    • Or, create a Microsoft Entra ID authenticated login from an existing account. Run the following T-SQL script in the master database:
    CREATE LOGIN [bob@contoso.com] FROM EXTERNAL PROVIDER;
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [bob@contoso.com];

  3. Connect to the Azure SQL Database your plan to mirror to Microsoft Fabric, using the Azure portal query editor, SQL Server Management Studio (SSMS), or the mssql extension with Visual Studio Code.

  4. Create a database user connected to the login:

    CREATE USER fabric_user FOR LOGIN fabric_login;
GRANT CONTROL TO fabric_user;

    Or,

    CREATE USER [bob@contoso.com] FOR LOGIN [bob@contoso.com];
GRANT CONTROL TO [bob@contoso.com];

Create a mirrored Azure SQL Database

  1. Open the Fabric portal.
  2. Use an existing workspace, or create a new workspace.
  3. Navigate to the Create pane. Select the Create icon.
  4. Scroll to the Data Warehouse section and then select Mirrored Azure SQL Database. Enter the name of your Azure SQL Database to be mirrored, then select Create.

Connect to your Azure SQL Database

To enable Mirroring, you will need to connect to the Azure SQL logical server from Fabric to initiate connection between SQL Database and Fabric. The following steps guide you through the process of creating the connection to your Azure SQL Database:

  1. Under New sources, select Azure SQL Database. Or, select an existing Azure SQL Database connection from the OneLake hub.
  2. If you selected New connection, enter the connection details to the Azure SQL Database.
    • Server: You can find the Server name by navigating to the Azure SQL Database Overview page in the Azure portal. For example, server-name.database.windows.net.
    • Database: Enter the name of your Azure SQL Database.
    • Connection: Create new connection.
    • Connection name: An automatic name is provided. You can change it.
    • Authentication kind:
      • Basic (SQL Authentication)
      • Organization account (Microsoft Entra ID)
      • Tenant ID (Azure Service Principal)
  3. Select Connect.

Start mirroring process

  1. The Configure mirroring screen allows you to mirror all data in the database, by default.

    • Mirror all data means that any new tables created after Mirroring is started will be mirrored.

    • Optionally, choose only certain objects to mirror. Disable the Mirror all data option, then select individual tables from your database.

    For this tutorial, we select the Mirror all data option.

  2. Select Mirror database. Mirroring begins.

  3. Wait for 2-5 minutes. Then, select Monitor replication to see the status.

  4. After a few minutes, the status should change to Running, which means the tables are being synchronized.

    If you don't see the tables and the corresponding replication status, wait a few seconds and then refresh the panel.

  5. When they have finished the initial copying of the tables, a date appears in the Last refresh column.

  6. Now that your data is up and running, there are various analytics scenarios available across all of Fabric.

Important

Any granular security established in the source database must be re-configured in the mirrored database in Microsoft Fabric.

Monitor Fabric Mirroring

Once mirroring is configured, you're directed to the Mirroring Status page. Here, you can monitor the current state of replication.

For more information and details on the replication states, see Monitor Fabric mirrored database replication.

Important

If there are no updates in the source tables, the replicator engine will start to back off with an exponentially increasing duration, up to an hour. The replicator engine will automatically resume regular polling after updated data is detected.

Related content