Search code examples
azure-sql-databaseazure-data-factory

How do I declare the isolation level of a stored procedure activity in ADF?

This question is for all you ADF developers.

ADF sometimes gives control over our database isolation. But that capability is only available for certain activities.

Surprisingly enough, Azure SQL Database uses a default isolation of RCSI. This isolation level has its pros and cons. As a result of this unusual setting in Azure, it is important for any database developer to be at least aware of the isolation, and fine-tune it where needed.

In Regards to ADF:

For the sake of some activities ("lookups" and "copy-data"), ADF allows us to declare the isolation that we require. This is great! You can find that setting below.

enter image description here

However for a SQL "stored procedure" activity, there is no flexibility for declaring isolation levels. It means developers are required to alternate back and forth between declaring our isolation levels in the ADF tool (for lookups), or in the back-end SQL database (for stored procedures). The inconsistency is strange, and results in the need to sprinkle our conconcurrency-declarations in both places. This quickly becomes labor-intensive and redundant. I would expect "stored-procedure" activities in ADF to have the same isolation-level options that we find in lookups .

Instead of declaring my isolation level in two places, is there a better way to ensure that both the "lookups" and the "stored procedure" activities in ADF are always using the same concurrency strategy?

Sometimes it doesn't feel like ADF is robust enough, if it can't help me specify my isolation level when making stored procedure calls. Database developers need to have control over the concurrency strategy, for certain projects.

Solution

  • True. You currently cannot set the Isolation Level for Stored Procedure Activity. Your only 2 options as of now are:

    Script Acitivty Use a Script Activity and Run your Stored Procedure from there. Before Executing stored procedure you can set the Isolation Level.

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC YourStoredProcName @Param1, @Param2, ...;

    Custom Activity Create a Custom Acitivity, which is more involving

    Set Up Azure Batch Account:

    Create an Azure Batch account in the Azure portal. Ensure you've also set up a linked storage account for the Batch account. Develop Custom Code:

    You can write your code in a language of your choice, like C#, Python, etc. Ensure your code performs the desired operations, including setting the desired transaction isolation levels or any other database-specific operations. Package Code in a Docker Container:

    Dockerize your application. Create a Dockerfile that specifies how your application is built and run. Build and test the Docker container locally to ensure your code executes correctly. Push your Docker image to a container registry, like Docker Hub or Azure Container Registry. Integrate with ADF:

    In ADF, create a pipeline. Add a 'Batch Service' linked service. Configure it to point to your Azure Batch account and the pool. Add a custom activity to your pipeline. Point the activity to the 'Batch Service' linked service you just created. Specify the Docker image URL and the command to run in the container.