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

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.