DEWR's director of technology services Brynten Taylor said that despite the problem he is enthusiastic about the upgrade, which has taken place over the last six months.
DEWR had been using Microsoft's SQL Server 2000 software and decided to get involved in Microsoft's early adopter programme. Speaking at the Australian launch of the software last week, Taylor said that apart from the small matter of having to maintain one piece of legacy hardware to handle tricky scripts, the upgrade was "seamless".
"We've seen mainly that it's a seamless upgrade, it goes straight in... As far as the databases themselves are concerned, we've had no major issues going forward," said Taylor.
However the executive admitted he had Microsoft SQL experts on call for the project, and warned those thinking of following a similar upgrade path to be careful when migrating the Data Transformation Services (DTS) packages, which were first introduced in SQL Server 7.0.
"The only part we've left as legacy is some of our DTS packages. We're still running on a 32-bit server to give ourselves a bit more time to sensibly migrate," Taylor told ZDNet Australia .
The DTS package feature of SQL Server allows admins to combine several database-related tasks into one process and use a variety of common programming languages to execute them.
"The only part of the product you've got to be careful with is DTS packages... In fact most of that is seamless as well, but of course developers always think they know better than everyone else, and they'll slightly shape their package in a certain way, which means it won't run on 64-bit SQL as distinct from 32-bit SQL," said Taylor.
Taylor said that being part of Microsoft's early adoption programme was beneficial for the department.
"Obviously whenever you early adopt something, you build a close relationship with the provider of the software... to make sure that any issues could be resolved very quickly," said Taylor.
"We didn't have to call on them that often ... but we have got access to some very clever SQL people. It has been a relatively seamless migration, but we've had a very high level of expertise sitting on the floor."
Running out of memory
The impetus for the upgrade, Taylor said, was simple: his team could see that the previous version of SQL Server would not be able to handle the additional load caused by new federal government initiatives that were announced in the 2005 budget.
"About six months ago, when the government's Welfare to Work initiatives came out and hit the press, we knew we had to do a lot of work... We got some very rough metrics that we would actually double our transaction load, and we knew our SQL Server 2000 databases were getting towards the higher end," said Brynten.
Taylor said the 64-bit nature of SQL Server 2005 allowed the department to escape the memory restrictions imposed by the previous 32-bit software. One of the main advantages of 64-bit software is that it allows programmers to develop applications that can use significantly more memory than they could in the 32-bit world.
However, the improved speed of the new software also proved a benefit, with the department enjoying significant gains in processing data cubes -- needed to hook business intelligence software into databases.
Not all of the speed gains came from the software though -- the department now runs SQL Server 2005 on Itanium servers with eight CPUs each. "We've got some pretty heavy hardware lying behind [the software]," said Taylor.
The technical director was also impressed by improvements in SQL Server 2005's usability.
"It's been a problem in the past - you had to be an SQL expert to see where the problems were," he said. "They've made that a lot easier. Still, no Joe Bloggs off the street can do it, but they've lessened the technical expertise required."
Taylor said alternative databases from vendors like Oracle or IBM weren't considered for the project due to its tight time frames -- upgraded software from the same vendor was more convenient in this case.