SqlServer has the ability to setup a ReadOnly standby database using Log Shipping
For an overview of Log Shipping see https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-ver15
The steps to setup SqlServer Log Shipping are described here; https://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/
another way to set it up is using TSQL
Example scripts where a standby database on a secondary server is created from database on primary server called tpcc I used 2 Linux sqlserver docker containers to set it up and I have a shared volume between the docker containers called /install
To run the containers;
for primary;
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<strong password>' -e 'MSSQL_AGENT_ENABLED=True' -v /mnt/h/hvr:/install -p 2433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
for secondary;
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<strong password>' -e 'MSSQL_AGENT_ENABLED=True' -v /mnt/h/hvr:/install -p 3433:1433 -p 4343:4343 -d mcr.microsoft.com/mssql/server:2019-latest
connect hammerdb to localhost,2433 to create tpcc database and tables
HVR agent installed on secondary server as well
Once created backup and restore to a standby tpcc dabase and enable Log Shipping using TSQL as below;
On the primary server run this scripts to backup the tpcc database;
BACKUP DATABASE tpcc
TO DISK = '/install/tlogs/tpcc.bak' with init;
On the secondary server run this script to create the standby database;
restore database tpcc from disk= '/install/tlogs/tpcc.bak' with standby='/var/opt/mssql/data/ROLLBACK_UNDO_tpcc.BAK'
On the primary server run these scripts to create sqlserver agent job and schedule to backup the transaction log;
DECLARE @LS_BackupJobId AS uniqueidentifierDECLARE @LS_PrimaryId AS uniqueidentifierDECLARE @SP_Add_RetCode As int
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database@database = N'tpcc',@backup_directory = N'/install/tlogs',@backup_share = N'/install/tlogs',@backup_job_name = N'LSBackup_tpcc',@backup_retention_period = 4320,@backup_compression = 2,@backup_threshold = 60,@threshold_alert_enabled = 1,@history_retention_period = 5760,@backup_job_id = @LS_BackupJobId OUTPUT,@primary_id = @LS_PrimaryId OUTPUT,@overwrite = 1
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)BEGIN
DECLARE @LS_BackUpScheduleUID As uniqueidentifierDECLARE @LS_BackUpScheduleID AS int
EXEC msdb.dbo.sp_add_schedule@schedule_name =N’LSBackupSchedule_localhost,24331',@enabled = 1,@freq_type = 4,@freq_interval = 1,@freq_subday_type = 4,@freq_subday_interval = 15,@freq_recurrence_factor = 0,@active_start_date = 20210422,@active_end_date = 99991231,@active_start_time = 0,@active_end_time = 235900,@schedule_uid = @LS_BackUpScheduleUID OUTPUT,@schedule_id = @LS_BackUpScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule@job_id = @LS_BackupJobId,@schedule_id = @LS_BackUpScheduleID
EXEC msdb.dbo.sp_update_job@job_id = @LS_BackupJobId,@enabled = 1
END
EXEC master.dbo.sp_add_log_shipping_alert_job
EXEC master.dbo.sp_add_log_shipping_primary_secondary@primary_database = N'tpcc',@secondary_server = N’localhost,3433',@secondary_database = N'tpcc',@overwrite = 1
On the secondary server run these scripts to create sqlserver agent job and schedule to copy the logs from shared to insternal storage and to restore the database using these transaction logs;
DECLARE @LS_Secondary__CopyJobId AS uniqueidentifierDECLARE @LS_Secondary__RestoreJobId AS uniqueidentifierDECLARE @LS_Secondary__SecondaryId AS uniqueidentifierDECLARE @LS_Add_RetCode As int
EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary@primary_server = N’localhost,2433',@primary_database = N'tpcc',@backup_source_directory = N'/install/tlogs',@backup_destination_directory = N'/var/opt/mssql/data',@copy_job_name = N’LSCopy_localhost,2433_herman',@restore_job_name = N’LSRestore_localhost,2433_herman',@file_retention_period = 4320,@overwrite = 1,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)BEGIN
DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifierDECLARE @LS_SecondaryCopyJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule@schedule_name =N'DefaultCopyJobSchedule',@enabled = 1,@freq_type = 4,@freq_interval = 1,@freq_subday_type = 4,@freq_subday_interval = 15,@freq_recurrence_factor = 0,@active_start_date = 20210422,@active_end_date = 99991231,@active_start_time = 0,@active_end_time = 235900,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule@job_id = @LS_Secondary__CopyJobId,@schedule_id = @LS_SecondaryCopyJobScheduleID
DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifierDECLARE @LS_SecondaryRestoreJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule@schedule_name =N'DefaultRestoreJobSchedule',@enabled = 1,@freq_type = 4,@freq_interval = 1,@freq_subday_type = 4,@freq_subday_interval = 15,@freq_recurrence_factor = 0,@active_start_date = 20210422,@active_end_date = 99991231,@active_start_time = 0,@active_end_time = 235900,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule@job_id = @LS_Secondary__RestoreJobId,@schedule_id = @LS_SecondaryRestoreJobScheduleID
END
DECLARE @LS_Add_RetCode2 As int
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)BEGIN
EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database@secondary_database = N'tpcc',@primary_server = N’localhost,2433',@primary_database = N'tpcc',@restore_delay = 0,@restore_mode = 1,@disconnect_users = 1,@restore_threshold = 45,@threshold_alert_enabled = 1,@history_retention_period = 5760,@overwrite = 1
END
IF (@@error = 0 AND @LS_Add_RetCode = 0)BEGIN
EXEC msdb.dbo.sp_update_job@job_id = @LS_Secondary__CopyJobId,@enabled = 1
EXEC msdb.dbo.sp_update_job@job_id = @LS_Secondary__RestoreJobId,@enabled = 1
END
The problem with HVR reading from database log on standby server
The above sqlserver log shipping is really a scheduled backup/restore of the transaction logs.
If HVR capture is configured to read from the standby logs, it fails with
F_JD0A62: An interim transaction log sector was encountered at [00000075:00006CC8] where the beginning of the block was expected. \
F_JT140F: The previous error occurred before any records were scanned. The scan start time was 2021-04-22T15:22:46+00:00 at address 00000075:00006cc8:0001 with SEQ 0x75001b3200. \
F_JT04A1: The previous error occurred between child 'sql2file-cap-docks' HVR version 5.7.0/15 executable and a parent HVR version 5.7.0/14 executable.
Another error also happens
F_JD20F2: DBMS error [[Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746. [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure]. DBMS error number [10054]. SQL State [08S01]. DBMS version [15.00.4123]. Last query [TRUNCATE TABLE #loginfo;INSERT INTO #loginfo EXEC(N'dbcc loginfo');]. \
F_JT1410: The previous error occurred after scanning record 1, which has LSN 00000075:00006cc8:0001 and timestamp 2021-04-22T15:22:46+00:00. \
F_JT04A1: The previous error occurred between child 'sql2file-cap-docks' HVR version 5.7.0/15 executable and a parent HVR version 5.7.0/14 executable [#7f5d].
The reason
In case of log shipping you start from creating a database mirror by restoring a source database backup.
Then you start shipping the logs and apply them your mirror (restore from the log backup).
As one of the last step of restoring from the log backup SQL Server does a recovery process. The committed changes are applied to the database, not yet committed changes are implicitly rolled back. This leads to some records being written to the log.
Provided capture is running all this time, HVR will read to the end of the log and will sit there waiting for more data to arrive.
When the next portion of log arrives, everything repeats. Except the log record written during recovery are stripped away. Also some transactions that were previously incomplete and were implicitly rolled back may have been committed by now.
The error F_JD0A62 may happen because some records the HVR read previously were deleted, and new records were written to the log instead, and block boundaries in the log changed as a result.
The reason for the communication link failure is because the restore closes the database connecrions;
The solution
start Capture using ArchivedLogOnly mode and point to the shared directory where the primary transaction logs are shipped on the secondary server
the solution for the communiction error is to let Capture run in a scheduled interval instead of continuous, since the logshipping is also not continious.
Working channel actions
sql2file SRC * * Capture /SupplementalLogging=CDCTAB /ArchiveLogPath="/install/tlogs" /ArchiveLogFormat=tpcc_%Y%M%D%h%m%s.trn /ArchiveLogOnly
sql2file SRC * * Scheduling /CaptureStartTimes="2,12,22,32,42,52 * * * *"
sql2file TGT * * Integrate