{"id":102097,"date":"2024-04-12T02:26:00","date_gmt":"2024-04-12T02:26:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102097"},"modified":"2024-09-03T20:04:46","modified_gmt":"2024-09-03T20:04:46","slug":"fabric-and-azure-sql-in-a-few-clicks-mirroring-a-database","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/fabric-and-azure-sql-in-a-few-clicks-mirroring-a-database\/","title":{"rendered":"Fabric and Azure SQL in a few clicks: Mirroring a Database"},"content":{"rendered":"

Finally, mirroring is available for Fabric<\/a>! You can mirror an Azure SQL<\/strong> to Fabric<\/strong>. It works for CosmoDB<\/strong> and Snowflake<\/strong> as well, but in this article, I will focus on Azure SQL. It is 100%, no, but it is definitely a feature that is really great even now.<\/p>\n

Before getting into a step-by-step of the process, let\u2019s understand the basics.<\/p>\n

Why Mirror a Database<\/h2>\n

You need to read data from production to build a single source of truth. If you create pipelines reading directly from production, you will create additional load over the production environment. The mirror allows you to do much of the production reporting from the mirror, leaving the production environment to serve other users. Keep in mind, production report, but not analytics report.<\/p>\n

Mirroring a production database to Fabric<\/strong> is one method to ensure the load over production will be as low as possible and the data will be transferred fabric to complete the transformations from this point.<\/p>\n

Only this? What about avoiding pipeline creation? Not really, you still need to create pipelines, as I will explain ahead.<\/p>\n

Where your data will be stored<\/h2>\n

If I tell you OneLake<\/strong>, you might want to slap me. Of course it\u2019s on OneLake<\/strong>, you know that! But where? A Data Warehouse<\/strong>? A Lakehouse<\/strong>? Where?<\/p>\n

Microsoft created a special type of object for this: The Mirrored Database<\/strong>. In the same way as the other data objects, this object also has two associated objects: A SQL Endpoint<\/strong> and a Semantic Model<\/strong>.<\/p>\n

\"A<\/p>\n

We can use the SQL Endpoint<\/strong> to query the mirrored data, in the same way we would do with a Data Warehouse<\/strong> or Lakehouse<\/strong>.<\/p>\n

Of course we can use the Semantic Model<\/strong> to build reports, but let\u2019s talk later if this is a good idea or not.<\/p>\n

What to do and not to do with a mirrored database<\/h2>\n

Mirroring a database is like creating a landing zone in your Data Intelligence environment. While it is similar, it is a bit different from that: a landing zone keeps the data ingested, the mirrored database doesn\u2019t. When a record is updated or deleted in production the same will happen with the mirror.<\/p>\n

The mirrored model contains all the common mistakes of production modelling which makes it not good for analytical reporting. Usually, it will not be good to hold history and may not be representing the facts in a business point of view.<\/p>\n

What you should NOT do: Use the mirrored model directly to build analytical reports and dashboards<\/em><\/strong>. The quality of this data would be low, the amount of history would be questionable, you would not have a single source of truth.<\/p>\n

However, keep in mind I wrote analytical reports<\/em><\/strong>. Production reports, which may be paginated reports, can be built over the Mirrored Database, because it has the same data as production. In this way, you remove from production the production reporting workload. But only production reporting, not analytical reporting.<\/p>\n

\"A<\/p>\n

Image created using Microsoft Designer Image Creator<\/p>\n

For the analytical reporting, you need to transform the mirrored model in a data intelligence model, prepared to hold the complete history of the data and provide it according to the business meaning.<\/em><\/strong><\/p>\n

In this way, the mirroring feature doesn\u2019t save you from building pipelines to transform the data, only move the entire workload of these pipeline to inside Fabric and OneLake<\/strong>.<\/p>\n

If you are concerned about duplicating the data in the OneLake<\/strong>, it\u2019s not really a duplication due to the needed transformations you will make. You can use shortcuts to handle the few exceptions which may not need transformations. Yes, the mirrored database supports shortcuts from lakehouses.<\/p>\n

Requirements For Mirroring<\/h2>\n

This is the list of what works and doesn\u2019t work with mirroring in Fabric. The list which you will skim and not really understand until, exactly like me, you get error messages about all these problems, one by one.<\/p>\n