{"id":1395,"date":"2024-04-27T13:43:04","date_gmt":"2024-04-27T11:43:04","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1395"},"modified":"2024-04-27T13:43:04","modified_gmt":"2024-04-27T11:43:04","slug":"a-first-look-at-database-mirroring-in-power-bi-or-in-fabric","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1395","title":{"rendered":"A first look at database mirroring in Power BI or in Fabric"},"content":{"rendered":"\n

The ability to mirror data is not new if you have dealt with SQL Server. The ability to mirror databases was added a long time ago. Setting this up was much more difficult, and it also required a costly enterprise license. Now there is a preview function for mirroring databases in Power BI or Fabric. The data is stored in OneLake and can be used from there. Of course, the data is provided via an SQL endpoint and can also be read and analyzed in Power BI. This function is very useful if the database is constantly being written and read. This means that users are not disturbed by the person analyzing the data when editing it. The creation of mirrored databases is currently limited to Azure SQL, Cosmos and Snowflake databases. However, I think that this will expand in the next few weeks or months. In this entry I would like to show how the database is mirrored and what else needs to be taken into account. First of all, I created an Azure database that pauses when not in use. Serverless, so the database costs significantly less money to use. However, if the database is mirrored, the database is constantly accessed to determine whether data has changed. This means that the database is always in active mode and is therefore also cost-intensive.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

To achieve this feature, Fabric must be enabled.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

However, in order for the function to be used, system-assigned managed identity must be activated on the database. It does not matter whether SQL-based authentication is used.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Further information can be found here:<\/p>\n\n\n\n

https:\/\/community.fabric.microsoft.com\/t5\/General-Discussion\/Mirroring-in-Fabric-Managed-Identity-error\/td-p\/3847842 After the mirroring name has been assigned, a connection to the database is established:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

For this demonstration, I also use SQL-based authentication. The data for this can be read from the Azure platform.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

After confirming this window, you can select either individual tables or all of them.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

After confirming, you have to wait a moment for the mirroring to start.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

However, it should be noted that some tables provide information about which columns cannot be mirrored. The information can be found behind the symbols.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

After confirming this window, the mirroring starts:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

the objects are created with the workspace where you were originally located.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Even though a semantic model has been created here, it is not usable.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

In the properties of the analytical database, the connection point with the SQL database can be copied.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

A connection can also be established with this database, and the mirrored objects can be found there and queried. It is not possible to change these objects by entering new data or changing the structure.<\/strong><\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

A connection to the SQL database can also be established from the Power BI Desktop side:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

When you query the table, you can see the data it contains from the source database.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

If a record is deleted or added in the source database, the mirroring can be monitored. In this example, I have deleted a row. However, the rows are not displayed correctly.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

The data can also be processed with the Lakehouse in Fabric, as it is located in OneLake<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"

The ability to mirror data is not new if you have dealt with SQL Server. The ability to mirror databases was added a long time ago. Setting this up was much more difficult, and it also required a costly enterprise … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1395"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1395"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1395\/revisions"}],"predecessor-version":[{"id":1414,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1395\/revisions\/1414"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1395"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}