FY08 Plan for Databases\
Prepared by A. Kumar, N. Stanfield, J.Trumbo\
June, 2007

**Databases Goal**

Continue to provide a stable, professional operation of laboratory
scientific and infrastructure 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 08**

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  / RMAN Oracle
    10g Testing

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    24.7 Infrastructure implémentation

-   DATABASES & INFO MANAGEMENT / Database Administration / Database Cad

-   DATABASES & INFO MANAGEMENT / Database Administration / Database D0
    Online Integration to DSG

-   DATABASES & INFO MANAGEMENT / Database Administration / Database DOE
    security audit

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    general support CDF

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    general support CMS detector

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    general support D0

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    general support Infrastructure (miscomp)

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    general support Minos

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    general support Nova

-   DATABASES & INFO MANAGEMENT / Database Administration / Oracle
    Backup & Recovery

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems Cad

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems D0
    Online Integration to DSG

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems DOE
    security audit

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    general support CDF

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    general support CMS detector

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    general support D0

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    general support Minos

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    general support Nova

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    general support, Infrastructure (miscomp)

-   DATABASES & INFO MANAGEMENT / Database Administration / Upgrade of
    Oracle Enterprise Manager to OEM 10 on Linux

-   DATABASES & INFO MANAGEMENT / Experiment DB ADMIN Support  / CDF DB
    Support

-   DATABASES & INFO MANAGEMENT / Experiment DB ADMIN Support / CMS
    Detector Database

-   DATABASES & INFO MANAGEMENT / Experiment DB ADMIN Support / D0 DB
    Support

-   DATABASES & INFO MANAGEMENT / Experiment DB ADMIN Support / MINOS
    Database Development

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 and development Oracle database infrastructure.
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.

b\. Open Source Database Support.

Support production and development open source database infrastructure.
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 / Database
    general support Freeware

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    general support, Freeware

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\. Production Deployment of the 3par San technology on d0, a standard,
high availability database disks 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. On 3par the Oracle database and open source
database can be hosted.

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    Move from D0ora2 clarion array

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems Move
    from D0ora2 clarion array

-   DATABASES & INFO MANAGEMENT / Database Administration / Databases on
    San Testing

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    Databases on San Testing

5\. Kerberize Oracle using Advanced Security Option.

-   DATABASES & INFO MANAGEMENT / Database Administration / Oracle
    Advanced Security Option

6\. Maintain expertise for new features.

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    Oracle Rac Testing ( ASM )

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    Oracle Rac Testing ( ASM )

-   DATABASES & INFO MANAGEMENT / Database Administration  / RMAN Oracle
    10g Testing

7\. Plan Strategy to incorporate virtual machines.

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    planning/implementation/standardization vm env.

8\. Complete upgrade to Oracle 10 for the infrastructure databases.

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    FNCDUG1-H1 Replacement

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    FNCDUG1-H1 Replacement

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    Oracle 10 Upgrades Infrastructure (miscomp)

9 Upgrade infrastructure hardware for databases, including major apps,
computer security, 24.7 applications and nimi.

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    Major Apps Implementation

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems
    Major Apps Implementation

-   DATABASES & INFO MANAGEMENT / Database Administration / Database
    24.7 Infrastructure implémentation

-   DATABASES & INFO MANAGEMENT / Database Administration / Systems 24.7
    Infrastructure Implementation.

10. SDSS

> In Fermilab FY08, both DR7 and DR8 will be loaded and released. .  
> DR7\
> will go public by June 30, 2007.  DR8 will go public by October 30,
> 2007 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 loading the Runs database.   By the end of FY08, all imaging
> runs into the RunsDB should have been loaded.  The intent will be to
> release RunsDB to the public along with DR8, since by that time, all
> SDSS data will be in the public domain.
>
> Continue using Idera to monitor and track performance. To standardize
> the tool if possible.  Perhaps monthly summary reports regarding
> status of systems and uptime metrics.
>
> 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)

    -   DATABASES & INFO MANAGEMENT / Database Administration / Database
        SDSS support non-ARC funded

**Database and Systems Operations**

-   Activity type: Ongoing

-   Milestones:

    a.  maintain database baselines

    b.  react to any new security initiatives

    c.  proactively maintain hardware

    d.  monitor both databases and systems allowing proactive
        troubleshooting

-   Metrics: uptime

-   Dependencies::

**Database Support**

**Oracle Support**

-   Activity type: Fy 08 project

-   Milestones:

    a.  Install, configure, and test new Oracle databases, perform
        version upgrades of existing databases.

    b.  Administer and operate production and development Oracle
        database infrastructure.

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

    d.  Perform Oracle database backups and data restores.

    e.  Disaster recovery for Oracle databases.

    f.  Replication

    g.  Diagnose problems, and monitor database performance.

    h.  Perform tuning of SQL queries.

```{=html}
<!-- -->
```
-   Metrics:  recommendation report

**Open Source Database Support**

-Activity type: Fy 08 project

-   Milestones:

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

    b.  Administer and operate production and development Open Source
        database infrastructure.

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

    d.  Perform Open Source database backups and data restores.

    e.  Disaster recovery for Open Source databases.

    f.  Replication

    g.  Diagnose problems, and monitor database performance.

    h.  Perform tuning of SQL queries.

    i.  Train dbas in both mysql and postgres to a level of production
        support

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

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

    l.  Purchase and implement monitoring software for postgres and
        mysql

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

    n.  Backup & restores.

    o.  Continuity initiatives

```{=html}
<!-- -->
```
-   Metrics:  recommendation report

-   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

**Agile response to new and rapid shifts of responsibilities and
demands**

-   Activity type: Fy 08 project

-   Milestones:

> a\. Build expertise for open source and common tools
>
> b\. Cross Training

-   Metrics:  Response time

-   Dependencies: training, manpower

**Complete production implementation of 3Par San technology**

-   Activity type: Fy 08 project

-   Milestones:

    a.  Insure stable environment for d0ofprd1 and d0ofint1

    b.  Move dev/int cdf offline databases to 3par to replace aging and
        unsupported disk array.

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

    d.  Assess and move if possible the cdf online dev/int environments
        of aging array.

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

-   Dependencies: successful implementation, budget

**Kerberize Oracle with Advanced Security Option.**

-   Activity type: Fy 08 project

-   Milestones:

a.  work as beta site for oracle aso kerberos project

b.  install aso for crons

c.  install aso for users

d.  move 90% of accounts to kerberized per experiment

e.  review 10% left and define an action plan to support aso

-   Metrics:  vendor recommendation report and purchase

-   Dependencies:  dependent on Oracle providing MIT standardized
    software product

**Maintain expertise for new features.**

**Investigate potential for using auto storage management**

-   Activity type: Fy 08 project

-   Milestones:

a.  prepare test plan

b.  benchmark tests

c.  document results

d.  provide recommendations for auto storage mgmt

-   Metrics: 

-   Dependencies:  fte cycles need to be available

**Evaluate rman incremental backups in Oracle 10**

-   Activity type: Fy 08 project

-   Milestones:

    a.  create test plan

    b.  test

    c.  recommend use or not of incremental backups

```{=html}
<!-- -->
```
-   Metrics:  recommendation report

-   Dependencies: fte cycles need to be available

**Plan Strategy to incorporate virtual machines**

-   Activity type: Fy 08 project

-   Milestones:

    a.  Prepare plan on proposed future vm potential configuration and
        purchases.

    b.  Invite vm ware to lab to discussion issues, strategy,
        recommendations.

    c.  Prepare a plan, including best practices, standards, procedures,
        risk analysis to incorporate vm into hardware environment based
        on recommendations.

    d.  Begin purchase of new hardware based on recommendations and
        strategic plan. Coordinate within quadrant.

-   Metrics:  recommendations and strategic plan, purchases

-   Dependencies:  budget

**Complete upgrade to Oracle 10 for the infrastructure databases**.

-   Activity type: Fy 08 project

-   Milestones:

a.  os upgrade

b.  Infrastructure databases upgrade 10gR2

-   Metrics: 

-   Dependencies:  removal of remaining matrix items including svx and
    d0 matrix applications.

**Upgrade infra structure hardware for databases**

-   Activity type: Fy 08 project

-   Milestones:

a.  plan separation of infrastructure apps, including major apps, 24.7
    and others

b.  request quotes for hardware

c.  purchase and implement new hardware

d.  plan for data movement to new databases

e.  implement new databases

-   Metrics:  recommendation report

-   Dependencies: hardware, budget, people resources

-   

**SDSS**

-   Activity type: FY 08 project

-   Milestones:

    a.  Load and Release DR7

    b.  Load and Release DR8

    c.  Loading Runs Database

    d.  Standardizing the Idrea Monitoring.

    e.  Support the transition from SDSS operations to long-term
        maintenance.

    f.  Consultancy JHU and UC for hosting DR5

-   Metrics: Meet Release Schedule dates.

-   Dependencies::

**Priorities**

Top priority is 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 2 is to improve the infrastructure hardware situation.  Fncdug1
is increasingly overloaded with users and products. A larger development
team, along with additional infrastructure 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 infrastructure database. We are continually
adding to the load on these machines with no additional resources.

Priority 3 is to move the infrastructure databases to Oracle 10. We are
still waiting for dependencies to be cleared before moving forward.

Priority 4 is get Oracle Advanced Security Option implemented.  We are
still relying on Oracle to fix to show stopping software issues and meet
MIT standards.  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 5 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**

After assuming responsibility for additional databases and hardware in
07, for both oracle and open source, the group's workload is saturated.
This trend of adding additional responsibility to the group does not
seem to be waning.

**Change Control**

Delay of full implementation of the 3par array and/or delay of purchase
of the hardware necessary to attach to the array for 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 the 3par is operational
with d0 offline. Thus, we need to purchase the interface hardware for
fcdfora1 to the 3par. A major outage on the fcdfora1 array requiring a
time and materials repair could be very expensive. 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 must be purchased.

2\. So 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. Unfortunately,
we are at Oracle\'s mercy for a workable product.  There is a risk that
this deficiency in security is called out in the next DOE audit. We
continue to work with Oracle and are pressuring Oracle to provide a
solution.

4\. Upgrading the infrastructure databases to Oracle 10 is still on the
list from fy 07. All other databases we are responsible for are running
Oracle 10, however, the matrix dependency still exists on g1/h1 for svx
and d0 miser. Hopefully summer 07 will remove these dependencies and
fall 07, upgrade to Oracle 10 can commence. Having all the databases at
a consistent version will ease maintenance effort. At this time, there
is no risk of failure of the infrastructure applications if this upgrade
is not completed.

5\. The infrastructure hardware is being strained due to increased
applications and users. Not addressing this priority may lead to slower
response, or denial of service at times, on the infrastructure
database(s). We continue to add mass in the form of applications and
data to the infrastructure hardware. This hardware is +6 years old, and
should be replaced not only due to age, but also performance.

6\. The absorption of multiple production Open Source 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 Cost  EQ/OP

  Oracle Licenses     1-year term         css    All            360000      op
  (scientific)        licenses (56k shows                                   
                      up in REX)                                            

  redhat license      aporas, dba32,cms,  css    Linux db       24000       eq
  renewals            lum, minos, etc            installs                   

  dbserver machine    dbserver machine    css    D0 dbservers   21000       eq
  replacements        replacements                                          

  San storage         3par and general    css    All            50000       eq
  experiment dbs      san space                                             

  sun support         this was on adams   css                   120         op
                      xls, only 120, not                                    
                      sure what it                                          

  fnlx01 replacment,  replace aging mysql css    Fnal mysql     4200        eq
  gen. mysql dev box  general purpose dev                                   
                      box                                                   

  dba32 replacement   replace aging 32b   Css    Oem prod       2000        eq
  oem prod box        oem monitoring box                                    

  dba64 replacement   replace dsg\'s      css    Dsg box to     2000        eq
  box                 practice                   test freeware              
                      freeware/oracle box        installs                   

  cards for to san    cards to san for    css    No hdw support 6000        eq
  for path to         dual path                  on arrays, no              
  san(3par or other)                             space on                   
                                                 d0lum1                     

  carnac/nimisrvb     carnac/nimisrvb     css    Nimi           10000       eq
  replacement         replacement                                           

  anils continuing    anil\'s continuing  Css    Cont.          17000       op
  education           education                  education                  

  misc                misc                Css    Misc hdw       2000        eq
  cables/batteries,   cables/batteries,                                     
  etc                 etc                                                   

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

  minosora1 going to  minosora1 going to  Css    Expired sun    540         eq
  d1 8/07             d1 8/07                    support going              
                                                 to d1                      

  dbi-dba d1 support  dbi-dba d1 support  Css    D1 offline     40000\*\*   eq
  costs               costs                      support\*\*                

  3rd party 24.7      3rd party 24.7      css    Freeware has   20000       Op
  support for         support for                no support                 
  freeware            freeware                                              

  freeware monitoring freeware monitoring Css    Freeware has   60000       Op
  software            software                   no monitoring              
                                                 tools                      

  lt03 enstore tapes  new tapes for new   Css    Rman tapes     15000       Eq
                      robot, db backups                                     

  replacment for      replacment for      Css    Db design tool 60000       eq
  oracle designer     oracle designer            supporting                 
                                                 freeware,                  
                                                 oracle,                    
                                                 sqlserver                  

  Virtualization of   Virtualization of   Css    Vm ware        ???         eq
  db servers (dev,    db servers (dev,                                      
  int, prod           int, prod                                             
  instances) hw       instances) hw                                         

  Virtualization of   Virtualization of   Css    Vm ware        ???         eq
  db servers (dev,    db servers (dev,                                      
  int, prod           int, prod                                             
  instances) sw       instances) sw                                         

  d0online machine    d0online machine    Css    Cluster        12000       eq
  replacements        replacements               software on                
                                                 online                     
                                                 machines need              
                                                 upgrading. Rh              
                                                 requires new               
                                                 hdw for this               

  MsSql conference    MsSql conference    Css    Training &     5000        Op
                                                 travel                     

  Oracle conference   Oracle conference   Css    Training &     4000        Op
                                                 travel                     

  Mysql conference    Mysql conference    Css    Training &     3000        op
                                                 travel                     

  Postgres conference Postgres conference Css    Training &     3000        op
                                                 travel                     

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

  pc replacements     pc replacements     Css    Replace old    9000        eq
                                                 eq.                        

  unsupported hdw     unsupported hdw     Css    In case of     10000       eq
  repairs             repairs                    emergency on               
                                                 t&m arrays                 
                                                 till moved to              
                                                 3par                       

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

**\*\*\* NOTE this \$40k figure does not include the d0 or cdf online
costs. They could not be found on short notice. They maybe costed by d0
& cdf. One could assume an additional \$40k to include them.**

**Changes in Personnel**

It is expected FY08 we will be asking for an additional dba.