Help: Can’t make any changes to Azure SQL after configuring Mirroring to Microsoft Fabric
Over the past few weeks, I have been doing some experimenting with Azure SQL mirroring to Microsoft Fabric. In the process, I ran into a couple of issues and challenges. In this data recipe, I will be going through one of my challenges when I got Azure SQL mirroring to Microsoft Fabric setup and running.
At first, everything seemed to be working as expected, and the integration felt very smooth. At that point, I continued to develop my test database in Azure SQL to learn a bit more about mirroring. I made a couple of minor changes to my test database and tried to publish them from my SQL Database Project.
Problem statement
During the development I mentioned earlier, I made a change to one of the replicated objects, at least in the SQL Database Project. The moment I tried to publish my changes to my Azure SQL demo database, I ran into an issue.
As you can see in the screenshot above, it seems that I can’t deploy changes to objects that are mirrored. In the following section, I will guide you on how to overcome this issue.
Solution
To overcome this challenge, we need to make a change to the way we deploy our changes to Azure SQL. When you deploy from a SQL Database Project, from Azure DevOps, with the built-in tasks, you’re deploying your changes by using SqlPackage behind the scenes. Over the past years, I have been spending a lot of time working with SqlPackage and learned a lot of use cases and capabilities.
Because of this, I had an idea to look at the SqlPackage deployment properties again to see if the solution was hiding in there. If you don’t know SqlPackage, I can advise you to read the following article to get familiar with SqlPackage: Download and Install SqlPackage – SQL Server | Microsoft Learn
After going through the documentation again, through the advanced publish settings, I found an interesting setting that might solve the issue. (reference to the documentation page: SqlPackage Publish – SQL Server | Microsoft Learn)
In my case, I was interested in the “DoNotAlterReplicatedObjects” property, which defaults to “True” when not set.
To test my idea, I changed the publishing profile in my SQL Database Project through Visual Studio Code in the following way:
After making the change, I retried deploying to my Azure SQL database.
As you can see in the screenshot above, the deploy happened successfully after making the change. At this point, my biggest question was, “What happened to the mirroring to Microsoft Fabric…” So I checked the mirroring process as well. At first, I didn’t see any change incoming, which made me slightly nervous.
Once, someone said to me, patient will be your biggest friend and after a couple of minutes, I checked again, and my most recent changes became available in the Azure SQL database mirror in Microsoft Fabric.
Bon appétit!
