Completion Report

Project Summary:

Background

This project was initiated, as it had been identified, there was a need to provide a more up to date environment for SQL Server based services and bring the SQL Server service into a more resilient and “enterprise” scaled infrastructure. This will allow us to bring the load balancer technologies more into use with these systems to provide the same features (including load balancing) for Windows-based services that we currently provide for our Linux-based services.

In addition, for existing systems or new ones to come in the near future with a requirement to run on latest SQL Server (2014), we need to ensure we are in a position where we have the experience and knowledge of installing and running this version of the software.

This will benefit us by ensuring minimised delay for projects with the need for this version of the software, continued supportable position for the Microsoft database product set within the University and exposure by the technical teams in the install/upgrade and running of the new version with an appreciation of its new feature set for use in other project and support areas. Our current offer of SQL Server 2008, as the primary version of the product, has a final end of live date of June 2019. If the infrastructure is not upgraded our services, such as UNIDESK, would be running on unsupported operating systems and other software and running the high risk that third party products would fail on older version of architecture software and databases.

This project fits within the University Strategy as an Enabler - High-quality infrastructure is key to enabling us to achieve our strategic goals and Strategic Theme of providing outstanding student experience.

Scope

The original scope of this project included the following:

• Decision on the benefits of moving to latest SQL Server version either on premise or externally hosted,
• Procurement of the necessary physical hardware and any required software & licenses.  The procurement process is expected to utilise existing framework agreement and not require a formal procurement exercise. This has to be confirmed with the Procurement Office

• Training of appropriate staff in Development Technology and Technology Management on new technologies introduced by the project e.g. SQL Server 2014
• The establishment of the new database infrastructure following consultancy with ITI Windows Services, IS Applications and Microsoft
• Agreed procedures on migration and support (including patching) of the new database tier
• Selection of one migration of a current SQL database supported by IS Apps
• Best practice for managing application database connections

Deemed out of scope at the start of project

The scope of this project does not include the migration of other databases. It is anticipated that plans for these will be made through a separate project or projects. There will be a requirement for the project team to review estimates and timescales following consultancy and design review stage to assess budget required, aligned to the decision on location of SQL server either on premise or externally hosted.

De-scoped during analysis

The original project brief stated that the project would analysis options for delivery of new infrastructure, as well as implement the build and deployment to Live of the new SQL database tier.

It was evident during the project analysis phase that this was taking longer than expected to complete. It was highlighted the project would be unlikely to deliver agreed infrastructure within 15/16 financial year therefore the decision was agreed to change scope of the project to complete analysis and design phase only and de-scope any implementation activity. This allowed unused budget to be returned to the INF Programme allowing other projects to commence earlier within the existing INF Programme and new project can be initiated in 16/17 for the implementation phase for any new infrastructure purchased.

This resulted in the remaining scope being noted as:

• Decision on the benefits of moving to latest SQL Server version either on premise or externally hosted,
• Procurement / Purchase of the necessary physical hardware and any required software & licenses.
• Final Outcome of project :

1. We will use the existing VM Infrastructure
2. We will have 4 vm hosts (2 AT, 2 KB) we can use for this
3. VMMS with SQL Server can be pinned to these 4 VM hosts, so they never use other VM hosts than these 4. This will mean we have a clear position on the number of cores for licensing.

Analysis of Resource Usage:

Staff Usage Estimate: 100 days

Staff Usage Actual: 52 days

Staff Usage Variance: -48%

Other Resource Estimate: 0 days

Other Resource Actual: 3500 days

Other Resource Variance: 0%

Explanation for variance:

1. PICCL 4 was raised in January 2016 to review the budget position and scope of the project. The original project brief stated that the project would analysis options for delivery of new infrastructure, as well as implement the build and deployment to Live of the new SQL database tier. As the analysis phase took longer to complete and expected delivery of new infrastructure not expected to complete by the end in financial year 15/16, the Project Manager put forward a proposal to protect a proportion of the budget and change scope of this project to complete analysis and design only in 15/16 and return 50 days effort back to the INF Programme. This would allow other INF projects to commence earlier within the existing INF Programme and a new project could be initiated in 16/17 for the implementation phase for new infrastructure. Budget approved for remainder of this project as 50 days. This was agreed with Programme Owner / Project Sponsor / Programme Manager / Portfolio Manager

2. The project originally looked to involve Microsoft from a consultancy perspective however the costs for this activity amounted to c. £25K. We therefore took the approach to bring in an independent local consultancy team, which took longer than anticipated to engage. The project team engaged with the consultancy team XMA, who were suggested by Procurement, who would assist on a pre-engagement basis therefore no charge to the project. The only costs charged were those aligned to the SQL Audit undertaken which was £3.5K.

3. Project Sponsor gave the ‘go ahead’ PICCL 5 to request VM servers to host SQL Server databases. As this is core funded virtual, no cost involved for us only some resource costs associated with build. Agreement for 4 new servers 2 x Test & 2 x Live as a stop gap until new infrastructure agreed and implemented. Development Technology charged c. 2 days effort for this to be completed which was absorbed with approved budget of 50 days.

Key Learning Points:

Useful Insights Gained

• Project completed an analysis of our existing SQL Server estate – this information is now available for our Production Management teams to use as a point of reference.
• SQL Audit undertaken which highlighted best practise. Information provided can be used by Development Services / Production Management for future project / support activities

Considerations for future projects

• For these types of projects, there has to be a business lead formally appointed to drive out the requirements, there were a number of opinions / suggestions raised but no clear directions of what was needed or required.
• At the outset of this project, it was unclear as to what infrastructure was best suited for our purposes, what was available and within our available budget and also differences how IS Applications would like to engage with infrastructure (on premises, hybrid or Cloud) to that which is available now or in future from an IT Infrastructure perspective.
• Consultancy recommendation and associated server costs (c. £35K – this was not a formal quote) were delayed due XMA being unable to submit a quote for the infrastructure –XMA are not on the existing framework for this type of infrastructure therefore due to an agreement with the University to use Esteem for purchasing Dell or HP via an existing agreement via ITI
• ITI review of consultancy report and license restriction following recommendation from XMA provided 3 options as they advised that the costings of the XMA/SuperMicro offering were already more expensive than that from our current suppliers and do not take into account VMware license and support costs :

1. To build a completely separate VMware cluster as per the XMA specification - £73.7K total (not including IS staff costs which would be additional to existing support cost (0.1FTE), storage, backup, networking etc.)
2. To build a completely separate cluster as per our current environment to offer the usual level of resilience and redundancy: £134.4K total (not including IS staff costs which would be additional to existing support cost (0.2FTE), storage, backup, networking etc.)
3. To add additional capacity to the existing VM estate: £28.3K total (not including IS staff costs but this would be minimal through existing operational support, storage, backup, networking etc.)

• On agreement to move forward with Option 3 (as this was within available budget £50K) the key blocker for IS Applications was aligned to Microsoft SQL Enterprise Licenses costs. On further investigation ITI advised that option 3 would not be achievable due to the expensive license costs involved therefore only option available to us to achieve any expansion would be to purchase VMware, VEAAM backup and a single server to extend existing VM infrastructure.
• Due to time constraints to spend funding before end of July 2016, there was a decision taken via email by Project Sponsor and advised to Team Leads Dev Tech and Tech Man to agree with a version of Option 3 which seemed to be open to interpretation and this led to a degree of frustration within the core project team unfortunately this was the only option available to go forward with.
• The project had ITI representatives involved from the outset on the project however it was not until XMA recommendation report and costs for infrastructure that any limitations/restrictions were highlighted – this was frustrating for the project team that they were not able to inform or raise internally within their own area to provide key details back to the project at an earlier stage.
• Moving forward it would be beneficial for engagement with ITI and how IS Applications Development Technology and Technology Management teams can gain skills / training for broadening their experience on cloud based solutions – possible option is to submit a proposal via Innovations Funding to try new options for development.

Outstanding issues:

• The project produced a draft Technical Architecture Document which has information to be confirmed by IT Infrastructure e.g.. server names, exi host, server specification. This information will be required for Development Technology team to allow them to plan what VM can be moved and for changing the licensing model to “per core” licensing. The draft version of the TAD was agreed by Iain Fiddes and Graeme Wood to allow the project to close.
• The servers have still to be installed by ITI which may have an impact on new projects currently being planned e.g. ACS036 Upgrade KX Database Infrastructure
• A new INF project or BAU support activity to be initiated to have 4 VM hosts (2 AT, 2 KB) built for IS Applications SQL Service on existing VM infrastructure. The VMS with SQL server can be pinned to these 4 VM hosts, therefore restricting to these VM hosts. This will mean that IS Applications have a clear position on the number of cores for licensing.