topic Re: How to get the MSSQL Transaction_ID in Qlik Replicate

How to get the MSSQL Transaction_ID

--Brad-- — Fri, 28 Jan 2022 13:35:45 GMT

I am trying to tie the Qlik Replicate change table to data in SQL Audit on MS SQL Server. I thought I would be able to do this with the transaction_id in Qlik Replicate, but that transaction_id does not match the transaction_id generated by MS SQL.

Qlik's is alphanumeric where SQL's is numeric only. I read in the documentation that The actual value is a hex-string of the 128-bit transaction ID." but when I decode Qlik's transaction_id, it still is not similar to what is in SQL:

Are these totally different transaction_ids? Am I just decoding it incorrectly? Can I get the SQL transaction_id some other way?

Thanks!
Brad

Re: How to get the MSSQL Transaction_ID

john_wang — Mon, 31 Jan 2022 01:48:30 GMT

Hello @--Brad-- ,

This is really a good question. Per my researching:

1. I'm afraid there is not an explicit mapping between transaction_id (in sys.dm_tran_database_transactions or other similar views) and [Transaction ID] (in TLOG, or say in fn_dblog() ). You cannot convert from one to another by a formula or vice versa because they are totally different things.

However we can find their relationship, I will describe in detailed below.

2. In Replicate, we use [Transaction ID] which gets from TLOG.

This is what you see in column "header__transaction_id".

3. Personally I do not think you can use transaction_id for your SQL Audit purpose, at least it's hard to use because it restarts at 0 after a server restart.

How to find out header__transaction_id by transaction_id

Get transaction_id from active transactions:

select * from sys.dm_tran_database_transactions WHERE database_id = DB_ID();

In my sample, the transaction_id is 18129.

Get the [Transaction ID] from TLOG by using transaction_id 18129

SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Xact ID] = 18129;

In my sample the [Transaction ID] is '0000:000016e6'

Get other information from TLOG by using [Transaction ID]

SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction ID] = '0000:000016e6';

In Replicate target table the header__transaction_id looks like: E6160000000000000000000000000000

Hope this helps.

Regards,

John.

Re: How to get the MSSQL Transaction_ID

--Brad-- — Tue, 01 Feb 2022 16:48:32 GMT

Thanks @john_wang ,

I see the relationship that you are talking about and have been able to trace the lineage from Replicate's header__transaction_id to the transaction_id that's in SQL Audit. I am working through how I would do this in a production scenario and my options don't seem ideal so far.

I am wondering: Is there any way to get Qlik Replicate to capture the XACT_ID (ie: transaction_id from SQL Audit) that is in the LDF file at the same time it is importing the rest of the data changes?

Thanks,
Brad

Re: How to get the MSSQL Transaction_ID

john_wang — Mon, 07 Feb 2022 13:48:10 GMT

Hello @--Brad-- ,

I was in public holiday in the past week. Sorry for the delay.

I'm afraid we are unable to get XACT_ID from TLOG in current Replicate versions yet. you may find all the header information by enable the store changes table. Please raise Feature Request in Suggest an Idea area if you really need it.

Regards,

John.

Re: How to get the MSSQL Transaction_ID

john_wang — Wed, 09 Feb 2022 01:09:52 GMT

Hello @--Brad-- ,

Noticed you opened an idea article Capture XACT ID transaction ID during MS SQL Replication . Let's wait for PM team update.

Thank you,

John.

Re: How to get the MSSQL Transaction_ID

--Brad-- — Wed, 09 Feb 2022 12:59:52 GMT

Thank you, @john_wang.