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 Mode set to Trigger, if a DDL change is rolled back and the CDDL tracking table entry for that 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.

Adding more tables to an existing MSJet application

To add tables to an existing MSJet application, use one of the following procedures. One common use case for this is when a customer developing a Striim application starts by including only a small number of tables, then, when testing shows the app is working well, wants to add more tables.

When schema evolution (CDDL Capture) is enabled (see Handling schema evolution), new tables specified in the Tables property will automatically be read by MSJet. For example, if the Tables property value is schema1.%, a new table schema1.table99 will be read. However, a new table schema2.table01 will be ignored.

To add new or existing tables outside the current scope of the Tables property, you must drop and recreate the application with a new Tables value, as follows:

Note

The following procedure is not recommended when the application uses a persisted stream. In that case, Contact Striim support.

  1. Export the app's TQL.

  2. Quiesce and undeploy the app.

  3. In the Striim console, enter DESCRIBE <namespace>.<application>;, for example, DESCRIBE ns1.myMSJetApp; and record the source restart position log sequence number (LSN):

    CHECKPOINT (
   ADMIN:SOURCE:MSJAD_REMOTE:1 
   SOURCE RESTART POSITION ^
      [all={ OldestOpenLsn: 0x000360a0:0001a600:0001, LastCommitLsn: 0x000360a0:0001a620:0001,
        NumberOfOperations: 2}] ...

    In this example, the restart position LSN is 0x000360a00001a6000001 (omit the colons).

  4. Drop the app.

  5. Perform initial load on the tables to be added.

  6. Edit the exported TQL, modify the Tables value to include the new tables, set the Start Position to the LSN recorded recorded in step 2 (in the above example, StartPosition: 'LSN:0x000360a00001a6000001', and import the TQL. Alternatively, import the unmodified TQL and make the changes in Flow Designer.

  7. When initial load is complete, deploy and start the new app.

When schema evolution (CDDL Capture) is disabled, only tables that exist when the application is started will be read. New tables are ignored even if they are within the scope of the Tables property. To add new tables:

  1. Quiesce and undeploy the app.

  2. Create the new table(s) and perform initial load on them.

  3. Alter the application (see ALTER and RECOMPILE) and modify the Tables value to include the new table(s).

  4. When initial load is complete, deploy and restart the app.

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.