Connecting to a mirrored SQL Server database source in a copy activity

[pmscorca]

Hi,

I've implemented a mirrored SQL Server database and I want to copy the related data in a warehouse by a copy activity. I've noticed that the connection to the mirrorred database doesn't appear between the selectable items: so, is it possible selecting in a copy activity the connection to the mirrored SQL Server database as a source? Is there a specific architectural choice to do not it?

Thanks

[apturlov]

Hi @pmscorca, this is a great question. I was not able to connect a mirrored Azure SQL database as a source in a Copy activity. I can connect the Copy activitiy directly to the original Azure SQL database, of course. In the official documentation for Copy activity the mirrored SQL database is not listed as a supported source. Also, this issue was already discussed in the community, see here Solved: Re: Unable to Select Mirror Database as Source in ... - Microsoft Fabric Community

I personally think there is logic behind it. If you have a working mirrored database in Fabric the data is already here, you don't need a copy activity to duplicate it. Of course, in terms of medallion architecture, the mirrored database would qualify for raw or bronze tier and it's likely not analytics ready. So, if you need to load data from the mirrored database into a Warehouse you'd likely need to transform data into a different schema and likely you want to implement an incrimental load. My suggestion would be to use a SQL script for that in a Script activity in the pipeline, or a Stored Procedure activity, for which you can create a stored procedure in your warehouse.
In both cases for SQL-based data load you would only need a connection to your Warehouse, not the mirrored database. In order to execute the cross-database SQL query you can use a three part syntax in you queries when referring to your mirrored database. You can test your quesies in SQL explorer in your Warehouse by adding your mirrored database in the list of SQL endpoints.

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

[pmscorca]

Hi, thanks for your reply.

I add that a mirrored SQL Server database (on-premise not Azure) is not selected inside a dataflow gen2 useful to perform some data transformations.

In the warehouse explorer it isn't visible the "+" feature to add a SQL analytics endpoint as visible in your picture. Thanks

Moveover, I've created this idea --> Mirrored database as a possible source for a dataflow gen2 

[apturlov]

@pmscorca I am puzzled with the context change. Not sure how your original question relates to your idea about a dataflow. But, yes, the mirrored SQL database (does not matter whether Azure or on-prem) cannot be used as a data source in Dataflow Gen2 as well. I would think the rationale is the same as in data pipelines: the data is already here, no need to duplicate. Now, there is a workaround for both scenarios: you can create shortcuts for mirrored tables in a Lakehouse, then use the Lakehouse as a data source and the shortcut tables will be visible in the list of tables:

This technique also applies to practically any workload that works with a Lakehouse.

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

[pmscorca]

Hi, thanks for your reply.

I prefer to refer a mirrored database in a warehouse by a specifying a fully qualified table (catalog_name.schema_name.table_name) in a query respect to create a shortcut in a lakehouse.

I think that a mirroring database could be at bronze level as for a lakehouse: it could be necessary apply some data transformations from bronze to silver/gold level, and therefore from a lakehouse or mirrored database to a warehouse; also for a lakehouse the related data could be duplicated in the next warehouse after applying some data transformations.

[v-achippa · Accepted solution]

Hi @pmscorca,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @apturlov for the prompt response. 

Currently this is a fabric limitation by design. Since this capability is not supported so I recommend upvoting the idea you created so the product team can track and consider it for future support. For now the current behaviour and workarounds remain the supported approach.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @pmscorca,

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue is resolved? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa