FY09 Tactical Plan for Database Services\
Prepared by J.Trumbo\
June, 2009

Docdb 2889

**Databases Goal**

Continue to provide a stable, professional operation of laboratory
scientific and information systems databases, providing maximum uptime
and technical support.

**Databases Strategy**

Develop stronger expertise and support for open source databases,
reducing the dependency on Oracle, with a goal of reducing development
and support costs.

**Tactical Objectives for FY 09**

1\. Database & Systems Operations

Support running experiments at appropriate levels. Maintain a database
uptime per SLA. The % rate for uptime on mission critical production
databases will vary depending on SLA and their commitment to up-time. In
addition, the system hardware will also help dictate up-time needs. All
databases will follow the baselines written, and we will provide support
to the database user community to help insure a stable, performant, and
smooth running environment.

List of Activities:

-   DATABASES & INFO MANAGEMENT / Database Administration / Scientific

-   DATABASES & INFO MANAGEMENT / Database Administration / Astro

-   DATABASES & INFO MANAGEMENT / Database Administration / Information
    systems

2\. Database Support.

Promote Postgres and Oracle as a 1^st^ tier database and MS-SQL and
MySQL as second-tier databases. Understand the pros & cons for Oracle
and Open Sources database options, gather information about what the
customers needs and select the appropriate database as per the findings.
The legacy systems will proceed with Oracle and there may be an occasion
for new experiments to use Oracle, but an analysis regarding appropriate
database technology will be performed for all new customers.

a\. Oracle Support

Support production, integration and development Oracle database
information systemss. Configure, tune, test, and maintain databases.
Coordinate effort with application team for reviews of data modeling and
database queries. Develop tools for operating, and monitoring of
databases. Perform and Define Oracle database backup and recovery.
Install and configure Oracle Internet Directory (oid), for Oracle v10
dbs.

b\. Open Source Database Support.

Support production, integration and development open source database
information systems. Configure, tune, test, and maintain database.
Coordinate effort with application team for reviews of data modeling and
database queries as necessary. Develop/procure tools for operating and
monitoring of databases Perform and define database backup and recovery.
Attain expertise on open source databases deployment.

-   DATABASES & INFO MANAGEMENT / Database Administration /
    Tools_development

3\. Agile response to new and rapid shifts of responsibilities and
demands accompanying the end of Run II data taking and reduction of
effort in the Run II experiments.

4\. Kerberize Oracle using Advanced Security Option.

5\. Maintain expertise for new features. Work with databases on vm
environments, oracle rac testing, etc.

7\. Complete upgrade to Oracle 10 for the information systems databases.

8\. Upgrade information systems hardware for databases, including major
apps, computer security, 24.7 applications and nimi development.

9\. Continued Deployment of the 3par San technology, a high availability
database disk storage array. Continue testing, standardization and
configuration of 3Par product to optimize additional hosts and database
instances. Move additional instances to the 3par that are currently
running on aging arrays and/ to meet the space needs on development
databases. Would like to investigate the potential of using Oracle
snapshots on 3par as a substitute for rman backups. Potentially, this
could mean significant savings in disk usage. On 3par the Oracle
database and open source database can be hosted.

10\. SDSS

In FY09, both DR7 and DR8 will be loaded and released.    DR7\
will go public by June 30, 2007.  DR8 will go public by October 31, 2008
and will be the final data release.  DR7 will incorporate all final data
model and schema\
changes, as we won\'t have time between DR7 and DR8 for any additional
changes.

Continue using Idera to monitor and track performance.   Produce
quarterly summary reports regarding status of systems and uptime metrics
per mou.

Support the transition from SDSS operations to long-term maintenance and
stewardship of the SDSS archive. Activities will include updating all
documentation associated with loading and hosting the SDSS SkyServer and
CAS system. Also help the JHU and UC science libraries with the
operation and maintenance of CAS\
clusters for hosting DR5.

-   ASTRO / SDSS II / Data Distribution Operations (SSP 268)

11\. DES

Fermilab has been asked to host an Oracle standby database for the Dark
Energy Survey. This database will be used for scientific queries as well
as failover from the master database hosted in Champaign, Il. Current
thinking is to implement this database under Oracle rac, as the master
is implemented, insuring high availability. DBI-DBA needs to train in
rac and auto storage management techniques to accomplish this goal.

**Database Support**

**Oracle Support \* indicates may not have time to do**

-   Activity type: Fy 09 project 20 fte months maintenance + 19 fte
    months projects = 35 fte months total

-   Milestones:

    a.  Oracle general support

```{=html}
<!-- -->
```
-   Install, configure, and test new Oracle databases, perform version
    upgrades of existing databases. 12.0 fte months

-   Administer and operate production and development Oracle database
    information systems. 1 fte month

-   Participate in a 24x7 on-call rotation schedule for production
    issues.

-   Perform Oracle database backups and data restores. .5 fte months

-   Disaster recovery for Oracle databases. .5 fte months

-   Replication support 1 fte month

-   Diagnose problems, and monitor database performance. .5 fte months

-   Perform tuning of SQL queries. .5 fte months

-   Management support 4 months

    a.  Install and configure Oracle Internet Directory (OID) for Oracle
        v10 dbs. Dependant on hardware delivery. 4 fte months

    b.  Configure and implement 5 new d0 online database boxes to
        replace current d0 online, test new cluster software and
        failover. 1.5 fte months

    c.  \*Configure and implement new cdf offline production box. Reuse
        current production box for development replacement. Dependant on
        hardware delivery. 1 fte month

    d.  \*Configure and implement new d0 offline production box. Reuse
        current production box for development replacement. Dependant on
        hardware delivery. 1 fte month

    e.  Begin planning upgrade of Oracle databases to 11g. .5 fte months

    f.  Begin upgrading Oracle databases to 11g. 4 fte months

    g.  Design and implement ITL asset management database. Scope
        undefined and unknown. 4 fte month .

    h.  Implement ust1_fts, the cms database on new hardware .5 fte
        months

    i.  Move the esh dev/int/prd database to a Unix platform from
        Windows. 2.5 fte months

```{=html}
<!-- -->
```
-   Metrics:  uptime statistics, performance

**SqlServer Support**

-   Activity type: Fy 09 project 1 fte month total

-   Milestones:

    a.  SqlServer general support

        -   Install, configure, and test new SqlServer databases,
            perform version upgrades of existing databases if necessary

        -   Support SDSS and Sharepoint databases, setup users, roles,
            etc.

        -   Administer and operate production and development SqlServer
            database information systems.

        -   Participate in a 24x7 on-call rotation schedule for
            production issues, if necessary

        -   Perform SqlServer database backups and data restores.

        -   Disaster recovery for SqlServer databases.

        -   Diagnose problems, and monitor database performance.

        -   Perform tuning of SQL queries.

        -   Cross train dbas on MS SqlServer technology.

> -Metrics:  uptime statistics, performance
>
> -Dependencies: scope of work in unknown in size. Fte calculation is
> based on minimal known scope. This may prove inaccurate.

**Open Source Database Support**

-Activity type: Fy 09 project 6 fte months general support + 22
training/projects= 28 fte months

-   Milestones:

    a.  Open Source general support

    -   Install, configure, and test new Open Source databases, perform
        version upgrades of existing databases

    -   Administer and operate production and development Open Source
        database information systems.

    -   Participate in a 24x7 on-call rotation schedule for production
        issues.

    -   Perform Open Source database backups and data restores.

    -   Disaster recovery for Open Source databases.

    -   Replication

    -   Diagnose problems, and monitor database performance.

    -   Perform tuning of SQL queries.

    -   Implement strategy for Open Source implementations, including
        standards, procedures, best practices, scripts, policies, etc.

    -   Continue assuming responsibilities of current production
        freeware databases, bring them up to standards.

    -   Purchase and implement monitoring software for postgres and
        mysql

    -   Purchase 24.7 3^rd^ party support for mysql and postgres.

    -   Backup & restores.

    -   Continuity initiatives

    b.  Train dbas in both mysql and postgres to a level of production
        support, this includes formal classes as well as on the job
        training. 6 fte months

    c.  \*Retrofit toolman application for open source database
        platforms, postgres&mysql, if possible, providing a monitoring
        tool for open source platforms 8 fte months.

    d.  \*Rebuld mysql installations on flxd01&flxd04 so that products
        are not owned by root. 1.5 fte months

    e.  \*Implement new machines to replace flxd01&flxd04. Dependant on
        hardware purchase. 1 fte month

    f.  \*Implement new machine for nimi dev database. Dependant on
        hardware purchase 2 fte month

    g.  Upgrade minos production and development to v5 of mysql. 1.5 fte
        month

    h.  Implement new Minerva production machine 1 fte month

    i.  Nova support .5 fte months

    j.  Support ilc/project x as needed. .5 months

```{=html}
<!-- -->
```
-   Metrics:  \# of people trained in different platforms

-   Metrics:  stabilization and consistency of existing freeware
    environments that are being absorbed. Cross training of support
    personnel to support these environments to enable dependable
    support.

-   Dependencies:  training, manpower

**Kerberize Oracle with Advanced Security Option.**

-   Activity type: Fy 09 project 1 fte month

-   Milestones:

a.  continue oracle aso kerberos project

b.  implement Kerberos authentication for DBA tasks

c.  install aso for users as needed

d.  define an action plan to support aso

-   Metrics:  % of kerberized dba tasks, % of kerberized user tasks

-   Dependencies: 

-   Maintain expertise for new features.

**Investigate potential for using auto storage management**

-   Activity type: Fy 09 project .5 fte month

-   Milestones:

a.  \*Proof of product

b.  \*benchmark tests

c.  \*document results

d.  \*provide recommendations for auto storage mgmt

-   Metrics:  benchmark figures

-   Dependencies:  fte cycles need to be available

**Evaluate rman incremental backups in Oracle 10**

-   Activity type: Fy 09 project 1 fte month

-   Milestones:

    a.  \*create test plan .03

    b.  \*test .03

    c.  \*recommend use or not of incremental backups .02

```{=html}
<!-- -->
```
-   Metrics:  na

-   Dependencies: fte cycles need to be available

**Complete upgrade to Oracle 10 for the information systems databases**.

-   Activity type: Fy 09 project 7 fte months

-   Milestones:

a.  Information systems databases upgrade 10gR2 planning and testing.

-   Metrics:  % of instances upgraded

-   Dependencies:  application readiness

**Upgrade infra structure hardware for databases**

-   Activity type: Fy 09 project 5 fte months

-   Milestones:

a.  plan separation of information systems apps, including major apps,
    24.7 and others 1 fte month

b.  plan for data movement to new databases 1 fte month

c.  Implement new databases, including oid repository and remedy. 3 fte
    months

-   Metrics:  % upgraded

-   Dependencies: people resources

**Migrate Databases to 3Par San technology**

-   Activity type: Fy 09 project 3 fte months total

-   Milestones:

    a.  Move dev/int cdf offline databases to 3par to replace aging and
        unsupported disk array. 1 fte month

    b.  Move dev/int d0 luminosity databases/snapshots to 3par to allow
        for a full and complete testing environment. .5 fte months

    c.  \*Assess and move if possible the cdf online dev/int
        environments of aging array. 5. fte month

    d.  Move fcdfora6 replica database to 3par. .5 fte month

    e.  Move Minos Sam databases to 3par .5 fte month

-   Metrics:  uptime, \$ saving on time and materials repairs to
    unsupported hardware.

-   Dependencies: successful implementation, budget

**SDSS**

-   Activity type: FY 09 project 9.5 fte months

-   Milestones:

    a.  Load and Release DR7 1 fte month

    b.  Loading Runs Database 1 fte month

    c.  Standardizing the Idrea Monitoring and metrics. 1 fte month

    d.  Support the transition from SDSS operations to long-term
        maintenance, including consultancy with JHU & UC. 6 fte months

    e.  \*Implementing DR7 at UC .5 fte months

-   Metrics: Meet Release Schedule dates.

-   Dependencies: UC dr7 implementation dependant on funding.

**DES Standby Database**

-   Activity type: FY 09 project 4 fte months

-   Milestones:

    a.  Test and develop expertise in oracle rac.

    b.  Test and develop expertise in auto storage mgmt.

    c.  Implement development stand by database at fermi, master in
        Champaign.

    d.  Install Oracle and implement production standby database at
        fermi

    e.  Investigate and implement proper rac, standby monitoring tools.

-   Metrics: Meet Release Schedule dates.

-   Dependencies: Hardware availability, Mou

**Priorities**

Top priority is to continue as strong, stable environment for RunII
databases, optimizing database uptime in a 24.7 environment. This will
be accomplished by proactively addressing hardware needs and constant
database monitoring.

Priority 2 is to continue to develop stronger expertise and support for
open source databases, reducing the dependency on Oracle, with a goal of
reducing development and support costs is to maintain a stable and
secure database environment. Stability and security is achieved by
following the standards and procedures set out in baselines and best
practices. This includes providing an agile response to the use of core
standards and procedures for development, implementation and maintenance
in a secure and consistent manner.

Priority 3 is to improve the information systems hardware situation. We
were able to procure hardware for this upgrade, thus, moving forward.  
Fncdug1 is increasingly overloaded with users and products. A larger
development team, along with additional information systems projects has
overloaded fncdug1.  We have turned 2 of 3 integration databases to
allow the 3rd to run with additional processes needed by the development
team.  Not addressing this priority may lead to slower response, and
denial of service, at times on the information systems database. We are
continually adding to the load on these machines with no additional
resources.

Priority 4 is to move the information systems databases to Oracle 10.
The final software dependency has been eliminated, moving forward.

Priority 5 is get Oracle Advanced Security Option implemented. 
Consequences of not being able to implement kerberos will be citings
resulting from any upcoming DOE audits.  We would like to avoid this at
all costs.  When Oracle provides the software, we will make it a
priority to test and implement.  This is a no \$ cost priority.

Priority 6 is to move the development and integration databases that are
currently living on unsupported hardware to 3par SAN. This includes cdf
online, which though still supported by D1, we have been warned the
support will not continue much longer. There is a firewall issue that
must be resolved to move this database(s) to 3par. Not enough disk
capacity to refresh D0 luminosity dev/int databases from production in
order to facilitate the testing on most recent production data.

**Staffing Issues**

Staffing should be adequate. SDSS will be ramping down, DES, Open Source
will require additional resources. 99 fte months or 8.25 dbas will be
needed to complete all tasks.

**Change Control**

Delay of full implementation of the 3par array to b0dau36, fcdfora1 and
d0lum1 pose some risk. The stakeholders would need to be informed of the
decision and potential consequences.

**Risk Assessment**

1\. It is imperative the experimental databases are kept running and
healthy.  This requires support, maintenance and hardware.  There is no
hardware support for the array on fcdfora1, cdf offline dev/int, due to
age. We need to move that to the 3par as soon as possible. The interface
card has been purchased, however, furloughs and forced vaca have led to
postponement of the move. B0dau36, cdf online dev/int, is almost in the
same state. D1 has warned us that end of support is very near for its
array. The complication with b0dau36 is a firewall that must be over
come. A switch or another network option needs to be implemented to
reach the 3par. D0lum1, the d0 luminosity dev/int array has never had
sufficient space for both a dev and int database, and now after the
calibrations are complete, d0lum1 not have space for even 1 full copy.
The users do not have the ability to run thorough tests under these
conditions, and our group's ability to support the users is vastly
diminished. Again, the interface hardware from d0lum1 to the 3par has
been purchased, manpower to do the move is pending.

2\. The risk assessment has the experiments as the highest risk (due to
aging hardware, etc.) but this isn\'t discussed as part of the tactical
plan.

3\. Oracle Advanced Security Option needs to be deployed. We do not
consider Oracle's work around a 'best solution' however; we have gotten
proof of product complete. A full scale implementation plan across
database for cron jobs needs to be completed. There is a risk that this
deficiency in security is called out in the next DOE audit. We continue
to work with Oracle.

4\. Upgrading the information systems databases to Oracle 10 is still on
the list from fy 07. The only databases still on v9 are the information
systems dbs. DSG had been waiting for the software dependency of matrix
to be eliminated before starting. As of May 08, matrix is gone and work
can begin. Having all the databases at a consistent version will ease
maintenance effort. At this time, there is no risk of failure of the
information systems applications if this upgrade is not completed.

5\. The information systems hardware is being strained due to increased
applications and users. New hardware to move the production databases to
has been purchased and is in prep. DSG will start installing asap,
however, various leaves of sysadmin personnel has slowed this
implementation The new box will be prepared to move the production
information systems dbs to, the old hardware, fncdug1, will remain in
use for applications only. No budget was allocated to replace fncduh1,
the development machine.

6\. Continued absorption of production environments at varying levels of
quality and resource demands is stretching our manpower limitations.
Lack of training in Open Source products and monitoring tools is a
concern.

**M&S**

  ------------------- ------------------- ------ -------------- -------- -------------
  Description         Notes               DEPT   ACTIVITY       Total    EQ/OP
                                                                Cost     

  Oracle Licenses     1-year term         css    All            370000   [op]{.mark}
  (scientific)        licenses (56k shows                                
                      up in REX)                                         

  Krypton replacement Console             Css                   4000     [eq]{.mark}

  San storage         3par 1T space       css    All (waiting   12000    [eq]{.mark}
  experiment dbs      anticipated &              \$                      
                      qlogic cards               verification            
                                                 from ray)               

  flxd01 & flxd04     replace aging mysql css    Fnal mysql     10000    [eq]{.mark}
  replacment, gen.    general purpose dev                                
  mysql dev box       box                                                

  dba32 replacement   replace aging 32b   Css    Oem prod       3000     [eq]{.mark}
  oem prod box        oem monitoring box                                 

  Cards for san       Ip cards for san    Css                   6000     [eq]{.mark}

  misc                misc                Css    Misc hdw       2000     [eq]{.mark}
  cables/batteries,   cables/batteries,                                  
  etc                 etc                                                

  oemora1&2 going to  oemora1&2 going to  Css    Expired sun    630      eq
  d1 3/09             d1 3/09                    support going           
                                                 to d1                   

  Oemora1&2 larger    Oem server machines Css    Running short  1000     eq
  disks                                          on disk space           

  dbi-dba d1 support  dbi-dba d1 support  Css    D1 offline     15000    [eq]{.mark}
  costs               costs                      support                 

  lt03 enstore tapes  new tapes for new   Css    Rman tapes     14000    [Eq]{.mark}
                      robot, db backups                                  

  MsSql Server        MsSql server        Css    Training &     5000     Op
  conference          conference                 travel                  

  Oracle conference   Oracle conference   Css    Training &     4000     Op
                                                 travel                  

  Mysql conference    Mysql conference    Css    Training &     2000     op
                                                 travel                  

  Postgres conference Postgres conference Css    Training &     3000     op
                                                 travel                  

  Foreign travel                          Css                   22485    op

  1 general ed class  1 general ed class  Css    training       41250    op
  per employee        per employee                                       

  Unsupported hdw                         Css                   10000    eq
  repair                                                                 
  ------------------- ------------------- ------ -------------- -------- -------------

**Changes in Personnel**

Though the dbas are over extended, it is known that all activities will
not be achievable. There are several activities that can be delayed till
manpower is available. No additional staffing is requested.