1. Mirroring in Fabric:
   • It’s a data replication feature.
   • It brings data from different systems into one analytics platform, Fabric’s OneLake.
   • It’s cost-effective and has low latency.
2. Data Sources:
   • Mirroring supports data replication from various sources like Azure SQL Database, Azure Cosmos DB, and Snowflake.
   • It continuously brings data into OneLake.
3. Benefits of Having Data in OneLake:
   • OneLake stores the most recent data in a format that’s easy to query.
   • Users can access this data for various analytics tasks within Fabric.
4. Usage of Data in OneLake:
   • Users can leverage Fabric’s services like Spark analytics, notebooks, data engineering, and Power BI Reports with the replicated data.
5. Integration and Collaboration:
   • Fabric is designed for integration and collaboration.
   • Mirroring supports Delta Lake table format, allowing integration with various technology solutions.
   • This facilitates collaboration between Microsoft and other technology providers.
6. Advantages of Mirroring:
   • It’s a turnkey solution, meaning it’s ready to use out of the box.
   • It’s cost-effective and low-latency.
   • It simplifies analytics needs by providing a replica of data in OneLake, usable for all analytical tasks.
7. Accelerating Journey into Fabric:
   • Delta tables replicated through Mirroring can be used in all Fabric experiences.
   • This accelerates the adoption of Fabric for analytics tasks.

Why use Mirroring in Fabric?

• Current Challenges: Many organizations have important data spread across different systems, making it hard to access and work with. This leads to restricted access, friction between people and technology, long wait times to create data pipelines, and limited freedom to use analysis tools.
• Solution with Fabric Mirroring: Fabric Mirroring offers an easy way to solve these problems by quickly replicating data into a centralized data lake called OneLake. This replication happens almost in real-time and the data is converted into a format that’s ready for analysis.
• Key Features of Mirroring: When you use Mirroring in Fabric, it creates three important items:
  • It manages the replication of data into OneLake and converts it into a format suitable for analytics, like Parquet.
  • It sets up a SQL analytics endpoint, allowing you to directly query the mirrored data.
  • It creates a default semantic model, making it easier to understand and work with the data.
• Additional Tools: Along with the SQL Query Editor in Microsoft Fabric, you can use other tools like SQL Server Management Studio, Azure Data Studio, and GitHub Copilot to work with the mirrored data.
• Sharing and Access Control: Fabric also provides features for sharing and access control, ensuring that sensitive information is protected while still enabling collaboration and decision-making across the organization.

How to enable Mirroring in my tenant?

• Power BI administrators have the ability to turn Mirroring on or off for the whole organization or for certain security groups.
• They can do this by adjusting the settings in the Power BI admin portal.
• Once Mirroring is enabled, users will see additional options for creating Mirroring items.
• For more detailed instructions, you can refer to the guide titled “Enable Mirroring in your Microsoft Fabric tenant.

How does the near real time replication of Mirroring work?

• Mirroring means making a copy of your data from one place to another securely.
• You can choose to copy an entire database or just specific tables.
• Once set up, the copied data will always stay up-to-date in the destination location (OneLake) for analysis.
• Key points about Mirroring in Fabric:
  • It’s easy to set up, without needing to create complex data pipelines or manage extra resources.
  • Fabric takes care of everything related to mirroring, so you don’t have to worry about hosting, maintenance, or managing the copied data.

Sharing concepts in Mirroring

• Sharing makes access control easier: It helps manage who can see and use certain data.
• Security features like RLS and OLS ensure control: These features help limit access to sensitive information, allowing you to control who can see what.
• Facilitates secure decision-making: Sharing enables safe and inclusive decision-making across your organization.
• How sharing works:
  • Users can grant others access to a mirrored database without giving access to the entire workspace.
  • When sharing a mirrored database, access to the SQL analytics endpoint and default semantic model is also granted.
• Accessing sharing options:
  • Click on the Share button next to the mirrored database name in the Workspace view.
  • Shared mirrored databases can be accessed through the Data Hub or the “Shared with Me” section in Microsoft Fabric.

Cross-database queries with Mirroring Database

• Your mirrored database’s data is now in OneLake.
• You can use T-SQL to write queries that pull data from different places, like mirrored databases, warehouses, and SQL analytics endpoints, all in one query.
• This allows you to join data from different sources seamlessly.
• For example, you can refer to tables in mirrored databases and warehouses using a three-part naming convention.
• The first part of the naming convention is the name of the mirrored database.

SELECT * 
FROM ContosoWarehouse.dbo.ContosoSalesTable AS Contoso
INNER JOIN Affiliation
ON Affiliation.AffiliationId = Contoso.RecordTypeID;

How to Enable Mirroring?

Enable Mirroring for your tenant

• Mirroring: This feature allows you to enable mirroring for your organization or specific security groups.
• For the entire organization: If your organization has one tenant, enabling this option will activate mirroring for everyone. If you have multiple tenants, you’ll need to enable it separately for each.
• For specific security groups: You can choose to enable mirroring for particular user groups. You can either specify which groups will have mirroring enabled or which ones won’t.
• Steps to enable:
  1. Go to the tenant settings in the admin portal and find Mirroring (preview) under Microsoft Fabric.
  2. Turn on the Mirroring (preview) switch.
  3. Optionally, select specific security groups or exclude certain groups.
  4. Click Apply to save your changes.

Enable Mirroring for a capacity

Capacity admins have the power to change this setting if they need to.For example, let’s say your organization decided not to turn on Mirroring because it’s still in the testing phase (preview). However, your developers want to try it out.In this case, Mirroring can be turned on for a specific capacity.Here are the steps to do that:

• Go to the capacity settings in the admin portal.
• Choose the capacity where you want to turn on Mirroring.
• Click on the “Delegate tenant settings” tab.
• Open the Mirroring (Preview) setting.
• Check the box that says “Override tenant admin selection” and make sure the Mirroring (preview) setting is turned on.
• (Optional) You can specify certain security groups to have access to Mirroring. Or you can exclude specific users.
• Click on Apply to save the changes.

How to Monitor Fabric mirrored database replication

Once mirroring is configured, visit the Monitor replication page to monitor the current state of replication.

The following are the possible statuses for the replication:

How to Explore data in your mirrored database using Microsoft

Fabric

1. SQL Analytics Endpoint:
   • It’s a read-only T-SQL layer in Microsoft Fabric.
   • You can analyze data in delta tables using visual query editor or T-SQL.
   • Access it by selecting the corresponding item in workspace view.
2. Data View:
   • Helps preview data within tables or views.
   • You can see sample data (top 1,000 rows) in Data preview mode.
3. Visual Queries:
   • No-code experience to create T-SQL queries visually.
   • Use drag-and-drop to design queries.
4. SQL Queries:
   • Provides a query editor for creating T-SQL queries.
   • Supports features like IntelliSense and syntax highlighting.
5. Notebooks:
   • Allows you to develop Spark jobs and ML experiments.
   • Explore mirrored tables by creating a shortcut to Lakehouse.
6. Delta Files Access:
   • Access mirrored table data in Delta format files directly.
7. Data Modeling:
   • Power BI datasets represent semantic models with business-friendly terms.
   • Inherits business logic from mirrored databases.
   • Allows for easy data modeling and relationship definition.

Explore data in your mirrored database directly in OneLake

1. Open the Mirrored Database item and go to the SQL analytics endpoint.
2. Click the three dots next to any table.
3. Select Properties and then copy the URL.
4. Open the Azure Storage Explorer app (if you don’t have it, download and install it).
5. Connect to Azure Storage and choose Azure Data Lake Storage (ADLS) Gen2.
6. Sign in using OAuth if needed, and then select the ADLS Gen2 resource.
7. Provide a Display name and paste the SQL analytics endpoint URL into the Blob container or directory URL box.
8. Click Next to finish.
9. Now you can access delta files directly from Azure Storage Explorer.

How to Mirroring Azure SQL Database?

• Fabric’s mirroring feature makes it simple to connect your Azure SQL Databases with other data in Microsoft Fabric without needing complex ETL processes.
• You can regularly copy your Azure SQL databases directly into Fabric’s OneLake.
• Once in Fabric, you can use various tools and scenarios like business intelligence, artificial intelligence, data engineering, data science, and data sharing to analyze and work with your data effectively.

Why use Mirroring in Fabric?

• Mirroring in Fabric means you don’t have to use different services from different companies.
• Instead, you get one product that’s easy to use and works well with other Microsoft and Azure services.
• It’s designed to make your analytics tasks simpler.
• It’s built to work with lots of other technology solutions that can read the Delta Lake table format.
• Overall, it’s about making things easier and more integrated for you.

What analytics experiences are built in?

Mirrored databases are an item in the Fabric Synapse Data Warehousing experience distinct from the Warehouse and SQL analytics endpoint items.

• Mirroring in Fabric: When you mirror a database in Fabric, it creates three things:
  1. Mirrored database item: This manages copying data into OneLake in a format that’s ready for analysis. It’s useful for tasks like data engineering and data science.
  2. SQL analytics endpoint: This is a special endpoint that lets you analyze data using T-SQL commands. It’s read-only, meaning you can’t change the data, but you can explore it and run queries.
  3. Default semantic model: This is like a template for how your data is structured. It helps you understand and work with your data more easily.
• Using the SQL analytics endpoint: With the SQL analytics endpoint, you can do several things:
  • Explore tables and data stored in your Delta Lake tables.
  • Create queries and views without needing to write code.
  • Develop more complex SQL objects like views and stored procedures to organize your data and logic.
  • Manage who has access to your data.
  • Query data from other databases and lakehouses in the same workspace.
• Tools for working with the SQL analytics endpoint: You can use various tools to work with the SQL analytics endpoint, including Microsoft Fabric’s SQL Query Editor, SQL Server Management Studio, Azure Data Studio, and GitHub Copilot. These tools help you interact with your data and perform analysis tasks more efficiently.

How to Configure Microsoft Fabric mirrored databases from Azure SQL Database?

Prerequisites

1. Create or Use an Azure SQL Database: You need to have or create an Azure SQL Database. It can be a single database or one in an elastic pool. If you don’t have one, create a new single database using the Azure SQL Database free offer.
2. Choose a Database for Mirroring: During the preview, it’s recommended to use a copy of an existing database or a test/development database that you can quickly restore from backup. If you’re using a database from a backup, learn how to restore it in Azure SQL Database.
3. Ensure Fabric Capacity: You need an existing Fabric capacity. If you don’t have one, start a Fabric trial.
4. Enable Mirroring in Fabric Tenant: Enable Mirroring in your Microsoft Fabric tenant. Also, ensure that the Fabric tenant setting “Allow service principals to use Power BI APIs” is enabled.
5. Networking Requirements:
   • Mirroring currently doesn’t support Azure SQL Database logical servers behind an Azure Virtual Network or private networking.
   • Update your Azure SQL logical server firewall rules to allow public network access.
   • Enable the “Allow Azure services” option to connect to your Azure SQL Database logical server in the Networking section of Azure SQL logical server in the Azure portal.

1. Enable System Assigned Managed Identity (SAMI):
   • Go to your Azure SQL logical server in the Azure portal.
   • Under Security in the resource menu, select Identity.
   • Turn on System assigned managed identity.
2. Create a Database Principal for Fabric:
   • You can do this using either a login and mapped database user or a contained database user.
   • For a login and mapped database user:
     • Create a SQL Authenticated login named fabric_login with a strong password.
     • Connect to your Azure SQL database and create a database user (fabric_user) connected to the login.
     • Grant necessary permissions to fabric_user.
   • For a contained database user:
     • Connect to your Azure SQL database and create a contained database user (fabric_user) with a password.
     • Grant necessary permissions to fabric_user.
     • CREATE LOGIN fabric_login WITH PASSWORD = ‘‘;
     • CREATE USER fabric_user FOR LOGIN fabric_login;
     • GRANT CONTROL TO fabric_user;
3. Create a Mirrored Azure SQL Database:
   • Open the Fabric portal and navigate to the Create hub.
   • Select Mirrored Azure SQL Database and enter the name of your Azure SQL database to be mirrored.
   • Connect to your Azure SQL Database by providing connection details like server name and database name.
4. Start Mirroring Process:
   • Configure mirroring options such as mirroring all data or selecting individual tables.
   • Start mirroring the database and monitor the replication status.
5. Monitor Fabric Mirroring:
   • Once mirroring is configured, you can monitor the replication status on the Mirroring Status page in the Fabric portal.

This process allows you to mirror your Azure SQL Database in Microsoft Fabric for analytics purposes, ensuring that your data is synchronized and available for analysis.

How to Secure data Microsoft Fabric mirrored databases from Azure SQL Database

• Enable the System Assigned Managed Identity (SAMI) for your Azure SQL logical server. This is needed for mirroring the Azure SQL Database to Fabric OneLake.
• Go to your logical SQL Server in the Azure portal and navigate to Security > Identity.
• Under System assigned managed identity, turn the Status to On.
• Fabric requires access to the Azure SQL database. To do this securely, create a dedicated database user with limited permissions, following the principle of least privilege.
• You can either create a login with a strong password and connected user, or a contained database user with a strong password.
• Grant the CONTROL permission to this user.

Limitations in Microsoft Fabric mirrored databases from Azure SQL Database 

Database level limitations

1. Supported Databases: Mirroring for Azure SQL Database is only supported on a primary database that allows writing.
2. Unsupported Conditions: Mirroring is not possible if the database has enabled Change Data Capture (CDC), Azure Synapse Link for SQL, or if it’s already mirrored in another Fabric workspace.
3. Transaction Handling: Active transactions keep the transaction log from truncating until they commit and the mirrored database catches up or aborts. Long transactions may fill the log excessively, requiring monitoring.
4. Resource Usage: During the initial snapshot, the source database might experience increased resource usage in terms of CPU and IOPS. Table updates or deletions can contribute to more log generation.
5. Replicator Engine Behavior: The replicator engine monitors tables for changes independently. If no updates occur, it backs off, increasing the duration exponentially, up to an hour. Regular polling resumes automatically after updated data is detected.
6. Maximum Tables: Fabric can mirror up to 500 tables. Tables exceeding this limit cannot be replicated.
7. Mirror All Data Option: If you choose to mirror all data, the first 500 tables, sorted alphabetically by schema and table name, will be mirrored. Tables beyond this limit won’t be mirrored.
8. Individual Table Selection: If you select individual tables instead, you’re limited to 500 tables.

Permissions in the source database

• Row-level security: Filtering data at the row level is not possible when you mirror an Azure SQL Database to Fabric OneLake.
• Object-level permissions: Permissions set for specific columns or objects are not transferred from the source SQL database to Fabric.
• Dynamic data masking settings: Data masking rules are not carried over from the source SQL database to Fabric.
• Mirroring configuration: To set up mirroring for an Azure SQL Database, the user connecting to the source database needs to have CONTROL or db_owner permissions granted.

Network and connectivity security

1. Enable Public Network Access and Azure Services: The source SQL server needs to allow connections from the internet and Azure services.
2. Use System Assigned Managed Identity (SAMI): Azure SQL server needs to have SAMI enabled. If disabled or removed, mirroring to Fabric OneLake will fail.
3. Avoid User Assigned Managed Identity (UAMI): UAMI is not supported for this process.
4. Keep Azure SQL Database SPN Permissions: Don’t remove permissions for the service principal name (SPN) of the Azure SQL database on the mirrored database item in Fabric.
5. Steps if SPN Permissions Are Removed Incorrectly:
   • Add the SPN as a user with Read and Write permissions.
   • This can be done by selecting the “…” option on the mirrored database item, then selecting Manage Permissions.
6. Cross-Tenant Data Mirroring Not Supported: Mirroring data between an Azure SQL Database and a Fabric workspace in different tenants is not supported.
7. Purview Information Protection Labels: Labels defined in Azure SQL Database aren’t mirrored to Fabric OneLake.

Table level

• To mirror a table, it must have a primary key rowstore clustered index.
• Tables with a primary key defined and used as nonclustered primary key can’t be mirrored.
• Mirroring isn’t possible if the primary key uses certain data types like hierarchyid, sql_variant, or timestamp.
• Clustered columnstore indexes aren’t supported for mirroring.
• If a table has Large Binary Object (LOB) columns larger than 1 MB, the data is truncated to 1 MB in Fabric OneLake.
• Source tables with features like temporal history, Always Encrypted, in-memory tables, graphs, or external tables can’t be mirrored.
• Certain table-level operations like partition switch/split/merge, altering the primary key, dropping or truncating the table, or renaming it aren’t allowed when mirroring is enabled.
• When there’s a DDL change, a complete data snapshot restarts for the changed table, and data is reseeded.

Column level

• If the original table has columns that are calculated based on other columns, these calculated columns can’t be copied to Fabric OneLake.
• Some types of data in the original table can’t be mirrored to Fabric OneLake. These include image, text, ntext, xml, rowversion (also known as timestamp), sql_variant, User Defined Types (UDT), geometry, and geography.
• Column names in the original table can’t have spaces or certain special characters like space, semicolon, curly braces, parentheses, newline, tab, or equal sign.
• Certain operations on individual columns in the original table are not supported when mirroring to Fabric OneLake. These operations include altering a column and renaming a column using the sp_rename function.

Warehouse limitations 

• The structure of the source schema isn’t copied exactly in the mirrored database.
• Instead, the source schema is simplified, and the name of the schema is added to the table names in the mirrored database.
• This means that the tables in the mirrored database have slightly different names compared to those in the source schema, making them easier to identify.

Mirrored item limitations 

• User needs to be a member of the Admin/Member role for the workspace to create SQL Database mirroring. 
• Stopping mirroring disables mirroring completely. 
• Starting mirroring reseeds all the tables, effectively starting from scratch. 

SQL analytics endpoint limitations 

• The SQL analytics endpoint is like the Lakehouse SQL analytics endpoint.
• It provides the same read-only experience for users.

Point to be noted:

• Stop Replication: When you choose to stop replication, the files in OneLake stay the same, but the process of incremental replication halts. You can start it again whenever you need by selecting “Start Replication.”
• Reasons to Stop Replication: You might stop replication to reset its state, after making changes to the source database, or as part of troubleshooting.
• Troubleshooting Steps:
  • Errors with Mirrored Database Creation: Make sure mirroring is enabled for your workspace or tenant. If errors persist, contact support.
  • Connection Issues with Source Database: Double-check connection details like server name, database name, username, and password. Ensure the server isn’t behind a firewall and that the necessary ports are open.
  • Views Not Replicated: Currently, only regular tables are supported for replication, not views.
  • Tables Not Replicated: Check the monitoring status and ensure the tables are listed. Use the Configure Replication button to verify table presence and any alerts.
  • Missing Columns in Destination Table: Look for alerts next to table details in the Configure Replication section.
  • Truncated Data in Columns: Fabric warehouse supports VARCHAR(8000) but not VARCHAR(max).
  • Data Not Replicating: Check the Monitoring page for the last successful replication date.
  • Unable to Change Source Database: Changing the source database isn’t supported; you need to create a new mirrored database instead.