I'm using copy activity in azdf from Azure SQL DB to CSV file. I saw an option to use snapshot but I don't see this option in Azure SQL DB. I have few question: Does it really using snapshot? Does it delete the snapshot after the copy? Can I see the snapshot somewhere in the portal? I'm looking for some more information about this scenario.

When you enable snapshot isolation on an Azure SQL Database, it does not create a true database snapshot, but rather a logical snapshot of the data. Enabling snapshot isolation in AzureSQL does not create a physical database snapshot. Instead, it maintains a logical snapshot of the data within the transaction log and tempdb to provide a consistent view for transactions. The resources used for snapshots are automatically managed and released when the transaction completes. Basically, when you enable snapshot isolation using the ALTER DATABASE command, it enables a specific isolation level for transactions accessing the database. It does not create a physical snapshot of the entire database, instead when a transaction runs under snapshot isolation:- It sees the data as it existed at the start of the transaction , even if other transactions have modified the data since then. The database engine keeps track of the changes made by active transactions to allow this consistent view. Committed changes from other transactions are visible , but uncommitted changes are not. Where are the Snapshots getting stored? The data required to provide the consistent view is stored in the database itself, specifically in the transaction log and tempdb database. Snapshot Lifecycle: Snapshots are maintained until the transaction that requested the snapshot completes. Once the transaction commits or rolls back: The changes made by the transaction are committed or rolled back in the database. The resources used to maintain the snapshot are released and can be reused by other transactions.

Snapshot in Azure data factory copy activity

OGINTZ Marina 105

I'm using copy activity in azdf from Azure SQL DB to CSV file. I saw an option to use snapshot but I don't see this option in Azure SQL DB.

I have few question:

Does it really using snapshot?
Does it delete the snapshot after the copy?
Can I see the snapshot somewhere in the portal?

I'm looking for some more information about this scenario.

Vinodh247 22,951 Reputation points MVP

2024-07-22T16:06:28.84+00:00

Hi OGINTZ Marina,

Thanks for reaching out to Microsoft Q&A.

The copy activity in adf allows you to copy data between data stores located on-premises and in the cloud.

-Snapshot usage:

the snapshot option in the copy activity is not related to database snapshots in azure sql db. Instead, it refers to creating a destination table automatically during the copy process.

To enable this, follow this path in the adf authoring ui: Copy activity sink > Table option > Auto create table. Alternatively, you can set the “tableOption” property in the Copy Activity sink payload.

-Deletion of Snapshots:

since the snapshot option is about table creation, it doesn’t create or manage database snapshots in azure sqldb. If you’re interested in database-level snapshots, you’d need to handle those separately within azure sqldb.

-Viewing snapshots:

unfortunately, there’s no direct way to view these “snapshots” in the azure portal. If you are specifically looking for database snapshots, you’ll find them within the azure sql db service, not as part of the copy activity.

Remember that the term “snapshot” can have different meanings depending on the context. In this case, it’s about table creation during data copying.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
OGINTZ Marina 105 Reputation points

2024-07-23T07:39:19.4666667+00:00

I'm not sure I understand. If I use snapshot in Isolation level in copy activity, what does it do? because I got a message that I need to enable it in the DB.
Vinodh247 22,951 Reputation points MVP

2024-07-23T08:24:20.3+00:00
Using snapshot isolation in adf copy activities allows for consistent reads during data movement, minimizing the impact of concurrent transactions. To utilize this feature, you must enable snapshot isolation on your azureSQL database. After enabling it, you can configure your ADF copy activity to take advantage of this isolation level, ensuring that your data copy operations are reliable and efficient.

Enable Snapshot Isolation: You can enable snapshot isolation on your Azure SQL Database by executing the following SQL command:
ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Set the Isolation Level in ADF: In your ADF copy activity, you can specify the isolation level as "Snapshot" when configuring the source settings.
OGINTZ Marina 105 Reputation points

2024-07-23T09:17:16.8866667+00:00

So, if I need to enable it on SQL DB, it works like DB snapshot, it should save it somewhere.
OGINTZ Marina 105 Reputation points

2024-07-24T06:26:19.21+00:00

thanks a lot for the answer!
OGINTZ Marina 105 Reputation points

2024-07-24T06:27:22.69+00:00

Can you please write it as an answer so I can accept it, thanks a lot!
Vinodh247 22,951 Reputation points MVP

2024-07-24T07:11:13.02+00:00

OGINTZ Marina: Glad it helped you. I have converted the comment as answer now, you can accept it as an answer.

Accepted answer

Vinodh247 22,951 Reputation points MVP

2024-07-23T12:07:40.6666667+00:00
When you enable snapshot isolation on an Azure SQL Database, it does not create a true database snapshot, but rather a logical snapshot of the data. Enabling snapshot isolation in AzureSQL does not create a physical database snapshot. Instead, it maintains a logical snapshot of the data within the transaction log and tempdb to provide a consistent view for transactions. The resources used for snapshots are automatically managed and released when the transaction completes. Basically, when you enable snapshot isolation using the ALTER DATABASE command, it enables a specific isolation level for transactions accessing the database. It does not create a physical snapshot of the entire database, instead when a transaction runs under snapshot isolation:- It sees the data as it existed at the start of the transaction, even if other transactions have modified the data since then.

The database engine keeps track of the changes made by active transactions to allow this consistent view.

Committed changes from other transactions are visible, but uncommitted changes are not.

Where are the Snapshots getting stored?

The data required to provide the consistent view is stored in the database itself, specifically in the transaction log and tempdb database.

Snapshot Lifecycle:

Snapshots are maintained until the transaction that requested the snapshot completes. Once the transaction commits or rolls back:

The changes made by the transaction are committed or rolled back in the database.

The resources used to maintain the snapshot are released and can be reused by other transactions.
Please sign in to rate this answer.

1 person found this answer helpful.
OGINTZ Marina 105 Reputation points

2024-07-24T08:15:22.6466667+00:00

I have another question, If I enable snapshot in copy activity, it create snapshot per table not per table because I can't use copy activity for whole DB only per table. Is there a way to turn snapshot per a table?

Vinodh247 22,951 Reputation points MVP

2024-07-24T08:24:27.27+00:00

If I enable snapshot in copy activity, it create snapshot per table not per table because I can't use copy activity for whole DB only per table. Is there a way to turn snapshot per a table?

i believe there's a typo. can you rephrase your question?

OGINTZ Marina 105 Reputation points

2024-07-24T08:53:12.3233333+00:00

sorry,

If I enable snapshot in copy activity, it create snapshot per table not per DB because I can't use copy activity for whole DB only per table. Is there a way to turn snapshot per a table?

OGINTZ Marina 105 Reputation points

2024-07-24T08:54:02.12+00:00

I would like to do snapshot for the whole DB

Vinodh247 22,951 Reputation points MVP

2024-07-24T09:23:37.3066667+00:00

CREATE DATABASE YourDatabaseName_Snapshot AS SNAPSHOT OF YourDatabaseName;

This command creates a snapshot of the specified database, allowing you to view the data as it existed at the time of the snapshot.

OGINTZ Marina 105 Reputation points

2024-07-24T09:29:35.3066667+00:00

Thanks, I will try

OGINTZ Marina 105 Reputation points

2024-07-24T11:11:32.57+00:00

I tried to run it as a script before the copy activity and I got an error:

Keyword or statement option 'SNAPSHOT' is not supported in this version of SQL Server.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Snapshot in Azure data factory copy activity

0 additional answers

Your answer