Overview
Background
The School of Geosciences has two databases used to support teaching and administration activity.
The key drivers for this project are to secure the databases, update the database technology, ensure they are centrally supported and benefit from disaster recovery provision.
The two databases are called GEN and ADMIN, and both are on Oracle 11g (11.2.0.4).
The GEN database is a student facing database used to teach spatial data. Students use SQL Plus, Python and PERL to interact and query the GEN database.
The ADMIN database is the 'business administration' database for the school. This database contains 'people' type data about postgraduates and students which is keyed in directly to the system (no feed from central HR). There is a webpage with user profiles generated from data in the database. There is a nightly feed from a CSV file containing EUCLID data about taught student degrees. Some of the data in the ADMIN database is sensitive, for example Staff Grade and research funding.
Computing Officers (COs) currently update and view the database directly. COs will require elevated access as part of the future solution.
The project solution is likely to see IS Technology Management as the Database Administrator for ADMIN and the school as the Database Administrator for GEN. The school will do the user creation. The VMs will be standalone, likely to be 12 VMs in total (3 instances x 2 environments (TEST, LIVE) x 2 locations (KB, AT))
A question is being followed up with IS Production Management about Oracle licensing, specifically whether we can mix Enterprise Edition and Oracle Academy licenses within one instance or whether these need to be separated.
21-Mar-2019 Update from Stefan Kaempf: "It looks like by we are covered by the campus agreement means that we can, but need to pay. Based on that I believe we need to split the db and use the academic licence".
In the future, GeoSciences may look at a PostgreSQL database solution so this project is to provide an interim solution.
Scope
In Scope
- Upgrade to Oracle v12c databases
- Migration of databases to central IS environments
- Set-up of appropriate Oracle licensing / confirm that the academic licence can be used
- Implementation of user access and permissions
- Support of the infrastructure and actual database their selves only
- Creation of support procedures (OLA/SLAs), roles and responsibilities, e.g. for database administration
Out of Scope
- Changes to applications using data from databases
- Changes to data and applications related to the University’s core systems implementation
- Any 'Application' level support beyond the database itself
- Creating the databases on the new 'Stretch Cluster'
- Decommission of any existing infrastructure
- GDPR is not being addressed as part of the Project as it cannot be effectively / safely dealt with until the data migration onto the resilient IS infrastructure is completed, however the works being completed as part of this Project will allow the Business to complete activities thereafter
Objectives & Deliverables
Priority: M = Must Have; S = Should Have; C = Could Have; W = Want
- M = has to be satisfied for the final solution to be acceptable in terms of delivery dates, compliance, viability etc.
- S = high-priority requirement that should be included if possible -workarounds may be available
- C = a nice-to-have requirement
- W = want but will not be part of this project
- O = Objective
- D = Deliverable
| Ref: | Objectives and Deliverables | Owner | Priority |
| O1 | Upgrade the databases from Oracle 11g to Oracle 12c | ||
| D1 |
Technical Architecture Document – upgrade requirements: The GEOSGEN database will be split into 2 databases: GEN AND LEARN(or Teach database that will be created standalone / clean of data - TBC). GEOSADMIN will remain as is. Renaming will follow IS Apps conventions as far as possible. Please note that an earlier delivery of a clean / un-populated version of the LEARN (Teach) database has been requested by the Business and added as a milestone, however it is recognised by the Project Team that this may not be possible and will need review / possible change as the Project progresses. |
Dev Tech | M |
| D2 | All database at Oracle v12c | Dev Tech | M |
| O2 | Migrate the databases to IS hosted environments | ||
| D3 | Complete data migration plan | Dev Tech | M |
| D4 | Virtual Machines (VM's) created / provisioned for each environment/database instance | Dev Tech | M |
| D5 | Complete implementation plan | Dev Tech | M |
| D6 | Complete deployment checklist | App Man | M |
| O3 | Define and implement user access levels and permissions | ||
| D7 | Define and create user access levels and permissions | Dev Tech | M |
| O4 | Define and document the support procedures, roles and responsibilities | ||
| D8 | Agree Service Agreement (SLA) | App Man | M |
| D9 | Agree Operational Level Agreement (OLA) | App Man | M |
Benefits
- Secure centrally hosted environment
- Supported version of Oracle
- Enhanced disaster recovery provision
- Access to bug fixing
- Access to enhancements
- Access to support
Success Criteria
- GeoSciences databases (GEN and ADMIN) provisioned on IS Supported VM's on Oracle 12c instances
- Agreed SLA / OLAs are determined and put in place between the Customer and IS Production for support of the Databases only, no application support is requested / required as part of this Project.
Project Milestones