Edit

Share via

Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Managed Instance (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 Managed Instance database, which represents a read-only, continuously replicated copy of chosen database from your Azure SQL Managed Instance in OneLake.

Prerequisites

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

The System Assigned Managed Identity (SAMI) of your Azure SQL Managed Instance 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 SQL Managed Instance 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 Managed Instance.

You can accomplish this with a login and mapped database user. Following the principle of least privilege for security, you should only grant CONTROL DATABASE permission in the database you intend to mirror.

Use a login and mapped database user

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

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

    • Create a SQL Authenticated login. You can choose any name for this login, substitute it in the following script for <fabric_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. Switch your query scope to the database you want to mirror. Substitute the name of your database for <mirroring_source_database> and run the following T-SQL:

    USE [<mirroring_source_database>];

  4. Create a database user connected to the login. Substitute the name of a new database user for this purpose for <fabric_user>:

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

    Or, for Microsoft Entra logins,

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

Create a mirrored Azure SQL Managed Instance 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 Managed Instance (preview).

Connect to your Azure SQL Managed Instance

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

  1. Under New sources, select Azure SQL Managed Instance. Or, select an existing Azure SQL Managed Instance connection from the OneLake catalog.
    1. You can't use existing Azure SQL Managed Instance connections with type "SQL Server" (generic connection type). Only connections with connection type "SQL Managed Instance" are supported for mirroring of Azure SQL Managed Instance data.
  2. If you selected New connection, enter the connection details to the Azure SQL Managed Instance. You need to connect to a specific database, you can't set up mirroring for the entire SQL managed instance and all its databases.
    • Server: You can find the Server name by navigating to the Azure SQL Managed Instance Networking page in the Azure portal (under Security menu) and looking at the Public Endpoint field. For example, <managed_instance_name>.public.<dns_zone>.database.windows.net,3342.
    • Database: Enter the name of database you wish to mirror.
    • Connection: Create new connection.
    • Connection name: An automatic name is provided. You can change it to facilitate finding this SQL managed instance database connection at a future time, if needed.
    • 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.

    • If tables can't be mirrored at all, they show an error icon and relevant explanation text. Likewise, if tables can only mirror with limitations, a warning icon is shown with relevant explanation text.

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

  2. On the next screen, give the destination item a name and select Create mirrored database. Now wait a minute or two for Fabric to provision everything for you.

  3. After 2-5 minutes, 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 the initial copying of the tables is finished, 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.

These are the replicating statuses:

  • For overall database level monitoring:

    • Running – Replication is currently running bringing snapshot and change data into OneLake.
    • Running with warning: Replication is running, with transient errors
    • Stopping/Stopped – Replication is stopped.
    • Error – Fatal error in replication that can't be recovered.

  • For table level monitoring:

    • Running –The data from the table is successfully being replicated into the warehouse.
    • Running with warning – Warning of non-fatal error with replication of the data from the table
    • Stopping/Stopped - Replication has stopped
    • Error – Fatal error in replication for that table.

If the initial sync is completed, a Last completed timestamp is shown next to the table name. This timestamp indicates the time when Fabric has last checked the table for changes.

Also, note the Rows replicated column. It counts all the rows that have been replicated for the table. Each time a row is replicated, it is counted again. This means that, for example, inserting a row with primary key =1 on the source increases the "Rows replicated" count by one. If you update the row with the same primary key, replicates to Fabric again, and the row count increases by one, even though it's the same row which replicated again. Fabric counts all replications that happened on the row, including inserts, deletes, updates.

The Monitor replication screen also reflects any errors and warnings with tables being mirrored. If the table has unsupported column types or if the entire table is unsupported (for example, in memory or columnstore indexes), a notification about the limitation is shown on this screen. 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