Azure SQL mirroring to Fabric fails with Error occured while interacting with landing zone

[lobitconsulting]

Hi,

I have a strange issue when mirroring an Azure SQL  database to Fabric.

-When doing GUI point-and-click, the mirroring starts within minutes. Status = Running, Rows Replicated = Correct number

-When doing (what I think) is same with API, using same connection, creating mirroring and start, everything looks ok,  Status = Running, Rows Replicated = 0.

When running
 SELECT * FROM sys.dm_change_feed_errors;
"Error occured while interacting with landing zone"
additional:

-Im using ServicePrincipal for the connection, using same connection object in both cases.

-Using Fabic notebook to initiate the API calls, authenticated as myself (admin).
Payload

 {

    "properties": {

        "source": {

            "type": "AzureSqlDatabase",

            "typeProperties": {

                "connection": xxxxxxxxxxxxxx,

                 "database": xxxxxxxxxxxxxxxx

            }

        },

        "target": {

            "type": "MountedRelationalDatabase",

            "typeProperties": {

                "defaultSchema": "dbo",

                "format": "Delta"

            }

        }

    }

}

The issue is reproducable. Whenever point-and-click it works. whenever API it fails.

[lobitconsulting · Accepted solution]

Update to this,

I found a reddit post with the same issue,
https://www.reddit.com/r/MicrosoftFabric/comments/1o696yw/the_issue_of_creating_mirrored_databases_u...
Its pointed out that the SAMI needs permission on the mirrored database item.

Also when reading the fine prints in the documentation its stated there as well.

So its really down to me not grasping the details.

Once I added the SAMI to the mirroring object, API works fine for setting up the mirroring.

Thanks @spaceman127 for your time

[spaceman127]

Hi @lobitconsulting ,

Great!
I tested it tonight because the problem kept bothering me, and I encountered exactly what you described.
The permission was missing.

I was able to reproduce it.

Perfect, your problem is solved.

And I'm happy to help when I can.

And feel free to mark the solution and leave some kudos.

Best regards 

[lobitconsulting]

hi @spaceman127  ,

Tested adding your config, but the result is same as before.

I have a large number of databases, so GUI approach is no option.

Any other suggestions?

[spaceman127]

Hi @lobitconsulting ,

all right.

I will test it and then let you know if I can reproduce the error. I have also already read up on the API.
If everything goes well, I will get to it in the next few days.

Could you perhaps share your complete code here?

Best regards

[lobitconsulting]

Hi @spaceman127 
Thanks for your time,

I removed some debug code to make it more compact, but in essence this is my pyspark notebook.
I did not include the connection object, this was created in GUI and I refer to the same connection object both in my GUI test and API test.
When doing the calls I authenticate as myself. The connection object is using service principal towards the database.

import requests, json, base64, uuid
 
token = mssparkutils.credentials.getToken("pbi")

workspaceId = "zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz"
connectionId = "xxxxxxxxxxxxxxxxxxxxxxxxx"
DATABASE = "mirrortesting"

# ====== HEADERS ======
headers = {
    "Authorization": f"Bearer {token}",
    "Content-Type": "application/json"
}

# ====== Create Mirroring Variables ======
 
urlCreateMirror = "https://api.fabric.microsoft.com/v1/workspaces/" + workspaceId + "/mirroredDatabases"

payloadUnencoded = {
    "properties": {
        "source": {
            "type": "AzureSqlDatabase",
            "typeProperties": {
                "connection": connectionId,
                 "database": DATABASE
            }
        },
        "target": {
            "type": "MountedRelationalDatabase",
            "typeProperties": {
                "defaultSchema": "dbo",
                "format": "Delta",
                 "landingZone": {
                 "type": "Managed"
                 }
            }
        }
    }
}
 
b64 = base64.b64encode(json.dumps(payloadUnencoded).encode('utf-8')).decode('utf-8')
display_name = f"Mirror Instance {DATABASE}"
 
body = {
    "displayName": display_name,
    "description": "Mirror created in notebook",
    "definition": {
        "parts": [
            {
                "path": "mirroring.json",
                "payload": b64,
                "payloadType": "InlineBase64"
            }
        ]
    }
}


# ====== Create Mirroring API CALL =====
resp = requests.post(urlCreateMirror, headers=headers, data=json.dumps(body), timeout=120)
mirroredDbId = resp.json()["id"]
time.sleep(20) #wait for Initialized



# ====== Start Mirroring ======
urlStart = "https://api.fabric.microsoft.com/v1/workspaces/" + workspaceId + "/mirroredDatabases/" + mirroredDbId + "/startMirroring"
resp = requests.post(urlStart, headers=headers, data=json.dumps({}), timeout=120)
time.sleep(20) #wait for 'Running'



# ====== Check status ======
urlStatus = "https://api.fabric.microsoft.com/v1/workspaces/" + workspaceId + "/mirroredDatabases/" + mirroredDbId + "/getMirroringStatus"
respStatus = requests.post(urlStatus, headers=headers, data=json.dumps({}), timeout=120)
print(json.dumps(respStatus.json(), indent=2))

[spaceman127]

Hi @lobitconsulting,

I haven't tested it yet, but I think I've read somewhere that you have to specify the landing zone in the API call.

Here's an example. No guarantees, but you can give it a try.

Let us know if it works.

target": {

      "type": "MountedRelationalDatabase",

      "typeProperties": {

        "defaultSchema": "dbo",

        "format": "Delta",

        "landingZone": {

          "type": "Managed"

Best regards