Overview

  1. Overview

The scope of this project will be to build a new virtual MySQL environment, in order to maintain O/S support.

Our current MySQL database estate currently resides on the same infrastructure as Oracle 11g, running on Linux Redhat 6 servers. The Oracle databases are in the process of being migrated to Oracle 12c and onto new Infrastructure running on Linux Redhat 7. Redhat Linux 6 will be end of life (production) on November 30th 2020.

(https://en.wikipedia.org/wiki/Red_Hat_Enterprise_Linux)

We therefore, minimally, need to build a new environment on a later version of Linux and upgrade the version of MySQL available. The new environment should be built on VMware, in accordance with our enterprise infrastructure platform strategy.

At the same time we are intending to migrate from MySQL to MariaDB (https://mariadb.org/). MariaDB is an “identical” clone of MySQL. This has some potential advantages, such as saving up to £30k year in Oracle licence costs, and moves the University towards a better supported open source community. This option will be fully investigated as part of the project design phase.

In addition, the project will create a new VM infrastructure, to host the shared file storage tier for Drupal. This is not strictly tied to the MySQL migration, but there are a number of advantages in doing the work in conjunction with MySQL migration.        

The project will build the new platform in advance of the final end-of-life support so that business-driven projects can migrate applications as part of follow-up projects.

There are an estimated 10-30 application that use MySQL, including Microservices. The project will migrate at least one smaller application that uses MySQL, in order to prove the new platform and establish it as part of a live service.

Additional application migrations will be included in scope, should there be sufficient budget and time. This is based upon the learning experience of the recent LAMP upgrade project and considered to more efficient approach than separate projects, keeping focus where there is hard deadline to exit the existing infrastructure. The major application using MySQL is the University Website (www.ed.ac.uk). This is a priority one service. 

 

 

  1. Scope

The scope of this project is to undertake the following activities:

  • Create an authoritative register of all applications that use MySQL.
  • Create an effective and agreed design for a new environment & infrastructure to replace the existing MySQL Linux Redhat 6 stack, based upon MariaDB, and associated file share system.
  • Commission, build and implement the new environment & infrastructure.
  • Migrate and deploy a pilot application database onto the new environment & infrastructure.
  • Optionally, migrate and deploy further application databases, including the University Website.
  • Known bugs in existing applications will not be remediated. Only those arising from migration will be resolved

This scope is further broken down in the objectives and deliverables section below.

  1. Objectives and Deliverables

O

D

Outputs / Associated Deliverables  

MoScoW

Primary Responsible

Also Involved

O01

 

Define Scope

 

 

 

 

D1

Application Register

Authoritative Register of all applications that use MySQL hosted databases and associated file shares and that are supported by IS Applications. Will inform design.

Must

Tech Man

 

Dev Tech Soft Dev Apps Man  

 

D2

Preliminary validation of MariaDB as the chosen option to replace MySQL, and authorisation to proceed to the design & build phases.  

Must

Dev Tech

Soft Dev

Tech Man

O02

 

Infrastructure Design  

 

 

 

 

D3

Technical Architecture Design

Design will be based around a MariaDB solution, on VMware.

 

Design will include a VM file share system, for use with Drupal

Must

Dev Tech  

Tech Man

 

 

 

 

 

 

O03

 

Infrastructure Build

 

 

 

 

D4

Non-LIVE Platforms

(Potentially DEV, TEST, TRAIN, STAGING – tbd as part of design)

Must

Dev Tech

Tech Man

 

D5

LIVE Platform  

Must

Dev Tech

Tech Man

O04

 

Infrastructure Handover  

 

 

 

 

D6

Supporting collateral

E.g. deployment checklist items, support documentation.

Must

Dev Tech

Tech Man

O05

 

Database Migration

 

 

 

 

D7

Migration of one small pilot application database.  

Must

Dev Tech Soft Dev

Tech Man

 

D8  

Migration of one small pilot application file share  

Should

Dev Tech Soft Dev

Tech Man

 

D9

Migration of all further application databases / file shares, including the migration of the University website CMS DB. This is a priority one service. 

 

Should

Dev Tech

Soft Dev

Tech Man

O06

 

Infrastructure Tidy-Up  

 

 

 

 

D10

Tidy up DEV / TEST / LIVE MySQL Linux 6 platforms.

 

Dependent upon: - - Migration of all hosted MySQL DBs

Could

Dev Tech

Tech Man

 

  1. Out of Scope

 

What

Details

OS-01

De-commission of DEV /TEST / LIVE  Linux 6 Platforms used by MySQL / file shares.  

Highly likely that Oracle will still be running on these shared platforms. 

OS-02

Termination of Oracle MySQL support contract  

Part of normal service support.

OS-03

New support contract for Maria DB

Based upon the use of the existing Oracle support contract, this is considered not to be necessary.   

OS-04

Other uses of MySQL Database servers, outside the scope of IS Applications support.

 

There may be other MySQL DB servers outside the scope and control of IS applications. These will not be assessed or migrated. 

OS-05

Migration of MySQL to the stretch cluster is considered to be out of scope 

- We not want to use it for the database servers for architectural reasons (Drupal needs a primary and secondary to keep the main website running in read-only mode during deployments).

- Not that simple to move it either as we’d likely use the secondary site for backups and such.

- Unlikely to be ready in time.

 

  1. Benefits

 

What

Details

BE-01

Maintenance of compliant infrastructure, in accordance with supplier release roadmaps and strategies.

 

 

 

 

 

  1. Project milestones

Milestone

Date

Governance Milestone?

Details

M1: End of Planning  

14-Nov-2018

Yes

Was: 14-Nov-2018

M2: Application Register Complete  

11-Jan-2019

No

 

M3: Infrastructure Design Complete  

01-Feb-2019

No

 

M9: Acceptance Sign-off  

09-May-2019

Yes

 

M11: Infrastructure Build Complete  

14-May-2019

No

 

M12: Delivery sign-off  

21-May-2019

Yes

Was: 19-Apr-2019

M13: Project Close  

18-Jun-2019

Yes

Was: 03-May-2019

 

 

 

 

 

  1. Priority and Funding

This is a compliance project, with a budget of 100 internal resource days.

The project has identified an amber risk that this is not sufficient funding to cover the project stated objectives. It has been agreed with the project sponsor to review the budget at the end of the design phase when more detail will be understood.   

There is no provision for external funding.

  1. High Level Work Breakdown Structure
 

Phase

Activity

Task

A

Project Planning 

 

 

A01

 

Create Project Brief & Other Artefacts

 

A02

 

End of Planning Sign-off

 

B

Application Register

 

 

B01

 

Create Application Register

 

B02

 

Review and sign-off Register

 

B03

 

Validation of MariaDB as the chosen option to replace MySQL.

- Ensure all 3rd party applications are certified to work with MariaDB.

- Assessment of each bespoke application.  

- Agreement from senior stakeholders before proceeding. 

 

B04

 

Review & Agree use of MariaDB

 

B05

 

Select Pilot Application

 

C

Infrastructure Design

 

 

C01

 

Create TAD

 

C02

 

Review and sign-off TAD

 

C03

 

Commission virtual servers with ITI

 

D

Non-LIVE Infrastructure Build

 

 

 

D01

 

Build Environments

 

D02

 

Build Peer Review

 

D03

 

Build Sign-off

 

 

 

 

 

F

LIVE Infrastructure Build

 

 

 

F01

 

Build Environment

 

F02

 

Build Sign-off

 

 

 

 

 

G

Pilot Application Migration

 

Will happen in conjunction with environment set-up.

(We will not build test until confirmation that the pilot application works on dev.)  

 

G01

Development

Migrate to DEV

 

G02

 

Unit Test & Sign-off

 

G03

 

Migrate to TEST

 

G04

 

Perform System Test

 

G05

 

Perform Performance TEST

 

G06

UAT

Create UAT Plan

 

G07

 

Perform UAT

 

G08

 

Acceptance Sign-off

 

G09

Deployment

Deployment preparation

 

G10

 

Deployment Sign-off

 

G11

 

Migrate to LIVE

 

G12

 

LIVE warranty period

 

G13

 

Handover to BAU

 

H

Other Application Migration

 

(iterate)

H01

 

 

 

….

 

 

 

H13

 

 

 

I

Infrastructure Tidy-up

 

 

I01

 

Tidy-up DEV / TEST / LIVE MySQL Linux 6 platforms.

 

J

Project Close

 

 

J01

 

Project Completion Report

 

J02

 

Project Close

 

(WBS will act as input into the estimating workshop).

  1. Impact and Dependencies.

 

What

Details

D01

Selection / Timings of Application Migrations

The selection and migration of application DBs may be dependent on other project activity and change freeze periods.

 

For example, for the University website we would want to avoid a MySQL migration happening at the same time as the existing planned PHP upgrade.

 

  1. Pre-Requisites

 

What

Details

PR1

No specific pre-requisites

 

 

  1. Assumptions

 

What

Details

A01

MariaDB is compatible with all our current use cases and management procedures.

MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation.[5] Contributors are required to share their copyright with the MariaDB Foundation.[6]

 

MariaDB intends to maintain high compatibility with MySQL, ensuring a drop-in replacement capability with library binary parity and exact matching with MySQL APIs and commands

(https://en.wikipedia.org/wiki/MariaDB)

 

  1. Constraints

 

What

Details

C01

Budget

The delivery of application migrations will likely be constrained by the project budget.

 

 

 

 

  1. Risks

 

Title / Description

Initial Probability* / Impact**

Management Approach /

Owner / Mitigating actions

R01

Incomplete Application Register

There is a risk that the application register is incomplete, with minor applications missing.  

Low / Medium

REDUCE / Bill Lee /

1) Conduct thorough peer review of register.

R02

Jump to later version of MySQL causes unexpected issues. There is a risk that migration to a newer version of MySQL (actually MariaDB) causes significant functional and non-functional issues.

 

Medium / Moderate

REDUCE / Chris Lawford /

1) Ensure adequate testing is performed.

2) Pilot using 1 non-critical application before

R03

Migration to MariaDB causes unexpected issues

There is a risk that migration to MariaDB causes significant functional and non-functional issues.

 

Since we are on an older version of MySQL, the primary risk is considered to be the move to a more up-to-date version MySQL.

Whether this is actually MySQL or a clone such as MariaDB is considered to be a secondary and lesser risk.

 

Low / Moderate  

REDUCE / Chris Lawford /

See above.

R04

Migration to VMware causes unexpected issues. There is a risk that Migration to VMware causes issues.

 

We may expect non-functional issues (particularly performance) to arise when moving to a virtual platform.

Medium / Moderate

REDUCE / Chris Lawford /

1) Ensure VMware environments are adequately sized.

2) Undertake performance testing versus a known baseline, to measure any degradation in performance is within acceptable boundaries.

 

R05

Insufficient Budget

There is a risk of lack of budget

 

It is unlikely that there is enough budget to cover all the must / should / could items.

High / Moderate

REDUCE / Stefan Kaempf /

1) Ensure MUSTs are covered, escalate as soon as overrun is forecast.

 

 

 

 

* Probability is rated Very Low/Low/Medium/High/Very High ** Impact is rated Insignificant/Minor/Moderate/Major/Severe (Risks will be updated on the projects website at the project brief approval stage.)

  1. Stakeholders & Communication Plan

Who

Brief Sign-off Required?

Role

Communication Plan

Cord, Chris

Y

Tech Mgmt Project Team Member

Project Team

Cropley, Geoff

N

Project Manager

N/A

Filalithis, Stratos

N

Head of Website & Communications Technology

Senior Stakeholder

Franceschi, Maurice

Y

Programme Manager

Senior Stakeholder

Gray, Tim

N

COM Programme Manager

Senior Stakeholder

Harris, Riky

Y

Dev Tech Lead Project Team Member

Project Team

Hobden, Andrew

Y

Apps Mgmt Lead Project Team Member

Project Team

Kaempf, Stefan

Y

Sponsor

Senior Stakeholder

Lang, Mark  

N

Dev Tech Team Manager

Senior Stakeholder

Larnach, Heather

Y

Tech Mgmt Lead Project Team Member

Project Team

Lawford, Chris  

Y

Project Manager (acting)

N/A

Lee, Bill

Y

Soft Dev Team Manager Project Team Member*

Project Team

Perera, Suran

Y

Apps Mgmt Lead Project Team Member

Project Team

Wadee, Adam

N

Portfolio Manager

Senior Stakeholder

Wardrop, Billy

Y

University Web-site Lead Project Team Member**

Project Team

* Other software development resource to be assigned, as required. ** To be included as appropriate.

 

 

Communication Plan

Details

01

Senior Stakeholder

- Receives Monthly Project report

- Receives regular updates from Programme Manager  

02

Project Team  

- Attends regular team meetings

03

Interested 3rd Party  

- Receives Monthly Project report

(To be transferred to project website upon approval of brief)

 

 

  1. Document Version Control

Version No

Contents

Date Created

Author

Comments

V0101

1st Draft

22/10/2018

Chris Lawford

 

V0102

Updated draft

15/11/18

Chris Lawford

Updated with feedback from project team.

 

 

 

 

 

 

  1. Document Sign-off

Name

Date Signed Off

Comments?

Cord, Chris

 

 

Franceschi, Maurice

22/11/2018

 

Hobden, Andrew

13/11/2018

 

Harris, Riky

20/11/2018

Verbal approval. Comment below.

Kaempf, Stefan

20/11/2018

Verbal

Larnach, Heather

-------------------

On holiday. Stefan has signed off.

Lawford, Chris

22/11/2018

 

Lee, Bill

19/11/2019

Yes, see below.

Perera, Suran

 

 

Wardrop, Billy

14/11/2018

 

Wheavil, Adam

15/11/2018

 

 

 

 

  1. Document Revision Details

Version No

Comment / Response

Date

Who

Document Updated?  In Version? When?

V0101

RH: Just a few notes. In the Brief: Section 1:

“At the same time we are intending to migrate from MySQL to MariaDB (https://mariadb.org/). MariaDB is an “identical” clone of MySQL, and has the advantage of saving up to £30k year in Oracle licence costs.”

Technically we could save money by using the Community Edition of Oracle MySQL too, we’re investigating switching to the MariaDB fork as it’s better supported by the open source database community. I think more important is that we mention that we want to upgrade our database version as part of the project, no matter the variant.

(CL 19/11) Text updated.

 

“Moving to the stretch cluster where appropriate is also within scope; however it is believed that the stretch cluster will not be ready in time. It is considered to be a simple “lift and shift” operation that can be done at a later date.

This isn’t quite right. I gather that the Metro stretched cluster does exist and is being used, but it hasn’t yet migrated to use the new 10Gbps network. However, not only do we not want to use it for the database servers for architectural reasons (Drupal needs a primary and secondary to keep the main website running in read-only mode during deployments), but it wouldn’t be that simple to move it either as we’d likely use the secondary site for backups and such.

 

Section 3 (and 8):

Not a big deal, but for Objective 3 we’ll also be building TRAIN and STAGING platforms too if my TAD is approved that way.

(CL 19/11) Text updated.

 

I see how it’s only Medium probability that there’s insufficient budget – that seems optimistic to me when we’re already estimating above that before we include migrations ;-) (CL 19/11) Risk probability uplifted.

----------------------------------------------------------------

CL: Riky, thanks for the early feedback. I will process and get back to you. Would you prefer we move to the stretch cluster is moved to an out-of-scope item – sounds like it to me…?    

And good that you have managed to push ahead with the TAD!   

--------------------------------------------------------------------------------------------------------

RH: If it were up to me, I wouldn’t have any mention of the Metro stretched cluster in the brief at all. If it’s out of scope it sounds like we want it, but can’t do it due to time limits or something like that, but we actually don’t want to do it at all because it’ll stop our most visible service from working.

Thanks, Riky

(CL 19/11) Migration to stretch cluster moved to out-of-scope, for clarity.

 

13/11

Riky H

Y / V0102 / 19/11  

V0101

It all looks fine to me.

Andrew

 

13/11

Andrew H

 

V0101

Hi Chris I’ve had a look through the projects material and I’m happy to sign it off from my perspective.

Thanks

 

14/11

Billy W

 

V0101

Hi Chris, I'm happy with the proposed brief/plan. Adam

15/11

Adam W

 

V0101

RK (13/11): There's something I wanted to highlight as part of the INF143 project that I don't think has been raised so far. The Oracle 11g physical servers currently host the shared file storage tier for Drupal (which is presented as the 'uws-content' Samba shares). While it's not technically in the scope of INF143, I'm assuming we will need to migrate this before the servers are decommissioned, and I imagine there will be a strong desire from Production Management to remove all ties between EdWeb and the Oracle database servers before that.

Two options I would suggest:

1) Build five dedicated small Puppetised VMs on the Metro stretched cluster to solely serve this shared content (matching the python-fs-* and lamp-fs-* servers). I'm keen to have one-per-tier as having multiple tiers (TEST, STAGING and TRAIN) all share the same TEST servers has introduced unnecessary complexity into the EdWeb estate.

2) Use the new 'drupal' set of "MySQL" database servers to be created for INF143 to host the content and use a regular rsync cron job to keep the AT servers updated to handle site loss.

While option 1 will be a little more expensive in server costs (and maybe slightly more effort, but the Puppet DSL already exists to configure these), it would be my preference to use the Metro cluster and avoid the complexity and Recovery Point Objective (data loss) hit of using rsync.

(CL 19/11) currently under review

---------------------------------------------------------

(CL 20/11) Outcome of resultant review meeting: 

1. It was agreed to include the migration of the Drupal file shares into the scope of project INF143.    

 

2. Of the two options proposed, we have agreed to go with Option 1. This the clearly preferred technical & architectural solution, maybe slightly more expensive in terms of build/test costs.

 

Option 1: Build five dedicated small Puppetised VMs on the Metro stretched cluster to solely serve this shared content (matching the python-fs-* and lamp-fs-* servers). I'm keen to have one-per-tier as having multiple tiers (TEST, STAGING and TRAIN) all share the same TEST servers has introduced unnecessary complexity into the EdWeb estate.

 

Option 2: Use the new 'drupal' set of "MySQL" database servers to be created for INF143 to host the content and use a regular rsync cron job to keep the AT servers updated to handle site loss.

 

19/11

Riky H

N / V0103 / nn/11

V0102

Tim G: Can you update the brief to make clear that the migration is that of the University’s Web CMS DB and that this is a priority 1 service.

19/11

Tim G

Y / V0103 / 19/11  

V0102

Hi Chris,

Brief looks ok apart from deliverable D2 where I would consider the primary responsibility will need to be a shared one between DevTec and DevTeam.  If you can update accordingly then I’m happy to sign this off. Thanks Bill

19/11

Bill L

Y / V0103 / 20/11

 

 

 

 

 

(V0103 Pasted 22/11 @10:39)

Project Info

Project
MySQL Environment Migration
Code
INF143
Programme
ISG - IS Applications Infrastructure (INF)
Management Office
ISG PMO
Project Manager
Frankie Quinn
Project Sponsor
Suran Perera
Current Stage
Deliver
Status
In Progress
Project Classification
Run
Start Date
24-Sep-2019
Planning Date
02-Nov-2018
Delivery Date
21-Sep-2021
Close Date
24-Sep-2021
Programme Priority
4
Overall Priority
Highest
Category
Compliance

Documentation

Plan