topic Questions about readSnapshotOnUnload parameter of MS SQL Server Endpoint in Qlik Replicate

Questions about readSnapshotOnUnload parameter of MS SQL Server Endpoint

iti-attunity-sup — Fri, 21 Jun 2024 05:48:56 GMT

Hello Experts

I have some questions regarding readSnapshotOnUnload parameter of MS SQL Server Endpoint.

Our customer is trying to migrate from on-premises SQL Server to RDS SQL Server.
The following internal parameter is set in the current Endpoint connecting to the on-premises SQL Server.
We would like to confirm if the internal parameters are still necessary to connect to RDS SQL Server.

readSnapshotOnUnload = TRUE

Questions :

1. In which case does readSnapshotOnUnload parameter need to be set?

Though my understanding is that it is required in order to ensure read consistency,
(e.g. in case that the SQL Server isolation level is not 'serializable' nor 'snapshots')
I would like to clarify which case should we use.

2. Is it required readSnapshotOnUnload=TRUE connecting to RDS SQL Server?

As far as I confirmed in our test RDS SQL Server environment, isolation level is 'read committed'.

And when I tried the parameter readSnapshotOnUnload = TRUE, connecting to RDS SQL Server,
I noticed that the following settings were required.

ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE <dbname> SET ALLOW_SNAPSHOT_ISOLATION ON

I am wondering how to configure the settings to be the same as when connecting to an on-premise SQL Server.

Any advice would be appreciated.

Regards,
Kyoko Tajima

Re: Questions about readSnapshotOnUnload parameter of MS SQL Server Endpoint

john_wang — Sat, 22 Jun 2024 04:51:24 GMT

Hello Tajima-san, @iti-attunity-sup

Thanks for reaching out to Qlik Community!

1. In which case does readSnapshotOnUnload parameter need to be set?

Though my understanding is that it is required in order to ensure read consistency,
(e.g. in case that the SQL Server isolation level is not 'serializable' nor 'snapshots')
I would like to clarify which case should we use.

Replicate is using the default isolation level of "Read Committed", If source table is heavily used and/or being updated in high frequency by business applications during the Full Load stage then may introduce duplicate data and application pause, set readSnapshotOnUnload to ensure data consistency and minimize locking contention.

2. Is it required readSnapshotOnUnload=TRUE connecting to RDS SQL Server?

As same as on-premise MS SQL Server.

I noticed that the following settings were required.
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE <dbname> SET ALLOW_SNAPSHOT_ISOLATION ON
I am wondering how to configure the settings to be the same as when connecting to an on-premise SQL Server.

You are right, these settings are required by MS SQL Server itself. Regarding how to config it, I'm pasting a link for your reference, or it's better to confirm with databases service provider or MS.

Hope this helps.

John.