Skip to main content

Striim Platform 5.0 documentation

Configuring SQL Server to use MSJet

MSJet must run on Microsoft Windows. There are three ways to install it:

  • in a Striim server or Forwarding Agent running on the same Windows system that hosts SQL Server, in which case MSJet's Mode property can be Direct or Mine

  • in a Forwarding Agent running on a Windows system other than the one that hosts SQL Server, in which case MSJet's Mode property must be Mine

  • in a Striim server running on a Windows system other than the one that hosts SQL Server, in which case MSJet's Mode property must be Mine

When running in a Forwarding Agent or on a Windows system other than the one that hosts SQL Server, MSJet can read from SQL Server running on Linux.

Note

If transactional replication is already running, follow the instructions in SQL Server setup for MSJet when transactional replication is already running.

If you are reading from an Always On availability group, follow the instructions in SQL Server setup for MSJet for an Always On availability group.

Before Striim applications can use MSJet, an administrator with local administrator privileges on the Windows host that will run MSJet (the "MSJet host") and a SQLmust do the following:

  1. Create a Windows user for use by Striim on the MSJet host.

  2. Grant that user local Administrator privileges on the MSJet host operating system and make it a member of the sysadmin role in SQL Server. (The sysadmin role includes the db_owner role on all databases.)

  3. Unless Striim is running on Windows and you will run MSJet on the Striim server, log in to the MSJet host as the user created in the previous steps and install a Forwarding Agent (see Striim Forwarding Agent installation and configuration). Your Striim Forwarding Agent or Striim Server must be installed and run as a user with local administrative privileges on the host operating system.

  4. If Microsoft Visual C++ 2015-2019 Redistributable (x64) version 14.28.29914 or later (see Learn / C++, C, and Assembler / Visual Studio 2015, 2017, 2019, and 2022) and Microsoft OLE DB Driver for SQL Server version 18.2.3.0 (see Learn / SQL / SQL Server / Release notes for the Microsoft OLE DB Driver for SQL Server > 18.2.3) are not already available on the MSJet host, install them.

  5. If Replication subscribers are enabled on each database to be read, skip this step.

    If Replication is not enabled, you must enable CDC logging. In SQL Server, enable change data capture on each database to be read using the following commands, which require the sysadmin role:

    USE <database name>
EXEC sys.sp_cdc_enable_db

  6. If the Capture or Cleanup jobs are running on any of those databases, stop them (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.

  7. If Replication subscribers are enabled on each database to be read, skip this step.

    If Replication is not enabled, 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.

  8. If using Windows authentication, skip this step.

    If using SQL Server authentication, create a SQL Server user for use by MSJet.

    For more information, see Microsoft's Choose an Authentication Mode and the notes for MSJet's Integrated Security property in MSJet properties.MSJet properties

  9. Grant the SQL Server user (if using SQL Server authentication) or the Windows user (if using Windows authentication) 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>

  10. Perform a full backup using the full recovery model (so that the backup includes the transaction log to be read by MS Jet) on each of the databases to be read (see Learn / SQL / SQL Server / BACKUP (Transact-SQL)). Known issue (DEV-41740): If MSJet's Mode will be Direct, set BLOCKSIZE=4096 (see Backup Set Options / BLOCKSIZE; not required when Mode is Mine).

  11. If Replication subscribers are enabled on each database to be read, skip this step.

    If Replication is not enabled, 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
SQL Server setup for MSJet when transactional replication is already running

If transactional replication is already running, install MSJet on the publisher.

Before Striim applications can use MSJet, an administrator with local administrator privileges on the Windows host that will run MSJet (the "MSJet host") and a SQLmust do the following:

  1. Create a Windows user for use by Striim on the MSJet host.

  2. Grant that user local Administrator privileges on the MSJet host operating system and make it a member of the sysadmin role in SQL Server. (The sysadmin role includes the db_owner role on all databases.)

  3. Unless Striim is running on Windows and you will run MSJet on the Striim server, log in to the MSJet host as the user created in the previous steps and install a Forwarding Agent (see Striim Forwarding Agent installation and configuration). Your Striim Forwarding Agent or Striim Server must be installed and run as a user with local administrative privileges on the host operating system.

  4. If Microsoft Visual C++ 2015-2019 Redistributable (x64) version 14.28.29914 or later (see Learn / C++, C, and Assembler / Visual Studio 2015, 2017, 2019, and 2022) and Microsoft OLE DB Driver for SQL Server version 18.2.3.0 (see Learn / SQL / SQL Server / Release notes for the Microsoft OLE DB Driver for SQL Server > 18.2.3) are not already available on the MSJet host, install them.

  5. If using Windows authentication, skip this step.

    If using SQL Server authentication, create a SQL Server user for use by MSJet.

    For more information, see Microsoft's Choose an Authentication Mode and the notes for MSJet's Integrated Security property in MSJet properties.MSJet properties

  6. Grant the SQL Server user (if using SQL Server authentication) or the Windows user (if using Windows authentication) 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>

  7. If the Capture or Cleanup jobs are running on any of those databases, stop them (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.

  8. Perform a full backup using the full recovery model (so that the backup includes the transaction log to be read by MS Jet) on each of the databases to be read (see Learn / SQL / SQL Server / BACKUP (Transact-SQL)). Known issue (DEV-41740): If MSJet's Mode will be Direct, set BLOCKSIZE=4096 (see Backup Set Options / BLOCKSIZE; not required when Mode is Mine).

SQL Server setup for MSJet for an Always On availability group

For background information related to the steps described below, see:

We recommend configuring the availability group listener. When you use the listener's IP address in the connection URL, it is not necessary to change the database connection when automatic or manual failover occurs.

Before Striim applications can use MSJet, an administrator with local administrator privileges on the Windows host that will run MSJet (the "MSJet host") and a SQLmust do the following:

  1. Create a Windows user for use by Striim on the MSJet host.

  2. Grant that user local Administrator privileges on the MSJet host operating system and make it a member of the sysadmin role in SQL Server. (The sysadmin role includes the db_owner role on all databases.)

  3. Unless Striim is running on Windows and you will run MSJet on the Striim server, log in to the MSJet host as the user created in the previous steps and install a Forwarding Agent (see Striim Forwarding Agent installation and configuration). Your Striim Forwarding Agent or Striim Server must be installed and run as a user with local administrative privileges on the host operating system.

  4. If Microsoft Visual C++ 2015-2019 Redistributable (x64) version 14.28.29914 or later (see Learn / C++, C, and Assembler / Visual Studio 2015, 2017, 2019, and 2022) and Microsoft OLE DB Driver for SQL Server version 18.2.3.0 (see Learn / SQL / SQL Server / Release notes for the Microsoft OLE DB Driver for SQL Server > 18.2.3) are not already available on the MSJet host, install them.

  5. If CDC is enabled on each database to be read, skip this step. To check whether CDC is enabled, run this query, replacing <database name> with the name of the database::

    select name, database_id, is_cdc_enabeled from sys.databases where name = '<database name>'

    If CDC is not enabled on any database to be read, you must enable it using the following commands (this requires the SQL Server sysadmin role):

    USE <database name>
GO
EXEC sys.sp_cdc_enable_db

  6. If the Capture or Cleanup jobs are running on any of those databases, stop them (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.

  7. If using Windows authentication, skip this step.

    If using SQL Server authentication, create a SQL Server user for use by MSJet.

    For more information, see Microsoft's Choose an Authentication Mode and the notes for MSJet's Integrated Security property in MSJet properties.MSJet properties

  8. Grant the SQL Server user (if using SQL Server authentication) or the Windows user (if using Windows authentication) the db_owner role for each database to be read using the following commands (this requires the SQL Server sysadmin role), replacing <user name> with the name of the SQL Server or Windows user:

    USE <database name>
EXEC sp_addrolemember @rolename=db_owner, @membername=<user name>

  9. Perform a full backup using the full recovery model (so that the backup includes the transaction log to be read by MS Jet) on each of the databases to be read (see Learn / SQL / SQL Server / BACKUP (Transact-SQL)). Known issue (DEV-41740): If MSJet's Mode will be Direct, set BLOCKSIZE=4096 (see Backup Set Options / BLOCKSIZE; not required when Mode is Mine).

  10. Create a SQL Server Agent Job for each database to be read by MSJet as described below. This stored procedure will enable database transaction log retention for use by MSJet.

Warning

The following transaction log retention SQLServer Job will require significant disk space, so you must implement administrative practices to avoid filling the disk, which will cause SQLServer will halt and may cause availability and integrity issues for the database. We recommend setting up alerts in the host operating system to warn you when disk space is low.

To create the stored procedure and SQL Agent Job:

  1. Download the SQL script MSJetAlwaysOnSetup.sql from https://github.com/striim/doc-downloads and paste it into the SQL Server Management Query Editor.

  2. Edit the script to change the default database (use master) to a database where your SQL Server Administrator user has CREATE and EXECUTE Stored Procedure permissions.

  3. Run the script to create dbo.MSJetLogRetentionAOG:

    AlwaysOnSetup1.png

  4. Create a new SQL Server Agent job:

    AlwaysOnSetup2.png

  5. Enter a name for the job similar to the following and assign an owner with permission to execute stored procedures:

    AlwaysOnSetup3.png

  6. Configure a New Job Step for each database:

    AlwaysOnSetup4new.png

  7. Set Command as follows, replacing the @dbname value with the name of the database. We recommend a @retentionminutes value of 4320 (three days).

    SPTextCapture.png

  8. Set the Advanced Settings as follows (you may specify a different location for the output file if appropriate for your environment):

    AlwaysOnSetup6.png

  9. Click OK to save the job step.

    AlwaysOnSetup7.png

  10. Repeat steps 6-9 for each database MSJet will read.

  11. Create a New Job Schedule under the Schedules tab. We recommend scheduling the job to run every 30 minutes. This job will have very low impact on performance.

    AlwaysOnSetup8.png
Creating the QUIESCEMARKER table for MSJet

To allow Striim to quiesce (see QUIESCE) an application that uses MSJet, you must create a QUIESCEMARKER table in SQL Server.

The DDL for creating the table is: 

CREATE TABLE QUIESCEMARKER (
  source varchar(100), 
  status varchar(100), 
  sequence int, 
  inittime datetime2, 
  updatetime datetime2 default CURRENT_TIMESTAMP, 
  approvedtime datetime2, 
  reason varchar(100), 
constraint quiesce_marker_pk primary key (source, sequence));

The user created as described in Configuring SQL Server to use MSJet must have SELECT, INSERT, and  UPDATE privileges on this table.

In this section: