Deploy real-time reporting (RTR)

Real-time reporting (RTR) is an NBS 7 capability that reduces reporting latency from as long as 24 hours to between 5 minutes and 1 hour. RTR uses Change Data Capture to detect row-level changes in source tables, publishes those changes to Kafka topics, and loads the data into the reporting database. This section covers steps to install RTR with Helm charts.

On this page

  1. Prerequisites
  2. Create service user
  3. Enable Change Data Capture
  4. Deploy RTR services

Complete the sections on this page in order. Each section depends on the previous one. If you encounter issues during database setup, contact support at nbs@cdc.gov.

Prerequisites

Before you begin, verify that your environment meets the following requirements and choose a database installation method. The method you choose applies throughout this guide.

To reduce risk, consider setting up RTR in a testing environment before moving to production. This lets you run RTR alongside MasterETL and compare results, then turn off MasterETL only after you are satisfied with those results.

  1. RTR installation requires a supported NBS 6 version. See the Supported NBS versions page. To verify your NBS release version, run the following query:

    USE NBS_ODSE;
SELECT max(Version) current_version
FROM NBS_ODSE.dbo.NBS_Release;
  2. Run the ETL jobs one final time and make sure they complete successfully.
    • PHCMartETL.bat
    • MasterETL.bat
    • covid19ETL.bat

  3. Choose a reporting database. RTR can write to your existing RDB database, or to a new database you create by duplicating RDB. Pick one option and use it consistently throughout this guide.

    Back up the RDB database before you proceed. This step cannot be undone.

    • Create a new reporting database (suggested): Duplicate your existing RDB. This lets you run RTR alongside MasterETL to compare results before fully committing. You can use any name for this reporting database, but for the remainder of this guide, we refer to it as RDB_MODERN. The exact steps for database duplication depend on your SQL Server version and hosting environment. If your database is on Amazon RDS, see Back up and restore on Amazon RDS. For other environments, see Microsoft’s documentation on backup and restore operations.
      • Use your existing RDB database: RTR takes over writing to RDB. Turn off the MasterETL.bat and covid19ETL.bat jobs and proceed to the next step. MasterETL remains available for manual recovery runs if needed.

    If you use a new reporting database, you must use the new reporting execution server to run reports.

Create service user

Create a database service user that the RTR services use to read source data and write to the reporting database:

  1. Name: Any name works. A name descriptive of the role, such as rtr-service-user, is suggested.

  2. Database permissions:

    • NBS_ODSE: db_datareader
    • NBS_SRTE: db_datareader
    • RDB / RDB_MODERN: db_owner

Enable Change Data Capture

In this section, the terms cdc and CDC appear as part of SQL Server column and parameter names and refer to Change Data Capture, not the Centers for Disease Control and Prevention.

Change Data Capture (CDC) streams row-level changes from NBS_ODSE and NBS_SRTE to Kafka, where RTR services load them into the reporting database. Change Data Capture was already enabled on NBS_ODSE in the Case Notification service deployment. Running the bootstrap script again on NBS_ODSE has no additional effect. The script also enables CDC on NBS_SRTE, which is required for real-time reporting.

To enable CDC on NBS_ODSE and NBS_SRTE:

  1. Using a sysadmin account, apply Bootstrap script 101. CDC requires sysadmin permissions.

Deploy RTR services

Now that you have completed database setup and onboarding, deploy the RTR services. Some services depend on the previous ones completing successfully, so complete the pages in the following order:

  1. Debezium
  2. Kafka connector
  3. Java service