Edit

Share via

Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Database (Preview)

  • 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

  • Create or use an existing Azure SQL Database.
  • You need an existing Fabric capacity. If you don't, start a Fabric trial.
  • Enable Mirroring in your Microsoft Fabric tenant.
  • Enable the Fabric tenant setting Allow service principals to user Power BI APIs. To learn how to enable tenant settings, see Fabric Tenant settings.
    • If you do not see Mirroring in your Fabric workspace or tenant, your organization admin must enable in admin 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.
    • Currently, you must update your Azure SQL logical server firewall rules to Allow public network access. You can perform this change via the Azure portal, Azure PowerShell, and Azure CLI.
    • You must enable the Allow Azure services option to connect to your Azure SQL Database logical server. You can make this change in the Networking section of Azure SQL logical server in the Azure portal.

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

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

  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 one of two ways, with a login and mapped database user, or a contained database user:

Use a login and mapped database user

  1. Connect to your Azure SQL logical server using SQL Server Management Studio (SSMS) or Azure Data Studio. Connect to the master database.

  2. Execute the following script to create a SQL Authenticated login named fabric_login. You can choose any name for this login. Provide your own strong password. Run the following in the master database:

    CREATE LOGIN fabric_login WITH PASSWORD = '<strong password>';
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER fabric_login;

  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 Azure Data Studio.

  4. Create a database user connected to the login:

    CREATE USER fabric_user FOR LOGIN fabric_login;

Use a contained database user

  1. 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 Azure Data Studio.

  2. Create a contained database user with password, and grant the CONTROL permission to the contained database user with the following T-SQL script:

    CREATE USER fabric_user WITH PASSWORD = '<strong password>';
GRANT CONTROL TO fabric_user;

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.
  4. Select the Create icon.
  5. Scroll to the Data Warehouse section and then select Mirrored Azure SQL Database (preview).
  6. Enter the name of your Azure SQL database to be mirrored, then select Create.

Connect to your Azure SQL Database

The following steps guide you through the process of creating the connection to your Azure SQL Database:

  1. Select Azure SQL Database under New connection or select an existing connection.

  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 Mirror 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