The Horowhenua District Council has been able to streamline its server solution, reduce costs and coordinate its database systems with Microsoft SQL Server.
||We were always looking to bring our systems to Microsoft SQL Server and the solution we have now was definitely worth waiting for.
Horowhenua District Council
Located an hour north of Wellington in Levin is the Horowhenua District Council’s main office. Established in 1989, the council also has two service centres in Foxton and Shannon.
The council’s functions and roles are wide ranging, from enabling democratic decision-making and managing local infrastructure, to facilitating solutions, providing advocacy and environmental management.
An important part of the council’s remit is asset management for water usage including waste and storm water. Water usage is tracked using Geographic Information Systems (GIS) and a webmap, an internet based viewer to provide a geographical reflection of the data captured for the region. In order to do this, Ian Tate, GIS Officer, requires the most up-to-date software and database to capture, manage and analyse all the data that is linked to each specific location.
A year ago the council decided to upgrade the Intergraph Geo-media 6.0 version to Intergraph Geo-media 6.1, and in order to do this the council’s infrastructure server required an upgrade because the Oracle license was up for renewal.
The council originally had three physical servers with three licences of SQL Server, and one physical server with an Oracle licence. Upon evaluation of the existing systems, two issues arose around the potential upgrade of the Oracle solution.
Firstly, the Oracle solution required specialist skills to continue maintenance, and as there was no longer an in-house person capable of running the solution, the council was faced with either hiring a new specialist or replacing the whole system. Further evaluation of the solution showed that the council already had staff with existing Microsoft Structured Query Language (SQL) Server skills who were capable of maintaining the SQL Server with no additional training. In addition to this, the Oracle solution had aged and the licensing costs to upgrade to the latest technology were greater than those to migrate to SQL Server.
Visualising the perfect Server
After an in depth evaluation of the solutions on offer in the market, and bearing in mind the need for significant cost savings, the council made the decision to work with SQL Services and Intergraph to condense the Oracle server and existing SQL Servers into two Microsoft SQL Servers.
Over a three-month period the Horowhenua District Council, Intergraph and SQL Services worked to transfer data and information from the legacy system to the Microsoft SQL Servers.
Luke Chignell, Consultant from SQL Services, explained that it was very complex project right from the start.
“The council had added extra functionalities to the Oracle server and those needed to be reproduced in the SQL Server. A big part of the project for the council was asset management. It is one thing to have the data tell you that you have a block of land but quite another to have it tell you the block of land has a house on it that needs to be cleaned once a month.”
Trevor Couper, IT Systems Analyst for the Horowhenua District Council, agreed with Luke’s assessment of the project.
“The complexity came from the fact that we were migrating data from a spatially aware platform to a non-spatially aware platform, so needed to make allowances for that. Intergraph was very good at helping us deal with any issues that arose during the data upload process.”
Streamlined information processes and successful teamwork
The implementation was completed in April 2009. The new system was set up and tested in parallel to ensure that the new SQL triggers and spatial scripts worked smoothly. There was freeze on entering data into the Oracle system over a two week period, after which the switch was flicked and the new system was up and running solo.
“I was incredibly impressed with the way both Intergraph and SQL Services worked in with us. All communication was very open and both teams showed great commitment to the project. We were always looking to bring our systems to Microsoft SQL Server and the solution we have now was definitely worth waiting for,” says Janet Greig, Project Manager for the Horowhenua District Council.
Luke Chignell agrees, adding there were no personality clashes throughout the project.
“All companies really stepped up and we held regular phone calls to ensure we were all on track and on the same page. Because each company is based in different regions around New Zealand those phone calls became very important. The council was great – very responsive, which made it easier from our end.”
Ian Tate identifies the most complex aspect of the project as the spatial adjustments SQL Services needed to make in the SQL 2005 server.
“Intergraph had to write new scripts for all of the Oracle triggers to reflect in the SQL Server. They did a fantastic job; both SQL Servers have about 17 databases running on them and all our application servers are virtualised, so it was no small job.”
“One trigger in particular that needed to be replicated was the one that identified underground pipe connections and kept track of the pipes that were deleted, upgraded or removed. These triggers are in the back end so it doesn’t make much difference to the worker who hits delete to remove a pipe from the system, but when running queries it makes it very clear to us.”
A plethora of benefits
One major benefit, according to Trevor and Ian, is the noticeable speed difference using the web application for GIS.
“The SQL Server is faster which means in a reduction in the amount of time I spend uploading data, which is great,” says Ian.
Trevor further reiterated the benefits, adding that the council now has a consolidated environment for support and maintenance, something that is providing them with additional cost benefits since SQL maintenance is priced per server.
“Because we are running two of the same servers we can link the GIS data with the corporate data – systems like the land information system can now be linked directly through the SQL Servers without having to bypass a third party software system.”
While some of the benefits are difficult to measure, such as the feeling of having a better management system and more streamlined workflow, there are some tangible benefits the council has noticed.
“We fully replaced our hardware, which we would have had to do anyway, but the migration of the GIS application to SQL Server and implementation of the distributed architecture allowed us to save over $20,000 a year,” explains Janet.
A further benefit is the extended features that represent the data in a geographical manner.
“We can now link our GIS data and our property data so that if we wanted to map which properties within the Horowhenua District have a dog, we could. Previously we would have had to export the data from Oracle to XP and then input it into the SQL server – now we can just link it directly, making it less time-consuming,” says Trevor.
“Another aspect that has become easier for us is that we can now use live data – we can write the query once and continually update it rather that exporting the data each time. Again it just reduces the amount of time we have to spend on queries.”
”This has been a great undertaking but has proven very successful for the GIS team,” says Janet.
“The team are most impressed with the live update function for queries and the familiarity of the system – we no longer have to train our staff to use two different servers.”
Facing the future
Going forward, the council is looking to implement the Business Intelligence features of SQL Server 2008 and SQL Server 2008 R2 to deliver considerably more value to their GIS system than they were able to realise with Oracle. By upgrading to SQL 2008 to regain some of the spatial capability, and continuing to improve the system and processes, the Horowhenua District Council will future-proof their GIS system.
For More Information
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to: www.microsoft.com
© 2009 Microsoft Corporation. All rights reserved.
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, and Microsoft SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.