Central Vermont Public Service
We simply keep the tables stored in Sybase and Oracle independent from each other, but they can communicate using Sybase Replication Agent for Oracle. Replication Agent for Oracle worked well because it includes some of the middleware features needed for the Sybase and Oracle databases to understand each other's version of SQL.
Lead Database Administrator
Central Vermont Public Service (CVPS) needed to integrate its key Work Management online-transaction database and a reporting database that both operate on the Sybase ASE platform with an Oracle facilities management database. Keeping the databases separate was paramount to ensuring proper performance, and rewriting applications to run on the other database platform would have been too costly and time-consuming. To solve this challenge, CVPS turned to Replication Server and Replication Agent for Oracle from Sybase. The two technologies combine to allow the Sybase ASE and Oracle databases to share information in real-time and offer a heterogeneous database communication solution that was less costly than a comprehensive middleware solution.
Sharing information enables CVPS to perform complicated heterogeneous database communication at lower cost and without extensive re-writes.
- Integrates Sybase databases with Oracle database
- Allows the heterogeneous databases to communicate with each other rather than forcing a complete application rewrite
- Reduces database integration costs by allowing communication via replication rather than a comprehensive middleware solution
- Adaptive Server Enterprise
- Replication Agent for Oracle
- Replication Server
- Enterprise Connect Data Access
Enabling Proper Communication Between Heterogeneous
Organized in 1929 by the consolidation of eight electric companies, Central Vermont Public Service (CVPS) is the largest electric company in Vermont and serves close to 160,000 customers. J.D. Power & Associates ranked CVPS second in the East among mid-sized utilities for overall customer satisfaction.
The company has relied on Sybase database and application development technology for over 15 years. Most of the firm's database applications are homegrown ? written with Sybase PowerBuilder ? and run on Sybase ASE, the primary enterprise database platform for CVPS.
One of the company's largest applications, Work Management, runs on the Sybase ASE database platform. "Work Management handles much of our day-to-day service operations," says Wendy Perry, lead database administrator at CVPS. "Through this application, we are able to better manage all of our service-related calls to ensure our customers receive prompt service with their electrical needs."
This Work Management database helps CVPS manage projects such as power line extensions, new designs, re-construction, crew scheduling, and material tracking. The Work Management database is connected to another Sybase ASE database on which CVPS runs queries for both pre-written and ad-hoc reporting using various front-end reporting tools. As is the case with many enterprises, CVPS also relies on a database platform from another vendor. For CVPS, this involves an Intergraph application running on an Oracle database that helps the firm manage electrical facilities, lines and poles.
The Sybase online-transaction database constantly receives updates from approximately 100 users throughout the day as well as batch-file processing that primarily runs during off hours. The Oracle database is used by a handful of users, but will be rolled out to more users over time. A key requirement for CVPS to operate its business properly is to ensure that the Sybase and Oracle online-transaction databases share information efficiently with each other as well as with the Sybase reporting database. CVPS needs to keep the three databases separate to ensure proper performance but also needs them to exchange information updates. "In particular, we need to keep the two online-transaction applications closely integrated since they share a lot of common information," Perry explains.
and Middleware Solutions Prove To Be Too Costly
At one point CVPS considered rewriting one of the applications so that IT would not have to support two major enterprises databases. But after evaluation, both time and costs were considered exorbitant, and prohibitive.
This meant CVPS needed to find a cost-effective solution that would allow the two online-transaction databases to exchange table information. The company also needed to make sure the Oracle server could provide information updates to the Sybase reporting server. CVPS first conducted a trial middleware solution by proxying the tables and manipulating the Sybase application to think the tables were still there. CVPS then realized this approach would also be a costly endeavor.
"Technically this approach worked," Perry said. "We could proxy, update and see the tables. But problems emerged when it came to joining the systems. The databases have separate optimizers so performance was not sufficient, and the databases did not have knowledge of each other?s index in order to join efficiently. To make this approach work, we would have had to rewrite a lot of code to make the data transfers between the Oracle and the Sybase databases perform properly."
Alternative: Sybase Replication Server and Replication Agent
It was then that Perry began looking at whether replication would meet their needs. CVPS had been using Sybase Replication Server for about 15 years, primarily to keep the previous query database updated with the online Work Management and Facilities Management transaction systems and to keep query users off the online systems. By expanding the way CVPS used Sybase Replication Server, the technology turned out to be a viable option for integrating the Sybase and Oracle databases.
"We realized replication was the best way to go," Perry reveals. "We simply keep the tables stored in Sybase and Oracle independent from each other, but they can communicate using Sybase Replication Agent for Oracle. The two databases can then view tables from the other database. Replication Agent for Oracle worked well because it includes some of the middleware features needed for the Sybase and Oracle databases to understand each other's version of SQL."
CVPS tested this approach and found it could properly replicate tables between Oracle and the two Sybase systems. This appealed to the company because it would be a lot less expensive than a comprehensive middleware solution. Replication also offered a viable option because CVPS needs to replicate data in real-time to keep the Work Management application running and to ensure customer service calls are handled efficiently.
Via Replication Reduces Costs and Improves Efficiency
The CVPS solution combines the capabilities of Replication Server with Sybase Replication Agent for Oracle, which includes a restricted-use license of Sybase Enterprise Connect Data Access (ECDA). ECDA serves as a partial middleware solution to provide transparent access to Sybase databases from other heterogeneous databases.
"By integrating our Sybase and Oracle databases using the ECDA restricted license only for Oracle replication, we reduced our overall licensing costs," Perry says. "A comprehensive middleware application would have been more costly and would not have performed as well when we tried to join Sybase and Oracle tables. By using replication to transfer tables, we can keep both systems separate so that each database does not have to see tables through the proxy."
This approach has proven to be much more efficient and less costly than giving direct access to tables via middleware. CVPS already had Sybase Replication Server in-house so that it just had to convert licenses and add the Replication Agent for Oracle option. Programming replication to work with the Intergraph application on Oracle took extra work since CVPS did not know that application as well as its homegrown application running on Sybase ASE. But even that turned out to be a benefit.
"We now understand the inner workings of the Intergraph application much better having gone through the process of configuring it to replicate with the Sybase application," Perry says. "We needed to see what we were getting for transactions from Oracle so we could understand the impact when tables came over to the Sybase database. By understanding what would happen to the data, we could properly code for it."
A significant part of the project time for CVPS involved determining which Sybase tables would match to the tables in Oracle. Perry's team developed a mini database that holds mapping information and metadata that maps the tables. In some cases, one Sybase table maps to multiple Oracle tables and vice versa. Within tables, CVPS also had to map columns, and in some cases the data types were different. One column might map to multiple columns or to a column in another table. CVPS then developed objects that reads the metadata to generate the code (DDL) to create all of the required replication components that manages both 2-way and 1-way replication between the databases. CVPS started out thinking the mapping would be straightforward and that the replication function strings would handle all the nuances.
"We found out, however, that the developers would need access to a lot of code and that would be difficult if it was stored inside Replication Server's function strings," Perry said. "So instead we wrote stored procedures in each local database so that all the Replication Server has to do is call the stored procedure and pass the parameters. This approach is much easier to maintain and debug since Replication Server does not have to store code. The stored procedures being called by the function strings perform all the logic in the databases so that each application can do what it's meant to do, and we avoid syntax issues."
Sybase Easier Than Other Software Partners
Perry has found that working with Sybase developers and the customer support team is much easier than working with most software vendors. "We began working with Replication Agent for Oracle when it was still fairly new, so we had a few bugs to work through as is the case with any new software," Perry says. "But Sybase always responded quickly with builds that we could test, and then they created formal fixes. They demonstrated fast turnaround times ? especially since we were in the test-and-development stage at that point. Most vendors provide escalated support only for production systems, but Sybase gives us prioritized support regardless of where we are in our development."
Perry has worked with Sybase for over 16 years now and continues be impressed with the technology and support. "I like to talk to a person when working on a problem, and that's what Sybase allows me to do. Other companies force you to fill-out forms first, but Sybase gives you immediate access to its people."
It's a fact of life for many organizations like CVPS that Sybase and Oracle databases need to be kept separate so they can focus on executing the functions they are designed for. But they also often need to be integrated to keep the data they share in sync. "Sybase Replication Server and Replication Agent for Oracle allowed us to create a solution that achieves Sybase-Oracle integration, and by leveraging existing technology, we reduced our costs," Perry says. "The resulting architecture is complex, but by going through the process, we have a better understanding of how Sybase ASE and Oracle operate under the covers, which will help us fine-tune both databases for better overall performance."