Setting up Mirrorred SQL Server

[ptmk25]

Trying to set up Mirrorred SQL Server to on prem sql using data gateway.

Error:

The database cannot be mirrored to Fabric due to below error: Test connection failed. undefined. Activity ID: undefined.

Tested: Dataflow works fine with the sql connection using Std data gateway.

I am Admin on the workspace.

Checked all sql permissions.

CDC is enabled db/table.

TLS 1.2 on sql and on DG

Tried:

encrypted and unencrypted

different database servers

different authentication methods

[v-sgandrathi · Accepted solution]

Hi @ptmk25,

Microsoft does not explicitly mention a same region requirement for SQL Mirroring, which can be overlooked. In reality, Mirrored Databases rely on the Fabric gateway pipeline, and this pipeline requires that the gateway region, data source region, and Fabric capacity region all match. Because of this, a SQL connection might work for Dataflows but fail for Mirroring, showing the error Test connection failed. undefined. This happens when Fabric’s mirroring service cannot properly validate the backend connection. To help prevent this issue, make sure your gateway is updated and located in the same region as the Fabric capacity, that the login has full database permissions and CDC is enabled, and try restarting the gateway. The region alignment rule applies to Mirroring even though it is not specifically mentioned in the documentation.

Thankyou.

[apturlov · Accepted solution]

Hi @ptmk25, as a general recommendation, I suggest to use different connections to your on-premise SQL server for data pipelines/dataflows and for mirroring. Setting up mirroring requires more permissions for the SQL server account you use in the connection credentials.

There may be some confusion about the data gateway as well. You need to use specifically an On-Premises Data Gateway for your on-prem SQL server (see here Install an on-premises data gateway)

There is a detailed instruction on setting up a mirrored SQL database in Fabric, see here Tutorial: Configure Microsoft Fabric Mirroring from SQL Server - Microsoft Fabric. Check that you are using an instruction compatible with your SQL Server version.

If you find this answer useful or solving your problem please consider giving kudos and/or marking as a solution.

[v-sgandrathi]

Hi @ptmk25,

Just looping back one last time to check if everything's good on your end. Let me know if you need any final support happy to assist if anything’s still open.

Thank you.

[v-sgandrathi]

Hi @ptmk25,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

[v-sgandrathi · Accepted solution]

Hi @ptmk25,

Microsoft does not explicitly mention a same region requirement for SQL Mirroring, which can be overlooked. In reality, Mirrored Databases rely on the Fabric gateway pipeline, and this pipeline requires that the gateway region, data source region, and Fabric capacity region all match. Because of this, a SQL connection might work for Dataflows but fail for Mirroring, showing the error Test connection failed. undefined. This happens when Fabric’s mirroring service cannot properly validate the backend connection. To help prevent this issue, make sure your gateway is updated and located in the same region as the Fabric capacity, that the login has full database permissions and CDC is enabled, and try restarting the gateway. The region alignment rule applies to Mirroring even though it is not specifically mentioned in the documentation.

Thankyou.

[ptmk25]

Thank you @v-sgandrathi , where can I find in the documentation the requirement for the data gateway Region in the same Region as the F capacity? Don't see it.

[v-sgandrathi]

Hi @ptmk25,

The error The database cannot be mirrored to Fabric, Test connection failed. undefined is caused by the Fabric mirroring service not validating the backend connection properly, resulting in an empty error message. Your SQL connection works in Dataflows but fails during mirroring for this reason. Try the Test connection again after 15–30 minutes, make sure the gateway is updated and located in the same region as your Fabric capacity, and check that the login has full database permissions with CDC enabled. Restarting the gateway can also help.

Solved: Re: SQL Server Mirroring keeps giving error when I... - Microsoft Fabric Community
Microsoft Fabric Mirrored Databases From SQL Server - Microsoft Fabric | Microsoft Learn
Troubleshoot Fabric Mirrored Databases From Azure SQL Database - Microsoft Fabric | Microsoft Learn

Thank you.