I am using the REST API details provided in the MS docs site: https://learn.microsoft.com/en-us/fabric/mirroring/mirrored-database-rest-api
to create a mirror database based on an Azure SQL database. Everything works including, creation of mirror db, source connection establishment except "table registration". The table doesn't show up in the Mirror DB.
When I try with the Fabric UI using same Azure SQL DB connection in Fabric, it works perfectly.
Access and Permissions:
1. Using the same Tenant for both Fabric and Azure logins.
2. The Azure SQl DB has SAMI enabled.
3. I have Admin permissions in the Fabric Workspace too.
This is my Code:
# === mirroring.json content as dict ===
mirroring_definition = {
"properties": {
"source": {
"type": "AzureSqlDatabase",
"typeProperties": {
"connection": connection_id,
# "database": database_name
"landingZone": {
"type": "MountedRelationalDatabase",
"typeProperties": {}
}
}
},
"target": {
"type": "MountedRelationalDatabase",
"typeProperties": {
"defaultSchema": "dbo",
"format": "Delta",
"retentionInDays": 1
}
},
"mountedTables": [
{
"source": {
"type": "AzureSqlTable",
"typeProperties": {
"schemaName": schema_name,
"tableName": table_name
}
},
"target": {
"type": "LakehouseTable",
"typeProperties": {
"name": "Customer"
}
}
}
]
}
}
print("Final mirroring definition:")
print(json.dumps(mirroring_definition, indent=2))
# === Encode mirroring definition ===
mirroring_payload = base64.b64encode(json.dumps(mirroring_definition).encode('utf-8')).decode('utf-8')
# === Construct payload for the API ===
mirror_payload = {
"displayName": mirror_display_name,
"type": "MirroredDatabase",
"description": "Create Mirrored Database item with inline JSON definition",
"definition": {
"parts": [
{
"path": "mirroring.json",
"payload": mirroring_payload,
"payloadType": "InlineBase64"
}
]
}
}
# === Authorization header using managed identity ===
headers = {
"Authorization": "Bearer " + mssparkutils.credentials.getToken("pbi"),
"Content-Type": "application/json"
}
# === 1. Create the mirrored DB ===
mirror_url = f"https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}/items"
create_response = requests.post(mirror_url, headers=headers, json=mirror_payload)
print("Create Response Status Code:", create_response.status_code)
try:
create_result = create_response.json()
print("Create Response JSON:", json.dumps(create_result, indent=2))
except:
print("Create Response Text:", create_response.text)
raise
# === 2. Extract mirroredDatabaseId (itemId) ===
mirrored_db_id = create_result.get("id")
if not mirrored_db_id:
raise Exception("No mirrored DB ID returned.")
print("Mirrored DB ID:", mirrored_db_id)
# === 3. Wait for DB to be ready ===
status_url = f"https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}/mirroredDatabases/{mirrored_db_id}/get..."
max_retries = 12 # Wait up to 60 seconds (12 * 5s)
wait_seconds = 5
current_status = "Initializing"
for attempt in range(max_retries):
time.sleep(wait_seconds)
status_response = requests.post(status_url, headers=headers)
status_json = status_response.json()
current_status = status_json.get("status", "Unknown")
print(f"Attempt {attempt + 1}: Mirroring status = {current_status}")
if current_status not in ["Initializing", "Unknown"]:
break
else:
raise TimeoutError("Mirrored DB is still initializing after maximum wait time.")
# === 4. Start mirroring ===
start_url = f"https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}/mirroredDatabases/{mirrored_db_id}/sta..."
start_response = requests.post(start_url, headers=headers)
print("Start Mirroring Status Code:", start_response.status_code)
table_status = f"https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}/mirroredDatabases/{mirrored_db_id}/get..."
table_status_response = requests.post(table_status, headers=headers)
print("Mirror table Status:", table_status_response.status_code)
try:
print("Mirror table Response:", table_status_response.json())
except:
print("Raw Mirror table Response:", table_status_response.text)
Printing the final responses instead of all:
Start Mirroring Status Code: 200
Mirror table Status: 200
Mirror table Response: {'continuationToken': None, 'continuationUri': None, 'data': []}
What could be the reason? Is it a limitation using REST API or something to do with authentication?
I have even fetched the Mirrored DB definition and body that was created using Fabric UI to compare and adjusted my API body and tried. Still No Luck!
