Skip to main content

SQL Server operational considerations

Operational considerations when using MSJet

  • Debug messages for the Windows-native portion of the adapter may appear in striim/logs/striim_mssqlnativereader.log rather than in striim.server.log.

  • If utilizing both replication and CDC, you must continue to keep CDC jobs enabled.

  • Known issue DEV-44032: If you drop a table and re-create it, you must manually enable CDC for the new table using the following command. Replace <schema name> with the name of the schema containing the table, <table name> with the name of the table, and <Striim role> with the name of the Windows or SQL Server user created for use by Striim as described in Configuring SQL Server to use MSJet.Configuring SQL Server to use MSJet

    EXEC SYS.sp_cdc_enable_table @SOURCE_SCHEMA = '<schema name>', @SOURCE_NAME = '<table name>',
  @ROLE_NAME = '<Striim role>'

  • Known issue DEV-59783: in this release, in Mine mode, with CDDL Capture Mose set to Trigger, if a DDL change is rolled back and the CDDL tracking table entry for thaty DDL is greater than 8000 bytes (an unusually large value), the application will halt and must be recreated.

Reading from multiple databases with a single Forwarding Agent

Each MSJet instance can read from only a single database. To read from multiple databases, you may run multiple instances of MSJet in a single Forwarding Agent (see Striim Forwarding Agent system requirements).Striim Forwarding Agent system requirements

Transaction alerts

When the following conditions occur, alerts including text similar to the following examples will be written to the server log and appear in the web UI.

  • A transaction has been open for over 24 hours: Transaction cache alert: Transaction 6.27.716 has been open for 1 day(s) 0 hour(s)

  • Total number of open transactions currently being processed exceeds 1000: Transaction cache alert: Total open transactions 1002 exceeds threshold 1000

  • A single transaction contains more than 1 million operations: Transaction cache alert: Transaction 10.10.3033 has 1000006 operations, exceeds threshold 1000000

These alerts will repeat every ten minutes so long as the condition persists.

MSJet limitations

  • Tables with XML columns are not supported.

  • Reading from secondary databases is not supported.

  • Reading from AG listeners is not supported.

  • Reading from backups is supported only if they are accessible only in the location where they were taken.

  • Reading from SQL Server sources with delayed transaction durability enabled (see Learn > SQL > SQL Server > Control Transaction Durability) is not supported.

Operational considerations when using MS SQL Reader

If you will no longer use an app in which MS SQL Reader's Auto Disable Table CDC property is False, when stopping it for the last time, do the following in order to delete the tables that will no longer be used for CDC (see Learn / SQL / SQL Server / Change Data Capture Tables (Transact-SQL)):

  1. Stop and undeploy the application.

  2. In the Flow Designer, edit the MS SQL Reader, set Auto Disable Table CDC to True, and save the change.

  3. Redeploy and start the application.

  4. Stop, undeploy, and drop the application.