Backup on read-only secondary Node

[bihluh]

Hi,

I have a source server running on SQL Server 2014 with AlwaysOn Availability Group. We have 3 nodes on the AG:  Server 1: Primary 

Server 2:  read-only nodes ( Sitting in same network),  backup is running here

Server B: DR , sitting on high latency network due to long distance.

Since the connection to the high latency node is always timeout, so we use "AlwaysOnSharedSynchedBackupIsEnabled" internal parameter to prevent the timeout issue. 

However since we have the AlwaysOnSharedSynchedBackupIsEnabled turn on, It could no longer get the backup information from the secondary node.

Is there a way to :

1. Skip only particular node in the AG , instead of skipping query to all secondary node?

2. Is there a way to force Qlik Replicate to connect to secondary read-only node during Full Load, instead of pulling the data from Primary node that can potentially affect the performance?

Regards,

Bih Luh

[SwathiPulagam · Accepted solution]

@bihluh 

Please find my answers below:

1. Skip only a particular node in the AG , instead of skipping the query to all secondary node?

A) We can't skip one node in the always-on setup. If you use "AlwaysOnSharedSynchedBackupIsEnabled"  then replicate will consider the current node as standalone (Replicate will skip reading msdb from all the other nodes). The best option for timeout issues is to increase excecuteTimeout and cdcTimeout internal parameters for SQL endpoint.

2. Is there a way to force Qlik Replicate to connect to secondary read-only node during Full Load, instead of pulling the data from Primary node that can potentially affect the performance?

A) follow the below steps

1) 1st time, When you start the task replicate will connect to the primary database and create a publication with all required articles. Creating publication will be finished in a few mins so you can stop the task as soon as you see the articles added in publication.

2) SQL server endpoint you can add IP called "additionConnectionProperties   :     ApplicationIntent=ReadOnly".  With this change, connections will go to secondary.

3) start to reload and as soon as you finish the Full load, Just stop the task and remove the additional parameter which you added in step 2.

4) Resume the task. Now connections go to primary again to read changes. 

Thanks,

Swathi

[SaranyaK]

Swathi - i have a question here. We have 2 source nodes in Always on setup.  This task is read from backup files only setup. The backup logfiles always happen on Secondary and we connect to listener and listener goes to secondary or primary wherever the backups are available. This was in 6.6. We recently migrated to 2021 and it no longer reading from the secondary when we connect thru listener unless we forcefully make it read from secondary with option AlwaysOnSharedSynchedBackupIsEnabled.  How do we make it to switch between primary and secondary using listener and pick up wherever the backlogs is available. The database entry for LSN is only made in primary or secondary and the Listneter should identify that and go to it without making it a standalone node. How do we achieve this?

[SwathiPulagam]

Hi @SaranyaK ,

By default, If you provide always on listener name in the endpoint then Replicate will connect to all Replicas in always on setup to get backup information. I just made a quick test with 2021.11 and it works fine. once i took backup on primary node and next time on secondary node, Replicate recognized changes from both nodes with no issue. See the below log.

"
00043772: 2022-04-18T08:12:12:701355 [AT_GLOBAL ]I: Task Server Log - SQL_NULL (V2021.11.0.301 USREM-HDW.qliktech.com Microsoft Windows 8 Enterprise Edition (build 9200) 64-bit, Revision:8516d095d574faad8d29465c8cf7e73c69be9033, PID: 53396) started at Mon Apr 18 08:12:12 2022 (at_logger.c:2687)
00043772: 2022-04-18T08:12:12:701355 [AT_GLOBAL ]I: Licensed to Attunity Internal - Attunity Replicate Development, evaluation license (73 days remaining), all sources, all targets, all hosts (at_logger.c:2690)
00043772: 2022-04-18T08:12:12:701355 [AT_GLOBAL ]I: Log file encryption base "OReVm6sByTpg7hwRndW/xw==" (at_logger.c:2702)
00043772: 2022-04-18T08:12:12:702355 [INFRASTRUCTURE ]I: The log level for 'SOURCE_CAPTURE' has been changed from 'INFO' to 'VERBOSE'. (at_logger.c:2934)
.
.
00032924: 2022-04-18T08:12:19:27122 [SOURCE_CAPTURE ]T: sqlserver_src_set_odbc_connection_string(): connection string (without credentials): 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=XXAO-LST.qa.int;DATABASE=AO_test;Trusted_Connection=no;APP=QlikReplicate/2021.11 (sqlServer);' (sqlserver_endpoint_util.c:234)
.
00032924: 2022-04-18T08:16:11:799775 [SOURCE_CAPTURE ]T: mssql_locate_log_storage_unit_at_replicas(...) Looking at replica: Name='XXXXXO02' Role=Primary, Net URL Address = XXXXXO02.qa.int (sqlserver_log_queries.c:564)
00032924: 2022-04-18T08:16:11:800769 [SOURCE_CAPTURE ]V: Construct statement execute internal: '
select
bmf.physical_device_name,
bs.position,
[dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn),
[dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn),
bs.backup_set_id
from msdb.dbo.backupmediafamily bmf, msdb.dbo.backupset bs
where bmf.media_set_id = bs.media_set_id
and bs.backup_set_id > 0
and bs.database_name=db_name() and bs.type='L'
and
(
cast('0000009F:000034C0:0002' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) >= cast([dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24))
and
cast('0000009F:000034C0:0002' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) < cast([dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24))
)
and bmf.device_type in(2, 102, 0)
' (ar_odbc_stmt.c:3962)
00032924: 2022-04-18T08:16:11:996208 [SOURCE_CAPTURE ]V: Execute: '
select
bmf.physical_device_name,
bs.position,
[dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn),
[dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn),
bs.backup_set_id
from msdb.dbo.backupmediafamily bmf, msdb.dbo.backupset bs
where bmf.media_set_id = bs.media_set_id
and bs.backup_set_id > 0
and bs.database_name=db_name() and bs.type='L'
and
(
cast('0000009F:000034C0:0002' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) >= cast([dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24))
and
cast('0000009F:000034C0:0002' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) < cast([dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24))
)
and bmf.device_type in(2, 102, 0)
' (ar_odbc_stmt.c:2707)
00032924: 2022-04-18T08:16:12:187712 [SOURCE_CAPTURE ]T: mssql_locate_log_storage_unit_at_replicas(...) Located : Count=1, Low LSN=0000009f:00003465:0001 High LSN=0000009f:000034c4:0001 (sqlserver_log_queries.c:654)
00032924: 2022-04-18T08:16:12:367946 [SOURCE_CAPTURE ]T: mssql_locate_log_storage_unit(...) succeeded in probing individual replicas backup history (sqlserver_log_queries.c:316)
00032924: 2022-04-18T08:16:12:367946 [SOURCE_CAPTURE ]T: LSN '0000009F:000034C0:0002' Active/Backup presence state is kLSN_PRESENCE_BACKUP_ONLY (sqlserver_endpoint_capture.c:874)
00032924: 2022-04-18T08:16:12:367946 [SOURCE_CAPTURE ]V: mssql_locate_log_storage_unit(...) :Storage unit is:D:\MSSQL14.MSSQLSERVER\MSSQL\Backup\1.trn. Serial number within backup media is: 2 First LSN in current backup set is: 0000009f:00003465:0001 First LSN in next backup set is: 0000009f:000034c4:0001 (sqlserver_endpoint_capture.c:964)
00032924: 2022-04-18T08:16:12:367946 [SOURCE_CAPTURE ]T: Retrieving partition id's from SQL Server (sqlserver_log_utils.c:5829)
00032924: 2022-04-18T08:16:12:369902 [SOURCE_CAPTURE ]V: Construct statement execute internal: 'select s.partition_id,s.object_id
from sys.system_internals_partitions s , sysobjects o
where s.object_id= o.id
and o.xtype=N'U'
and s.is_data_row_format=1
and s.object_id in (901578250,933578364 )' (ar_odbc_stmt.c:3962)
00032924: 2022-04-18T08:16:12:556530 [SOURCE_CAPTURE ]V: Execute: 'select s.partition_id,s.object_id
from sys.system_internals_partitions s , sysobjects o
where s.object_id= o.id
and o.xtype=N'U'
and s.is_data_row_format=1
and s.object_id in (901578250,933578364 )' (ar_odbc_stmt.c:2707)
00032924: 2022-04-18T08:16:12:753239 [SOURCE_CAPTURE ]T: mssql_find_at_table_mapper(...) Partition ID = '72057594043105280', Lookup Key='0072057594043105280', is there? true (sqlserver_log_utils.c:651)
00032924: 2022-04-18T08:16:12:754246 [SOURCE_CAPTURE ]T: mssql_find_at_table_mapper(...) Partition ID = '72057594043170816', Lookup Key='0072057594043170816', is there? true (sqlserver_log_utils.c:651)
00032924: 2022-04-18T08:16:12:754246 [SOURCE_CAPTURE ]T: mssql_log_processing: querying for tables identified by partition IDs: 72057594043170816,72057594043105280 (sqlserver_log_processor.c:4022)
00032924: 2022-04-18T08:16:12:754246 [SOURCE_CAPTURE ]T: mssql_log_processing(...) processing 'D:\MSSQL14.MSSQLSERVER\MSSQL\Backup\1.trn', set 2 LSN='0000009F:000034C0:0002' (sqlserver_log_processor.c:4034)

.
.
.
.
.

00032924: 2022-04-18T08:17:28:90883 [SOURCE_CAPTURE ]T: mssql_locate_log_storage_unit_at_replicas(...) Inputs: LastBackupSetId=0 , LSN = 0000009f:000034c4:0001, Virtual device selector value = 0 (sqlserver_log_queries.c:525)
00032924: 2022-04-18T08:17:28:90883 [SOURCE_CAPTURE ]T: mssql_locate_log_storage_unit_at_replicas(...) Looking at replica: Name='XXXXXO01' Role=Secondary, Net URL Address = XXXXXO01.qa.int (sqlserver_log_queries.c:564)
00032924: 2022-04-18T08:17:28:90883 [SOURCE_CAPTURE ]V: Construct statement execute internal: '
select
bmf.physical_device_name,
bs.position,
[dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn),
[dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn),
bs.backup_set_id
from msdb.dbo.backupmediafamily bmf, msdb.dbo.backupset bs
where bmf.media_set_id = bs.media_set_id
and bs.backup_set_id > 0
and bs.database_name=db_name() and bs.type='L'
and
(
cast('0000009f:000034c4:0001' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) >= cast([dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24))
and
cast('0000009f:000034c4:0001' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) < cast([dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24))
)
and bmf.device_type in(2, 102, 0)
' (ar_odbc_stmt.c:3962)
00032924: 2022-04-18T08:17:28:317179 [SOURCE_CAPTURE ]V: Execute: '
select
bmf.physical_device_name,
bs.position,
[dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn),
[dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn),
bs.backup_set_id
from msdb.dbo.backupmediafamily bmf, msdb.dbo.backupset bs
where bmf.media_set_id = bs.media_set_id
and bs.backup_set_id > 0
and bs.database_name=db_name() and bs.type='L'
and
(
cast('0000009f:000034c4:0001' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) >= cast([dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24))
and
cast('0000009f:000034c4:0001' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) < cast([dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24))
)
and bmf.device_type in(2, 102, 0)
' (ar_odbc_stmt.c:2707)
00032924: 2022-04-18T08:17:28:500889 [SOURCE_CAPTURE ]T: mssql_locate_log_storage_unit_at_replicas(...) Located : Count=1, Low LSN=0000009f:000034c4:0001 High LSN=0000009f:000034d0:0001 (sqlserver_log_queries.c:654)
00032924: 2022-04-18T08:17:28:691084 [SOURCE_CAPTURE ]T: mssql_locate_log_storage_unit(...) succeeded in probing individual replicas backup history (sqlserver_log_queries.c:316)
00032924: 2022-04-18T08:17:28:691084 [SOURCE_CAPTURE ]T: LSN '0000009f:000034c4:0001' Active/Backup presence state is kLSN_PRESENCE_BACKUP_ONLY (sqlserver_endpoint_capture.c:874)
00032924: 2022-04-18T08:17:28:692127 [SOURCE_CAPTURE ]V: mssql_locate_log_storage_unit(...) :Storage unit is:D:\MSSQL14.MSSQLSERVER\MSSQL\Data\2.trn. Serial number within backup media is: 2 First LSN in current backup set is: 0000009f:000034c4:0001 First LSN in next backup set is: 0000009f:000034d0:0001 (sqlserver_endpoint_capture.c:964)
"

Please create a case to troubleshoot the issue.

Thanks,

Swathi

[lyka]

Hi Saranya,

As we may need additional logs to troubleshoot the issue, please open a support case.

Thanks
Lyka