How SQL Server optimisation transformed a UK Housing Association
Growing challenges
One of the UK’s largest and oldest housing associations runs a number of important internal and resident-facing applications, including an online resident portal, a CRM system, and mobile working solutions for managing property visits and maintenance. These applications draw data from a number of databases running on Microsoft SQL Server.
Over time, the complexity of the underlying databases and their stored procedures had increased significantly. However, neither the SQL Server software nor the server hardware on which it was running had been updated for a number of years, and this was having a negative impact on performance and availability.
“Our systems are not meant to be there to annoy, they’re meant to help,” says the organisation’s head of design and development. “When our team is out visiting a resident to inspect their property or carry out maintenance, they need to have correct and up-to-date information at their fingertips.”
Go-slows and time-outs
Processing bottlenecks within the SQL Server environment were often causing overnight data transfers to time out or fail, which meant that the technical team had to scramble to re-run jobs first thing in the morning. This not only meant that field employees might not have the latest data, it also caused significant stress for the applications team.
If the CRM system lacked the latest data, call centre staff might not be able to resolve a resident’s query about whether their rent payment had been received, for example. And if updates to a mobile working solution failed, a maintenance team might not have access to details of past repair work. While the issues would nearly always be resolved within a couple of hours, it was a pain and a nuisance for all involved.
“As our requirements grew, time-outs were a more frequent issue,” recalls the spokesperson. “Fortunately, there wasn’t a huge impact on residents or on our teams in the field – it was more of a background problem. For the technical team, it was tiring and stressful to be rushing about trying to resolve problems. The 7:00am to 9:00am period was often a nightmare for us.”
Even diagnosing the issues was challenging. The database landscape included large numbers of poorly documented and maintained stored procedures, some of which were calling databases that no longer existed. Many databases were receiving too many requests at the same time, leading to performance bottlenecks and time-outs during overnight processing. In addition, the departure of a long-standing, key team member with deep skills in integration and SQL Server ratcheted up the pressure on the team.
“We knew we needed some external help and expertise because we no longer had the internal resource that we needed to stay afloat, let alone to optimise our environment,” says the spokesperson. “It was clear that we needed to upgrade and update our SQL Server landscape, and also to rationalise and optimise the architecture including the stored procedures, ETL processes and SQL queries.”
Professional and intelligent
The housing association was aware that Northdoor were well-known within the technology industry as expert outsourced SQL DBA service providers, who possessed the technical skill and expertise to deliver the services required. Armed with that knowledge, the organisation asked Northdoor to guide the transformation of the SQL Server landscape.
“Right away, we appreciated Northdoor’s professional project management and ability to quickly understand what we wanted to achieve,” says the spokesperson. “I’ve worked with other solution providers where I ended up constantly needing to describe what I wanted, to keep them productive. With Northdoor, we never had to explain anything twice and we could get on with our day jobs while their consultant executed the work. Rather than micro-managing, I could just explain the business goal, and Northdoor would then determine what needed to be done. It very quickly became a relationship where we trusted Northdoor to make the right decisions.”
Transformational work
Northdoor executed a full programme of modernisation and optimisation of the housing association’s data platform, migrating multiple outdated versions of Microsoft SQL Server to a single, consistent, up-to-date platform running on new hardware. Legacy versions of DTS packages containing old VBScripts were modernised to the latest version of ETL processes to take advantage of the new technology. As part of this work, Northdoor also optimised stored procedures and SQL agent jobs to enhance performance, pruned obsolete jobs, and resolved other historical issues to significantly improve reliability.
In architectural terms, Northdoor assisted the housing association’s decision to migrate and consolidate multiple databases running on multiple SQL Server instances onto four VMware virtual machines, rationalising the workload to ensure an even balance and thereby minimise the potential for unmanageable spikes in demand. Key applications are now pulling data from two sources: the central housing management solution and the Microsoft Dynamics CRM.
Modernising software development processes
Northdoor also helped the housing association to adopt a more modern and agile approach to software development through the deployment of a DevOps foundation on Azure and the introduction of formal source control. Introductory training in Azure DevOps processes was delivered to key staff.
The spokesperson comments: “Our change control and release management were not as tightly managed as they should have been. Working with Northdoor, we’ve formalised our approaches and significantly increased the quality and repeatability of our work. We’re now up to speed with DevOps, and we’ve built CI/CD pipelines that will really increase the speed at which we can deliver new functionality to employees and residents.”
To complement the work of internal teams and provide additional cover and added capacity, the housing association has engaged Northdoor to provide a fully managed support service for Data Platform, which includes routine maintenance of the SQL Server landscape backed by proactive monitoring and issue resolution. Northdoor also provides ongoing strategic consultancy for changes and improvements.
Smooth transition
Northdoor ran rehearsals to ensure that go-live for the new SQL Server landscape would run as smoothly as possible, which enabled the housing association to achieve its transformation without any negative impact on users.
“The new environment made an immediate difference: we noticed that everything was running much faster and better,” says the spokesperson. “Normally with IT projects, there are a few teething problems, but in this case there was just a clear boost in performance and availability.”
Performance enhancements
Stored procedures that previously took 2.5 hours are now completed inside 40 minutes, which has contributed to a ten times improvement in the performance of the overnight CRM refresh.
“We now have considerably fewer time-outs and a much higher performing environment,” says the spokesperson. “For my team, that means considerably less stress and more time to focus on delivering new functionality, rather than firefighting. And for users and residents, it means applications that run faster, more reliably, and with the latest data. Northdoor optimised our environment to really make things sing!”
Growing in confidence
The improved performance and reliability are vital elements in enabling the housing association to continue growing.
“Thanks to the work we’ve done with Northdoor, there’s no longer any fear about scaling up our technology to meet our growing business needs,” says the spokesperson.
The refreshed data platform from Northdoor gives the housing association confidence that its key systems will scale to significantly larger future needs. Simultaneously, the increased reliability and the ongoing support from Northdoor empower the internal team to focus on essential projects to improve service to residents and employees.
“Northdoor completely fitted in with our internal team and combined professional competence with a friendly, can-do attitude,” says the spokesperson. “We trusted Northdoor to use their time appropriately to deliver the results we were seeking, and that’s exactly what happened. Like every other organisation, we face increasingly challenging timelines and budgets, and external skills of course come at a premium. But with Northdoor we feel that it has definitely paid to get the experts in.”
