CWMS Database

Planned Activities

Most of the planned activities in FY25 for the CWMS Database support cloud migration efforts. 

Minor Improvements and Field Support

HEC staff will support the field in investigating, debugging, and fixing database issues that are encountered throughout FY25. This includes developing and deploying at least one new schema update in FY25 that contains fixes and improvements to the database.

Monitor Performance and Improve as Needed

The team will continually monitor performance of the database and improve as needed, based on feedback from the field and through internal performance reviews. HEC will conduct internal meetings at least monthly to identify, prioritize, and implement database performance improvements. 

Monitor and Improve Goldengate Performance

Goldengate is a tool used to copy data between databases in real time. It is utilized to move data from District T7s to the national CWMS database in CWBI. Continued monitoring of Goldengate is needed to ensure data transfer is up to date and running. As disruptions occur, HEC staff remediate the issue and backfills data into the national database. HEC staff also install patches to Goldengate as they become available from Oracle.  

Data Acquisition and National Database Administration

As we migrate systems to the cloud, HEC staff will develop tools, train district staff, and assist in developing scripts in python 3 for data acquisition at district offices. As district follow the "what can districts do now" steps, HEC and field staff will support the migration of server side processed and scripts. This migration includes the training and funding of district staff, with the goal of forming a national team of experts to assist other districts in script migration. Other tasks include integrating district data acquisition scripts into the Airflow application for scheduling runs, developing and implementing a GitHub repository for data acquisition scripts, and developing tools to assist in district data acquisition migrations.

Authorization for Data Access

An authorization scheme for determining who can both read and write to tables in the CWMS database is needed as we migrate to a single database environment. This task will develop a plan and implement that plan into the database/CDA. This includes developing a web application that will allow districts to assign read/write/delete permissions to specific users.

Migration of Historical Public Data into CWBI

As we transition to the national CWMS database in CWBI and Access to Water using the CWBI database, districts need to have a complete historical record of data available for time series that are accessible through Access to Water. HEC will assist and develop tools to migrate historical data from district T7 databases into the national CWMS database in CWBI.

Move Current Build System to Flyway Investigation

As we move into the cloud environment, migrating away from scheduled database schema updates into a Continuous Integration and Continuous Deployment (CI/CD) environment will make database updates more regular and standardized. Flyway is a system to implement CI/CD into database development. This task includes investigating and beginning the migration of our database to the Flyway system.  

Workshops and Webinars

Workshops and webinars will be developed as needed to assist districts in cloud migration tasks related to the CWMS database. This includes training on converting data acquisition scripts, using cwms-python to interact with the database, and any new tools developed by HEC for cloud migration.