Configuring Azure SQL Database in a Fabric Copy Activity - Source and Destination Settings That Matter
Most Australian businesses we work with have at least one Azure SQL Database sitting at the centre of something important. A line-of-business app built in .NET ten years ago. A billing system. A customer portal. When those organisations move to Microsoft Fabric, the first real job is nearly always the same: get the data out of Azure SQL and into a lakehouse or warehouse, on a schedule, without someone babysitting it.
The copy activity in a Fabric Data Factory pipeline is how most people do that. It looks simple. You pick a connection, pick a table, pick a destination, hit run. And for a 50,000 row table it really is that simple. The trouble starts when the table has 400 million rows, or when someone asks for incremental loads, or when the destination is Azure SQL itself and you need upserts rather than appends.
This post walks through the Azure SQL Database specific settings in the copy activity, based on Microsoft's documentation and on what we've actually changed on client projects. Microsoft's reference page is Configure Azure SQL Database in a copy activity if you want the full field list.
Setting up the connection
Before you touch the copy activity, you need a connection to the database. In Fabric this lives under Manage connections and gateways, or you can create it inline from the copy activity's Source tab.
The authentication choice matters more than people think. You'll typically see basic (SQL username and password), organisational account, service principal and workspace identity options. My strong preference for anything that runs on a schedule is a service principal or workspace identity. Organisational account auth works fine until the person who created the connection goes on leave, changes their password, or leaves the company, and then your nightly load quietly fails. I've seen that exact scenario take down a finance team's month-end reporting at a mid-sized Brisbane firm. Nobody noticed for three days.
If your Azure SQL server has public network access disabled (and it should, for anything holding customer data), you'll need either a VNet data gateway or a managed private endpoint setup. Sort that out before you start building pipelines. It's the single most common thing that stalls the first week of a Fabric project.
Source settings
On the Source tab, once you've picked the Azure SQL connection and database, you choose how to read the data.
Use query gives you three options:
- Table - pick a table from the dropdown. Fine for small reference tables.
- Query - write your own SQL. This is what we use most often, because it lets you filter, select only the columns you need, and add an incremental watermark predicate.
- Stored procedure - call a proc and copy its result set. Useful when the DBA team owns the extraction logic and doesn't want you writing queries against their schema.
My honest advice: default to Query, even for simple cases. SELECT * from a table pulls columns you don't need, and when someone adds a column to the source table next year your destination schema suddenly changes. Naming the columns explicitly costs you two minutes and saves a confusing failure later.
Query timeout
Under the advanced section there's a query timeout setting. The default is 120 minutes. For most loads that's plenty. If you have a query that genuinely takes longer than two hours to return, the timeout is not your real problem. Look at the query plan.
Isolation level
You can set the transaction isolation level for the read: Read committed, Read uncommitted, Repeatable read, Serializable, Snapshot, or none (which uses the database default).
This is one of those settings that most people leave alone and probably shouldn't. If you're reading from a busy production OLTP database during business hours, a long read under Read committed can sit there holding shared locks and get in the way of the app. Snapshot isolation is the cleaner answer if the database has it enabled, because readers don't block writers. Read uncommitted (dirty reads) is faster still, but you can get rows that are later rolled back. For analytics loads that's sometimes acceptable. For anything feeding financial reports, I wouldn't.
Partition options
This is where the copy activity gets interesting for big tables. You have three choices:
- None - a single connection reads the whole result set. Default, and fine up to a few million rows.
- Physical partitions of table - if the source table is actually partitioned in SQL, the copy activity reads each partition in parallel.
- Dynamic range - you specify a partition column (ideally an integer or date column with an index on it), and optionally upper and lower bounds. The service splits the range into chunks and reads them in parallel.
Dynamic range is the one we use most. If you write a custom query, you need to include the placeholder ?AdfDynamicRangePartitionCondition in your WHERE clause so the service can inject its range filters. People miss this constantly. Without the placeholder, every parallel reader runs the full query and you get duplicates in the destination, or the pipeline throws an error, depending on how you've set it up.
A real example: a logistics client had a shipment events table with roughly 600 million rows. A single-threaded copy took close to five hours. Switching to dynamic range on the identity column, with parallel copies set to 8, brought it under 50 minutes. The source database was a Business Critical tier with headroom, which matters. If your source is a small General Purpose database, parallel reads will just fight each other for DTUs or vCores and you won't see much gain. Sometimes you'll make it worse.
I wrote more about the throughput side in an earlier post on copy activity performance for SQL databases.
Destination settings
Azure SQL Database also works as a destination. This comes up when you're feeding an operational system, pushing curated data back into an app database, or (less ideally) using Azure SQL as a reporting store because the team isn't ready for a Fabric warehouse yet.
Table option
You can either use an existing table or let the copy activity auto-create the table. Auto-create is handy for prototyping. For production, I'd create the table yourself with proper data types, keys and indexes. Auto-created tables tend to end up with generous nvarchar(max) columns and no primary key, which is a pain to fix later.
Write behaviour
There are three write behaviours:
- Insert - append rows. Fast and simple.
- Upsert - insert new rows and update existing ones based on key columns you specify. You can choose whether to use a temp database (tempdb) for the interim staging, or a schema in the destination database.
- Stored procedure - pass the data to a stored procedure using a table type parameter, and let the proc decide what to do.
Upsert is the one everyone wants, and it works well for moderate volumes. For very large upserts, I've had more predictable results with a two-step pattern: bulk insert into a staging table, then run a MERGE or UPDATE/INSERT in a separate Script or Stored procedure activity. It's more moving parts, but you get full control over the logic, and it's easier to debug when a key collision blows up at 2am.
The stored procedure option is useful for complex logic, though the table-valued parameter approach can be slow for large batches. I'd keep it for cases where you genuinely need row-level business rules on write.
Pre-copy script
You can run a SQL script against the destination before the copy starts. The classic use is TRUNCATE TABLE staging.Orders before a full reload, or deleting the slice you're about to reload in an incremental pattern. Simple, and it saves you adding a separate activity.
One caution: the pre-copy script runs every time, including retries. If your script deletes data and the copy then fails halfway, you've got a partial table until the next successful run. Design for that.
Bulk insert table lock and batch settings
Under advanced settings you'll find options for write batch size, write batch timeout, maximum concurrent connections, and whether to take a table lock during bulk insert.
The table lock option speeds up large loads noticeably, because SQL doesn't need to manage row-level locks. The trade-off is that nothing else can write to that table during the load. For a staging table that only the pipeline touches, turn it on. For a table the app is writing to, absolutely don't.
Max concurrent connections is worth setting deliberately if your destination is a smaller database. Fabric will happily open more connections than a Standard tier database wants to handle.
Mapping
The Mapping tab lets you import schemas and map source columns to destination columns. For straightforward copies where the column names match, you can leave it empty and let the service map by name. When names differ, or you need type conversions, import the schemas and map explicitly.
The type conversion settings are worth a look if you're moving data between SQL and a lakehouse. Decimal precision and datetime handling are the usual suspects. We had a client lose cents on currency values because a money column landed as a float somewhere in the chain. Explicit mapping caught it.
What still feels rough
A few honest gripes, as of late 2026:
- Error messages from failed copies can be vague. "Failure happened on 'Sink' side" tells you where, not why. You'll often need to check the detailed activity output JSON.
- The UI for parameterising queries with dynamic content is functional but clunky. Building a watermark query with string concatenation in the expression builder is nobody's favourite afternoon.
- Monitoring parallel copy behaviour isn't as transparent as it could be. You can see throughput and duration, but working out why a partitioned copy is skewed (one partition doing 80% of the work) takes some digging.
None of these are dealbreakers. The copy activity is reliable once it's set up, and for Azure SQL sources it's the right default tool. Just don't assume the defaults are tuned for your data.
Where to start
If you're about to build your first Azure SQL to Fabric pipeline, here's what I'd do. Get the connection working with a service principal and a private network path first. Build the copy with a query source, not a table. Start with partition option None, measure, and only add dynamic range partitioning if the duration is a problem. Create destination tables yourself.
We help organisations set up Fabric data pipelines through our Microsoft Fabric consulting and Data Factory consulting work. If you've got a gnarly Azure SQL migration and want a second pair of eyes, get in touch.