From Oracle to SQL for Enhanced Efficiency and Future Growth
Boston Water and Sewer Commission (BWSC) manages the largest and oldest system of its kind in New England, providing drinking water, sewer and stormwater services to more than one million people daily. The water and sewer system has evolved and grown dramatically, having served the City of Boston as far back as the 18th century.
The Challenge
Despite its deep historical roots, BWSC aims to be future-forward with its technology. The agency had long relied on Oracle to support its Trimble Cityworks and Esri ArcGIS deployment within a single database. While sophisticated, the use of Oracle left BWSC in the minority for new Cityworks functionality. Since the majority of Cityworks customers were SQL Server, development was prioritized for this platform first, with updates for Oracle coming later.
Advances in technology, including broader industry adoption of SQL Server – which was easier to work with and had more available support staff – led BWSC in 2020 to seek a partner to support a significant technological evolution. The agency also wanted to upgrade its Cityworks work order management software, implement ArcGIS Enterprise, update its custom-coded GIS maintenance editing tools, and test all other integrated software applications and processes.
The ultimate goal was to improve BWSC’s GIS environment with state-of-the-art technologies that offered scalability for future growth and applied industry best practices to benefit both internal and external users.
The Approach
After evaluating responses to an RFP, BWSC selected NV5 for its strong GIS background, which included the fact that NV5 is the only Platinum partner with both Esri and Trimble.
“We were impressed with NV5’s resume,” said Kathryn Brewer, BWSC’s IT project director. “We could see the strength of knowledge of each person on the team. They brought expansive experience across GIS, Cityworks and more, covering all the different pieces of our puzzle.”
NV5 managed BWSC’s conversion from Oracle to SQL, a significant project made more complex by the amount of scripting, development and customized code that needed translation for the SQL database. The project also entailed upgrading and configuring
Cityworks, upgrading ArcGIS, building new tools to work with these new environments and updating existing tools to work within the new system.
Once this work was completed, it was time to cutover to the new SQL system – a process that could have taken up to two weeks and caused significant downtime for the agency. However, through preparation and automation of several steps in the conversion process, NV5 completed the cutover in just eight hours, minimizing the impact on internal users and assuring uninterrupted customer service.
The Results
“We felt like we were in a technological rut for several years, but now we’ve been freed from that,” Brewer said. “The modern workflow NV5 designed brings us a lot of value and access to more functionality.” Once the new SQL-based solution was operational, BWSC saw immediate improvements. SQL enabled Brewer’s team to respond to functionality requests from the GIS team without a long learning curve. “Our users are active and engaged, and they push us, asking for database schema changes and additional functions,” Brewer said. The NV5-designed system simplified setting up triggers, adding new data fields and responding to queries, which allows the team to be responsive to user requests.
Sean Connor, Director of GIS for BWSC, stated that “GIS staff has noticed no difference from our side in terms of impacts to workflows, which is remarkable considering the significant back-end changes.”
One positive, but unintended, consequence of the project was the ability to expand integrations with SQL. Brewer noted that now they are able to integrate with Argon, an asset management software tool for pipe scoring and maintenance prioritization. This application connects with GIS and Cityworks, as well as allowing parties to visualize information from a centralized dashboard.
Implementing Esri ArcGIS Enterprise was also part of this project, which enabled support of dashboards, web maps and other apps. For example, BWSC set up a collaboration with a third-party vendor that is testing for lead in schools and childcare centers. For other projects, vendors hosted the applications and data, limiting who at BWSC could access the information. Now, BWSC is moving in the direction of hosting future applications and data itself so that all interested internal parties can see this valuable data.
BWSC staff that utilize Cityworks had positive comments, much like the GIS staff, acknowledging that there was no difference in workflows or functionality as a result of the expansive backend change. The project also led to improvements in the efficiency of running the Cityworks monthly management reports from the app for those who need them.
The success of this project resulted in further collaboration between BWSC and NV5. The two organizations are now working on the agency’s implementation roadmap, which includes continuation for upgrading Cityworks to a cloud deployment, and the ArcGIS Enterprise Deployment to facilitate compatibility with ArcGIS Pro and the Esri Utility Network.
By working with NV5, BWSC not only addressed its immediate daily challenges with outdated technology, but also positioned the agency for future growth and improvements in efficiency.