Contact Us

The first step was to consolidate a number of databases to ensure it was easier to manage in future for the current in-house team. There were 76 databases in total, and all databases were monitored to ensure active databases are consolidated and unused databases were archived and decommissioned. 

The extraction processes every night were taking a very long time (at least 12 hours every night) and when there were any technical issues it was proving difficult to re-run the current processes and to catchup during the working day. These processes were reviewed to understand the root cause and subsequently the processes were changed to ensure it finished in 3 hours instead of 12 hours. This improvement also enabled the process to be easily restarted during a day to refresh data in cubes, reports and dashboard across council in the event of any unforeseen circumstances. 

All databases were migrated from legacy servers of SQL server 2005 to SQL Server 2012. Any unused databases, cube reports and SSRS reports were decommissioned. New reports were set up in line with the requirements from the business users. 

The Sandwell council achieved the following advantages from the project:

• The number of SQL servers was reduced from three to one enabling easier maintenance

• The number of databases as a result of the consolidation resulted in the reduction in the total number to 20 from the previous 76

• The extraction process over night was now taking 3 hours instead of the previous 12 hours 

• New reports/alerts were set up to improve data quality across various datasets and sources