Creating a Mirrored Azure SQL Database in Fabric
Creating a Mirrored Azure SQL Database in Fabric
This week they announced Announcing the Public Preview of Database Mirroring in Microsoft Fabric | Microsoft Power BI Blog | Microsoft Power BI
I decided to see how easy it was to create a mirrored database in Fabric and below are my findings (PS it is AMAZING)
I did follow the tutorial, to help me get it up and running here: Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Database (Preview) – Microsoft Fabric | Microsoft Learn
What I did find is that there were some extra things I had to do to get it fully working, which I will explain below.
If you did not know Microsoft is offering a free Azure SQL Database (Try for free (preview) – Azure SQL Database | Microsoft Learn), which I had already setup. This is a great option to try out Azure SQL for free.
The other great news is that the free version of Azure SQL is compatible to be used with Fabric mirroring.
Prerequisites
By default, in the Power BI/Fabric Admin Portal mirroring is disabled.
I went into the Admin Portal settings and enabled the setting as shown below.
There are also some other networking requirements that might need to be configured based on your requirements.
Next, I had to enable the System assigned managed identity for the SQL Server as shown below.
Next, I created the login and mapped the user to my database. Here I followed the exact steps in the tutorial I am pasting below.
Creating the Mirrored Azure SQL database in Fabric.
Finally, I was at the point where I could now create the mirrored Azure SQL Database in Fabric.
The first step is to navigate to your Fabric enabled App Workspace.
In my example I navigated to “Fabric – FourMoo”
I then clicked on the Create button.
I then needed to scroll down and click on See All
I then scrolled down to the Data Warehouse section and clicked on Mirrored Azure SQL Database as shown below.
I then gave my Mirror Azure SQL Database a name and clicked on Create.
Next, it brought up the window asking for which database connection to get started. I clicked on Azure SQL Database because I did not have an existing connection.
I then put in the details below for my Azure SQL Server, database name and for the Authentication kind I put basic because I had previously configured the username and password to connect to the Azure SQL Database.
I then clicked on Create, which created the connection.
On the next screen I left the defaults and clicked Mirror database. I assumed that it would just start mirroring (This is where my assumption was wrong)
Once this was setup I clicked on Monitor Replication.
When I had a look here, I could not see anything being replicated as shown below.
I waited a few minutes and still NOTHING was showing. I even went back to my database to make sure that there was data in the table.
I also went back into my Fabric App Workspace, and I could see the Mirrored database as shown below.
I then went back into the Mirrored database and clicked on Configure replication.
This time I toggled the switch to “Mirror all data” to off.
Now I can see why there was an error, almost all the tables were showing up with a red X. When I hovered over the red x I got an error saying, “This table can’t be mirrored to Fabric because it doesn’t have a primary key”.
This was something I had not read in the documentation that the tables needed to have a primary key.
Fortunately I did have a table which had a primary key. I then enabled this table as shown below.
I then clicked on Apply Changes.
Patiently I waited a few minutes, then clicked on the Monitor Replication
I could then see the data being mirrored.
I then tested inserting a row into my table and within a few minutes the data was in the mirrored database in Fabric.
Summary
Whilst I had a small hiccup it honestly took me all of 1 hour to get the mirrored Azure SQL Database up and running in Fabric. This is really amazing. The Fabric team has abstracted all the complexity away and made it super easy to use.
Now I can use this mirrored database data in any of my other fabric workloads.
As always, I hope you found this useful and if you have any questions, please feel free to leave it in the comments section.
Have a great weekend!
Creating a Mirrored Azure SQL Database in Fabric
This week they announced Announcing the Public Preview of Database Mirroring in Microsoft Fabric | Microsoft Power BI Blog | Microsoft Power BI
I decided to see how easy it was to create a mirrored database in Fabric and below are my findings (PS it is AMAZING)
I did follow the tutorial, to help me get it up and running here: Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Database (Preview) – Microsoft Fabric | Microsoft Learn
What I did find is that there were some extra things I had to do to get it fully working, which I will explain below.
If you did not know Microsoft is offering a free Azure SQL Database (Try for free (preview) – Azure SQL Database | Microsoft Learn), which I had already setup. This is a great option to try out Azure SQL for free.
The other great news is that the free version of Azure SQL is compatible to be used with Fabric mirroring.
Prerequisites
By default, in the Power BI/Fabric Admin Portal mirroring is disabled.
I went into the Admin Portal settings and enabled the setting as shown below.
There are also some other networking requirements that might need to be configured based on your requirements.
Next, I had to enable the System assigned managed identity for the SQL Server as shown below.
Next, I created the login and mapped the user to my database. Here I followed the exact steps in the tutorial I am pasting below.
Creating the Mirrored Azure SQL database in Fabric.
Finally, I was at the point where I could now create the mirrored Azure SQL Database in Fabric.
The first step is to navigate to your Fabric enabled App Workspace.
In my example I navigated to “Fabric – FourMoo”
I then clicked on the Create button.
I then needed to scroll down and click on See All
I then scrolled down to the Data Warehouse section and clicked on Mirrored Azure SQL Database as shown below.
I then gave my Mirror Azure SQL Database a name and clicked on Create.
Next, it brought up the window asking for which database connection to get started. I clicked on Azure SQL Database because I did not have an existing connection.
I then put in the details below for my Azure SQL Server, database name and for the Authentication kind I put basic because I had previously configured the username and password to connect to the Azure SQL Database.
I then clicked on Create, which created the connection.
On the next screen I left the defaults and clicked Mirror database. I assumed that it would just start mirroring (This is where my assumption was wrong)
Once this was setup I clicked on Monitor Replication.
When I had a look here, I could not see anything being replicated as shown below.
I waited a few minutes and still NOTHING was showing. I even went back to my database to make sure that there was data in the table.
I also went back into my Fabric App Workspace, and I could see the Mirrored database as shown below.
I then went back into the Mirrored database and clicked on Configure replication.
This time I toggled the switch to “Mirror all data” to off.
Now I can see why there was an error, almost all the tables were showing up with a red X. When I hovered over the red x I got an error saying, “This table can’t be mirrored to Fabric because it doesn’t have a primary key”.
This was something I had not read in the documentation that the tables needed to have a primary key.
Fortunately I did have a table which had a primary key. I then enabled this table as shown below.
I then clicked on Apply Changes.
Patiently I waited a few minutes, then clicked on the Monitor Replication
I could then see the data being mirrored.
I then tested inserting a row into my table and within a few minutes the data was in the mirrored database in Fabric.
Summary
Whilst I had a small hiccup it honestly took me all of 1 hour to get the mirrored Azure SQL Database up and running in Fabric. This is really amazing. The Fabric team has abstracted all the complexity away and made it super easy to use.
Now I can use this mirrored database data in any of my other fabric workloads.
As always, I hope you found this useful and if you have any questions, please feel free to leave it in the comments section.
Have a great weekend!
Creating a Mirrored Azure SQL Database in Fabric
This week they announced Announcing the Public Preview of Database Mirroring in Microsoft Fabric | Microsoft Power BI Blog | Microsoft Power BI
I decided to see how easy it was to create a mirrored database in Fabric and below are my findings (PS it is AMAZING)
I did follow the tutorial, to help me get it up and running here: Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Database (Preview) – Microsoft Fabric | Microsoft Learn
What I did find is that there were some extra things I had to do to get it fully working, which I will explain below.
If you did not know Microsoft is offering a free Azure SQL Database (Try for free (preview) – Azure SQL Database | Microsoft Learn), which I had already setup. This is a great option to try out Azure SQL for free.
The other great news is that the free version of Azure SQL is compatible to be used with Fabric mirroring.
Prerequisites
By default, in the Power BI/Fabric Admin Portal mirroring is disabled.
I went into the Admin Portal settings and enabled the setting as shown below.
There are also some other networking requirements that might need to be configured based on your requirements.
Next, I had to enable the System assigned managed identity for the SQL Server as shown below.
Next, I created the login and mapped the user to my database. Here I followed the exact steps in the tutorial I am pasting below.
Creating the Mirrored Azure SQL database in Fabric.
Finally, I was at the point where I could now create the mirrored Azure SQL Database in Fabric.
The first step is to navigate to your Fabric enabled App Workspace.
In my example I navigated to “Fabric – FourMoo”
I then clicked on the Create button.
I then needed to scroll down and click on See All
I then scrolled down to the Data Warehouse section and clicked on Mirrored Azure SQL Database as shown below.
I then gave my Mirror Azure SQL Database a name and clicked on Create.
Next, it brought up the window asking for which database connection to get started. I clicked on Azure SQL Database because I did not have an existing connection.
I then put in the details below for my Azure SQL Server, database name and for the Authentication kind I put basic because I had previously configured the username and password to connect to the Azure SQL Database.
I then clicked on Create, which created the connection.
On the next screen I left the defaults and clicked Mirror database. I assumed that it would just start mirroring (This is where my assumption was wrong)
Once this was setup I clicked on Monitor Replication.
When I had a look here, I could not see anything being replicated as shown below.
I waited a few minutes and still NOTHING was showing. I even went back to my database to make sure that there was data in the table.
I also went back into my Fabric App Workspace, and I could see the Mirrored database as shown below.
I then went back into the Mirrored database and clicked on Configure replication.
This time I toggled the switch to “Mirror all data” to off.
Now I can see why there was an error, almost all the tables were showing up with a red X. When I hovered over the red x I got an error saying, “This table can’t be mirrored to Fabric because it doesn’t have a primary key”.
This was something I had not read in the documentation that the tables needed to have a primary key.
Fortunately I did have a table which had a primary key. I then enabled this table as shown below.
I then clicked on Apply Changes.
Patiently I waited a few minutes, then clicked on the Monitor Replication
I could then see the data being mirrored.
I then tested inserting a row into my table and within a few minutes the data was in the mirrored database in Fabric.
Summary
Whilst I had a small hiccup it honestly took me all of 1 hour to get the mirrored Azure SQL Database up and running in Fabric. This is really amazing. The Fabric team has abstracted all the complexity away and made it super easy to use.
Now I can use this mirrored database data in any of my other fabric workloads.
As always, I hope you found this useful and if you have any questions, please feel free to leave it in the comments section.
Have a great weekend!
Creating a Mirrored Azure SQL Database in Fabric
This week they announced Announcing the Public Preview of Database Mirroring in Microsoft Fabric | Microsoft Power BI Blog | Microsoft Power BI
I decided to see how easy it was to create a mirrored database in Fabric and below are my findings (PS it is AMAZING)
I did follow the tutorial, to help me get it up and running here: Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Database (Preview) – Microsoft Fabric | Microsoft Learn
What I did find is that there were some extra things I had to do to get it fully working, which I will explain below.
If you did not know Microsoft is offering a free Azure SQL Database (Try for free (preview) – Azure SQL Database | Microsoft Learn), which I had already setup. This is a great option to try out Azure SQL for free.
The other great news is that the free version of Azure SQL is compatible to be used with Fabric mirroring.
Prerequisites
By default, in the Power BI/Fabric Admin Portal mirroring is disabled.
I went into the Admin Portal settings and enabled the setting as shown below.
There are also some other networking requirements that might need to be configured based on your requirements.
Next, I had to enable the System assigned managed identity for the SQL Server as shown below.
Next, I created the login and mapped the user to my database. Here I followed the exact steps in the tutorial I am pasting below.
Creating the Mirrored Azure SQL database in Fabric.
Finally, I was at the point where I could now create the mirrored Azure SQL Database in Fabric.
The first step is to navigate to your Fabric enabled App Workspace.
In my example I navigated to “Fabric – FourMoo”
I then clicked on the Create button.
I then needed to scroll down and click on See All
I then scrolled down to the Data Warehouse section and clicked on Mirrored Azure SQL Database as shown below.
I then gave my Mirror Azure SQL Database a name and clicked on Create.
Next, it brought up the window asking for which database connection to get started. I clicked on Azure SQL Database because I did not have an existing connection.
I then put in the details below for my Azure SQL Server, database name and for the Authentication kind I put basic because I had previously configured the username and password to connect to the Azure SQL Database.
I then clicked on Create, which created the connection.
On the next screen I left the defaults and clicked Mirror database. I assumed that it would just start mirroring (This is where my assumption was wrong)
Once this was setup I clicked on Monitor Replication.
When I had a look here, I could not see anything being replicated as shown below.
I waited a few minutes and still NOTHING was showing. I even went back to my database to make sure that there was data in the table.
I also went back into my Fabric App Workspace, and I could see the Mirrored database as shown below.
I then went back into the Mirrored database and clicked on Configure replication.
This time I toggled the switch to “Mirror all data” to off.
Now I can see why there was an error, almost all the tables were showing up with a red X. When I hovered over the red x I got an error saying, “This table can’t be mirrored to Fabric because it doesn’t have a primary key”.
This was something I had not read in the documentation that the tables needed to have a primary key.
Fortunately I did have a table which had a primary key. I then enabled this table as shown below.
I then clicked on Apply Changes.
Patiently I waited a few minutes, then clicked on the Monitor Replication
I could then see the data being mirrored.
I then tested inserting a row into my table and within a few minutes the data was in the mirrored database in Fabric.
Summary
Whilst I had a small hiccup it honestly took me all of 1 hour to get the mirrored Azure SQL Database up and running in Fabric. This is really amazing. The Fabric team has abstracted all the complexity away and made it super easy to use.
Now I can use this mirrored database data in any of my other fabric workloads.
As always, I hope you found this useful and if you have any questions, please feel free to leave it in the comments section.
Have a great weekend!
Creating a Mirrored Azure SQL Database in Fabric
This week they announced Announcing the Public Preview of Database Mirroring in Microsoft Fabric | Microsoft Power BI Blog | Microsoft Power BI
I decided to see how easy it was to create a mirrored database in Fabric and below are my findings (PS it is AMAZING)
I did follow the tutorial, to help me get it up and running here: Tutorial: Configure Microsoft Fabric mirrored databases from Azure SQL Database (Preview) – Microsoft Fabric | Microsoft Learn
What I did find is that there were some extra things I had to do to get it fully working, which I will explain below.
If you did not know Microsoft is offering a free Azure SQL Database (Try for free (preview) – Azure SQL Database | Microsoft Learn), which I had already setup. This is a great option to try out Azure SQL for free.
The other great news is that the free version of Azure SQL is compatible to be used with Fabric mirroring.
Prerequisites
By default, in the Power BI/Fabric Admin Portal mirroring is disabled.
I went into the Admin Portal settings and enabled the setting as shown below.
There are also some other networking requirements that might need to be configured based on your requirements.
Next, I had to enable the System assigned managed identity for the SQL Server as shown below.
Next, I created the login and mapped the user to my database. Here I followed the exact steps in the tutorial I am pasting below.
Creating the Mirrored Azure SQL database in Fabric.
Finally, I was at the point where I could now create the mirrored Azure SQL Database in Fabric.
The first step is to navigate to your Fabric enabled App Workspace.
In my example I navigated to “Fabric – FourMoo”
I then clicked on the Create button.
I then needed to scroll down and click on See All
I then scrolled down to the Data Warehouse section and clicked on Mirrored Azure SQL Database as shown below.
I then gave my Mirror Azure SQL Database a name and clicked on Create.
Next, it brought up the window asking for which database connection to get started. I clicked on Azure SQL Database because I did not have an existing connection.
I then put in the details below for my Azure SQL Server, database name and for the Authentication kind I put basic because I had previously configured the username and password to connect to the Azure SQL Database.
I then clicked on Create, which created the connection.
On the next screen I left the defaults and clicked Mirror database. I assumed that it would just start mirroring (This is where my assumption was wrong)
Once this was setup I clicked on Monitor Replication.
When I had a look here, I could not see anything being replicated as shown below.
I waited a few minutes and still NOTHING was showing. I even went back to my database to make sure that there was data in the table.
I also went back into my Fabric App Workspace, and I could see the Mirrored database as shown below.
I then went back into the Mirrored database and clicked on Configure replication.
This time I toggled the switch to “Mirror all data” to off.
Now I can see why there was an error, almost all the tables were showing up with a red X. When I hovered over the red x I got an error saying, “This table can’t be mirrored to Fabric because it doesn’t have a primary key”.
This was something I had not read in the documentation that the tables needed to have a primary key.
Fortunately I did have a table which had a primary key. I then enabled this table as shown below.
I then clicked on Apply Changes.
Patiently I waited a few minutes, then clicked on the Monitor Replication
I could then see the data being mirrored.
I then tested inserting a row into my table and within a few minutes the data was in the mirrored database in Fabric.
Summary
Whilst I had a small hiccup it honestly took me all of 1 hour to get the mirrored Azure SQL Database up and running in Fabric. This is really amazing. The Fabric team has abstracted all the complexity away and made it super easy to use.
Now I can use this mirrored database data in any of my other fabric workloads.
As always, I hope you found this useful and if you have any questions, please feel free to leave it in the comments section.
Have a great weekend!
[…] Gilbert Quevuavilliers holds up a mirror: […]
Hi Gilbert,
RE: What I did find is that there were some extra things I had to do to get it fully working…,
Yes indeed. I also explored new Microsoft Fabric Mirroring features (i.e. including Azure SQL Database Mirroring
in this case), and came to the very same conclusions.
Most documents I found mainly focused mainly on the second part, that is Mirroring creation part performed on Microsoft Fabric, omitting prior many crucial configuration steps on Azure Portal (SAMI, Firewall rules) as well login and mapping user
to a database (master) scripts, which I coded and tested on Azure Data Studio.
This may sound a bit too subtle to some, but let’s face it, without proper prior configurations,
Mirroring just won’t work and consequently generate unwanted frustrations. Yeah I know I’ve been there…
Everything is now running pretty well and I’m happy with results.
Mirroring does offer some interesting features options to explore. That is, if we first take some time to master
how to configure them properly. Thanks for sharing your experience and keep on your good work.
Take Care
Hi Denys,
Thanks for the comment and I agree that sometimes it can be a challenge but once it is working it is so much better!
Did you note any changes to the Azure SQL Database – either the table you replicated, or to the setup/configuration of the Azure SQL instance? I am wondering if the mirroring turns on SQL Server change tracking? does it add a timestamp or version column?
Hi Jon, thanks for the comment.
I did not see any change to the database with the Change tracking. I think it uses something similar to get it working!