Striim 4.0.4 documentation

SQL Server setup for MSJet

Before Striim applications can use MSJet, a SQL Server administrator with the necessary privileges must do the following:

  1. Enable change data capture on each database to be read using the following commands:

    USE <database name>
    EXEC sys.sp_cdc_enable_db
  2. Stop the Capture and Cleanup jobs on each of those databases (see Administer and Monitor Change Data Capture (SQL Server)). This will stop SQL Server from writing to its CDC change tables, which MSJet does not require.

  3. Create a SQL Server user for use by MSJet. This user may use Windows Authentication or SQL Server Authentication (see discussion under Integrated Security in MSJet properties).

  4. Grant that user local Administrator privileges on the Windows system that will run the reader. (If Striim is running in Linux, the MSJet reader must be deployed to a Forwarding Agent running in Windows. If Striim is running in Windows, the MSJet reader can be deployed locally.)

  5. Grant that user the db_owner role for each database to be read using the following commands, which require the sysadmin role:

    USE <database name>
    EXEC sp_addrolemember @rolename=db_owner, @membername=<user name>
  6. If you have not previously performed a full backup on each of the databases to be read, do so now (Full Database Backups (SQL Server)).

  7. Configure the following stored procedure to run every five minutes on each database that will be read. This will retain the logs read by this adapter for three days. If that is more than necessary or not enough, you may increase the retentionminutes variable. Note that the longer you retain the logs, the more disk space will be required by SQL Server.

    declare @retentionminutes int = (3 * 24 * 60) --3 days in minute granularity
    
    declare @trans table (begt binary(10), endt binary(10))
    insert into @trans exec sp_repltrans
    
    select dateadd(minute, -@retentionminutes, getdate())
    
    declare @firstlsn binary(10) = null
    declare @lastlsn binary(10) = null
    declare @firstTime datetime
    declare @lasttime datetime
    
    select top (1) @lastTime = (select top(1) [begin time] 
      from fn_dblog(stuff(stuff(convert(char(24), begt, 1), 19, 0, ':'), 11, 0, ':'), default)),
        @lastlsn = begt
     from @trans
    order by begt desc     
    
    --All transactions are older than the retention, no further processing required,
    --everything can be discarded
    if (@lasttime < dateadd(minute,-@retentionminutes, getdate()))
    begin
      EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1  
    end
    else
    begin
      --see if anything can be discarded
      select top (1) @firstTime = (select top(1) [begin time] 
        from fn_dblog(stuff(stuff(convert(char(24), begt, 1), 19, 0, ':'), 11, 0, ':'), default)),
          @firstlsn = isnull(@firstlsn, begt)
      from @trans
      order by begt asc
    
      if (@firsttime < dateadd(minute, -@retentionminutes, getdate()))
      begin
        --Since only full VLogs can be truncated we really only need to check the earliest LSN 
        --for every Vlog's date
        select @firstlsn = substring(max(t.lsns), 1, 10), 
               @lastlsn = substring(max(t.lsns), 11, 10)
        from (select min(begt + endt) as lsns 
        from @trans group by substring(begt, 1, 4)) as t
        where (select top(1) [begin time] 
        from fn_dblog(stuff(stuff(convert(char(24), t.lsns, 1), 19, 0, ':'), 11, 0, ':'), default)
        where Operation = 'LOP_BEGIN_XACT') < dateadd(minute, -@retentionminutes, getdate())
    
        exec sp_repldone @xactid = @firstlsn, @xact_seqno = @lastlsn, @numtrans = 0, @time = 0,
          @reset = 0  
      end
    end