CRIMS DB2 to SQL Server Migration

Comprehensive Project Overview

Project Scope and Objectives
Project Lifecycle and Execution

1. Planning and Design Phase
Cleanup Identification
List of SP and UD for migration
Identify Risk and Challenges
Milestone & Roadmap
Team formulation
Source Control Repository
Preliminary Analysis

2. Development and Migration Work
Timeframe
Parallel development
Interfaces
Java Classes
Mule Applications
Code Development
Query Optimization
Data Type Resolution
Schema Consistency

3. Testing and Quality Assurance
JIRA Ticket
Test Cases
Query Optimization Testing
Interface Testing
Code Freeze
Performance Testing
Regression Testing
End-to-end Testing
User Acceptance Testing

4. Risk Management and Challenges
Task Overlaps
Change Management
Cross-Team Coordination
Rigorous Testing
Rollback Planning

5. Go-Live and Deployment

6. Post-Migration Tasks

Benefits of Migration
Improved Performance
Easier Maintenance
Reduced Costs
Better Integration
Scalability
Modernization

Conclusion

Screenshots
Figure 1: SharePoint Project files
Figure 2: SharePoint Reports testing files
Figure 3: SharePoint Post Migration tasks
Figure 4: Our customers

Introduction

The CRIMS DB2 to SQL Server migration was a huge undertaking transferring 600 tables and 1TB of data that required extensive planning, collaboration, coordination, and execution. The project officially began on January 15th, 2024, and successfully completed on December 8th, 2024. Besides many county agencies, our customers include Local, Regional, State, and Federal Agencies. This migration was a step forward towards database modernization due to the limitations of the legacy DB2 system hosted on a mainframe, which prevented the ITD CRIMS team’s ability to develop requested enhancements for the past several years. Over the past five years, all new projects have been developed in SQwith Vision 2036 of providing Safe and Livable Communities and meets the 10X goals of Accessible and Integrated Infrastructure. This migration boosts accessibility, scalability, and performance, reducing operational overhead by 23% and speeding up feature deployment by 25%, all while offering a future-proof solution for law enforcement.

Project Scope and Objectives

The primary goal of this project was to migrate the existing CRIMS database from DB2 to SQL Server while ensuring smooth transition for the users.

The migration included all relevant database objects, including tables, stored procedures, java classes for sql, and user-defined functions. These elements were carefully assessed and categorized based on their dependencies and criticality to system operations. Each stored procedure and function were meticulously rewritten to conform to SQL Server’s syntax and optimized for performance. The 232 Java classes were also modified to support the new query syntax.

As part of the migration, a list of 56 obsolete tables was identified for retirement. These tables were either no longer used or had been replaced by more efficient structures in SQL Server. Their removal helped streamline database operations and reduce unnecessary storage consumption.

38 Mule applications that interfaced with DB2 were also migrated to SQL Server. This required updating configurations, rewriting queries, and ensuring that each application could seamlessly communicate with the new database. Mule applications were tested individually and in integration environments to validate functionality.

During the DB2 to SQL migration, we also migrated the non-CRIMS code of 15 APIs and D-Series jobs for AWS, 50 stored procedures for DALITE, 40 stored procedures for the Public Defender system, and 12 stored procedures for the Probation system.

A rigorous testing and validation process was put in place to ensure data integrity and application functionality. Each migration step was verified through multiple testing phases, including unit testing, integration testing, and user acceptance testing, to guarantee a seamless transition.

Smooth integration with other dependent systems was a crucial objective. The migration plan accounted for all interfacing applications, ensuring that their connectivity and functionality remained intact post-migration. Various API connections and data exchange processes were tested extensively to prevent failures.

Training IT teams and developers on the new SQL Server environment was a key part of the transition. This included hands-on sessions, documentation, and live support during the initial rollout to ensure smooth adoption and immediate issue resolution.

Project Lifecycle and Execution

1. Planning and Design Phase

The project followed a structured lifecycle, beginning with multiple planning and design meetings. The planning and design phase included:

Cleanup Identification: Identifying the list of tables to be retired was one of the first steps. A thorough analysis was performed to determine which tables were obsolete and which ones needed to be retained or restructured in SQL Server.

List of SP and UD for migration: A comprehensive list of stored procedures and user-defined functions to be migrated was compiled. The complexity of these procedures varied, and each was reviewed to determine necessary modifications before migration.

Identify Risk and Challenges: A feasibility analysis was conducted to assess potential risks and challenges. This involved evaluating compatibility issues, data transformation requirements, and potential performance bottlenecks in the new environment.

Milestone & Roadmap: A detailed roadmap with milestones was created to guide the migration process. Key deliverables were defined for each phase, ensuring accountability and progress tracking throughout the project.

Team formulation: Cross-functional teams comprising approximately 30 resources from ITD CRIMS, AWS, Public Defender, Probation, ITD Shared Services, ITD Server Services, and other teams were assembled to collaborate on different aspects of the migration.

Source Control Repository: GitHub was established as the primary version control system to manage code changes. Branching strategies were implemented to allow simultaneous development and testing without disrupting production environments.
Preliminary Analysis: A proof of concept (PoC) environment was set up to validate migration strategies before full-scale execution. This environment
served as a testing ground for database conversions, application integrations, and performance assessments.

2. Development and Migration Work

Timeframe: From January 2024 to August 2024, the team worked on developing, refactoring, and migrating the database schema and application logic.

Parallel development: Code changes occurred in parallel to production updates, ensuring that all modifications were consistently reflected in both environments. This parallel development approach minimized disruptions and allowed for continuous validation.

Interfaces: A total of 147 interfaces were identified for testing and upgrades. Each interface was analyzed for compatibility with SQL Server and modified as needed.

Java Classes: 232 classes were migrated as part of the transition. Application logic that was tightly coupled with DB2 had to be carefully refactored to work efficiently in SQL Server.

Mule Applications: 38 Mule applications were updated to work with SQL Server. These updates included modifying database connection settings, rewriting SQL queries, and reconfiguring data transformation rules.

Code Development: Ensuring that application logic remained intact post-migration was a priority. Automated and manual validation methods were used to confirm data consistency and business rule adherence.

Query Optimization: SQL Server indexing strategies were implemented to optimize performance compared to the legacy DB2 indexes. Indexing best practices were applied to improve query execution times and reduce database load.

Data Type Resolution: Data type discrepancies between DB2 and SQL Server were resolved to maintain consistency in reports and application queries. This involved mapping incompatible data types and implementing transformation logic.

Schema Consistency: Cross-validation of schema structures between DB2 and SQL Server was conducted to ensure consistency. Schema comparison tools were used to verify object definitions, relationships, and constraints.

3. Testing and Quality Assurance

JIRA Ticket: A comprehensive test plan was created, with each task being tracked using JIRA tickets.

Test Cases: A total of 1160 test cases were executed across various functional areas, covering key workflows such as person summary, name search, juvenile records, custody, pretrial, probation, bookings, subpoenas, e-processes, and system admin functionalities.

Query Optimization Testing: Multiple query tests were performed, including ARIES, DOJ queries, missing persons, and warrant lists. Each query was validated to ensure it produced accurate and expected results in SQL Server.

Interface Testing: Testing included interface testing for CRIMS, AWS, Dalite, Mule apps, intra-CRIMS, and DOJ systems. These tests ensured that all connected applications continued to function correctly post-migration.

Code Freeze: A Code Freeze Period was enforced from September 2024 to December 2024 to stabilize the environment before go-live. This allowed teams to focus on final validations and performance optimizations.

Performance Testing: Multiple tests were conducted to ensure SQL Server could handle the same or greater loads than DB2 with improved response times. Load testing scenarios were simulated to measure database performance under peak conditions.

Regression Testing: Automated test cases were executed to validate that existing business functions remained unaffected by the migration. Automated regression test suites were run regularly to detect unintended changes.

End-to-end testing: To confirm operational continuity end-to-end testing was conducted across various user workflows. Real-world scenarios were simulated to verify system behavior under normal and edge cases.

User Acceptance Testing: The CJIS team worked closely with Sheriff’s Department, Probation, Courts, District Attorney and Public Defender on the User Acceptance testing.

4. Risk Management and Challenges

The project faced several challenges, including task overlaps, continuous production changes, cross-team coordination, rigorous testing requirements, data validation issues, and rollback planning. Each risk was mitigated through strategic planning and proactive issue resolution. The following risks and challenges were identified, and a mitigation strategy was developed:

Task Overlaps: Due to the complex nature of the project, certain tasks overlapped, requiring careful coordination.

Change Management: Continuous production changes required additional validation to ensure they were included in the migration scope.

Cross-Team Coordination: Multiple teams were involved, necessitating frequent stand-up calls and status meetings.

Rigorous Testing: With over 1000 test cases, rigorous testing was essential to ensure a smooth transition.

Data Validation Issues: Differences in how DB2 and SQL Server handle NULL values required extensive validation to prevent data mismatches.

Rollback Planning: A rollback strategy was created to revert back to DB2 in case of critical failures during go-live.

5. Go-Live and Deployment

The final deployment was executed on December 8th, 2024, with only four hours of outage, completing all necessary go-live tasks efficiently including the following:

Pre-data migration tasks completed.
Data migration execution and validation.
Data count verification to ensure consistency.
Backup of migrated data.
Production applications have started again.
Reversing the F5 rule and validating AWS, Dalite, and JCATS production traffic.
Confirming CRIMS applications were up and running for all users.

6. Post-Migration Tasks

Post-migration efforts focused on cleanup, performance monitoring, and user adaptation including the following:

Retiring tables that were no longer needed.
Searching for control characters that could cause issues.
Updating JBoss and JDBC data sources in production.
Conducting row count comparisons to ensure data integrity.
Monitoring system performance and fine-tuning indexes for optimal efficiency.
Addressing minor bugs identified during early post-migration operations.
Conducting training sessions for users to adapt to SQL Server’s new functionalities.

Benefits of Migration

Migration brought significant improvements in performance, maintainability, cost reduction, integration, scalability, and modernization.

With the following improvements:

Improved Performance: SQL Server offers better query optimization and performance tuning features, resulting in faster data processing.

Easier Maintenance: SQL Server is more modern and has better support, making it easier for the ITD CRIMS team to implement enhancements.

Reduced Costs: Migration eliminated mainframe expenses, reducing overall operational costs.

Better Integration: With SQL Server, CRIMS applications can integrate seamlessly with other Microsoft technologies.

Scalability: SQL Server supports larger datasets and high availability solutions.

Modernization: Allows for future advancements in reporting, analytics, and AI-driven enhancements,

Now all the pending enhancements requests have been added to develop and release pipelines. The users are overjoyed to be able to get new features. The system is more scalable and can adapt to the changing technology needs and feature additions to meet the customer’s needs while keeping it secure.

Conclusion

The CRIMS DB2 to SQL Server migration was a huge undertaking that required extensive planning, development, and testing efforts over one year. The successful execution of this project has positioned CRIMS on a modern, scalable, and maintainable database platform, enabling the implementation of long-awaited enhancements and improving overall system efficiency. The benefits realized from migration far outweigh the challenges faced during the process.
Moving forward, the CRIMS team will focus on optimizing the SQL Server environment, leveraging its advanced capabilities for future projects, and ensuring continued improvements to the system.