Rds administration security
This presentation is the property of its rightful owner.
Sponsored Links
1 / 15

RDS Administration & Security PowerPoint PPT Presentation


  • 72 Views
  • Uploaded on
  • Presentation posted in: General

RDS Administration & Security. Session #396 Monday, 3/17/2003 11:45am HEUG 2003 Conference - Dallas. Carol Jordan Technical Lead, Application Support Stanford University. Kevin Dale Functional Analyst, Application Support Stanford University. Presenter Background

Download Presentation

RDS Administration & Security

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Rds administration security

RDS Administration & Security

Session #396

Monday, 3/17/2003 11:45am

HEUG 2003 Conference - Dallas


Rds administration security

Carol Jordan

Technical Lead, Application Support

Stanford University

Kevin Dale

Functional Analyst, Application Support

Stanford University

Session #396 - RDS Security & Administration


Rds administration security

Presenter Background

-- My first implementation was commercial HRMS in 1997.

-- I joined Stanford in 2001, during the Student Administration implementation project. As part of that implementation project, Stanford installed the RDS in early 2001.

-- Stanford’s PeopleSoft implementation:

PeopleSoft 7.6 modules:Human Resources, Payroll, Benefits

Admissions, Student Records, Student Financials,Financial Aid, HTML Access

PeopleTools 7.62

Session #396 - RDS Security & Administration


Rds administration security

Synopsis of PresentationDescription of the presentation: To share the techniques we’ve used in the security and administration of the RDS to make it a strong and useful tool for enterprise-wide reporting.In this presentation, I’ll talk about the original vision for enterprise-wide reporting, our hardware set-up, the highlights of our nightly batch process, and some of our automation tricks for end-user security.What to take away: setting up a reporting environment for PeopleSoft users takes a committed cross-functional effort from DBAs, Storage, and UNIX/Windows system administrators

Session #396 - RDS Security & Administration


Rds administration security

Stanford’s vision for enterprise-wide reporting:To provide a single, web-based interface for all reporting needs, across all applications and all back-end platforms using Brio Portal

Session #396 - RDS Security & Administration


Technical infrastructure overview

Technical Infrastructure – Overview

  • Two components to our reporting infrastructure: PeopleSoft & ReportMart

  • ReportMart:

  • Database/application/web servers: Oracle 8i, UNIX Solaris 7

  • PeopleSoft:

  • Database servers: Oracle 8i, UNIX Solaris 7

  • Separate database servers for transactional and reporting databases

  • Storage: EMC Symmetrix frame, BCVs

  • RDS server: Windows 2000

  • Features of our set-up:

  • Separate servers for transactional and reporting databases

  • EMC TimeFinder to re-create the reporting DB… in just a few minutes…with no downtime!

  • DB links to tie together operator security between the RM and PS applications

  • AutoSys scheduler spans UNIX & Windows jobs

Session #396 - RDS Security & Administration


Technical infrastructure graphical overview

Technical Infrastructure – Graphical overview

Session #396 - RDS Security & Administration


Creating rprd original

Creating RPRD – Original

  • Export ODS schema

  • Create a RPRD as an exact image of PRD (using TimeFinder)

  • Import ODS schema

  • Run RDS (and other) builds

  • This worked okay, but we learned as we went along…

Session #396 - RDS Security & Administration


Creating rprd getting better

Creating RPRD – Getting better

Problem: RPRD isn’t ready due to problems with overnight processing…but users are running reports on incomplete or incorrect data.

Solution: We implemented Oracle “restricted session” to lock out reporting users while the builds are in progress. Once the builds are complete, we let the reporting users access the reporting DB.

Session #396 - RDS Security & Administration


Creating rprd getting better1

Creating RPRD – Getting better

  • Problem: Performance of reporting builds, performance of reports and ad hoc queries

  • Solutions:

  • We placed an Oracle “resource limit” on reporting users – this kills queries after a pre-defined time period.

  • We scheduled a cron job that runs a UNIX “top” command every 10 minutes, and if CPU is over 75% send an e-mail – we can then take a look at problems before we get the “reporting is too slow” trouble ticket.

  • We scheduled reports of the highest-cost SQL for reporters’ daytime activity and overnight batch-builds activity. This report shows us the SQL that needs to be tuned.

Session #396 - RDS Security & Administration


Creating rprd getting better2

Creating RPRD – Getting better

  • Problem: Needed to extend the operator security defined within PeopleSoft to the reporting system

  • Solutions:

  • We used DBA policies to limit ad hoc reporters’ access to secured data (such as salary).

  • The ReportMart team used PS operator classes to grant the privilege to see various report categories. A cron job runs several times daily to update RM security from PeopleSoft.

  • We implemented Fast Security, but only for reporting – we re-wrote the AppEngine process into a custom SQR that runs in about an hour:-- drop all indexes, then rebuild only the ones we need-- use “truncate table reuse storage”-- load fewer columns in the Fast Security table

Session #396 - RDS Security & Administration


Creating rprd revised

Creating RPRD – Revised

  • Run daytime SQL Cost Report (for reporting activity)

  • Export ODS schema

  • Create a RPRD as an exact image of PRD (using TimeFinder)

  • Place DB in “restricted session” – lock out reporting users

  • Import ODS schema

  • Run RDS (and other) builds, run Fast Security

  • Run overnight SQL Cost Report (for batch processing)

  • Remove “restricted session” – make system available to reporters

Session #396 - RDS Security & Administration


Creating rprd next steps

Creating RPRD – Next steps

  • We need a way to block users from “testing” in RPRD. Our next improvement will be to block users from getting into the reporting DB through the PeopleSoft and trying out fixes in a fresh copy of production.

  • We think we’d get a performance gain from running RDS builds on UNIX.We haven’t had time to put this together…yet.

  • We’d like to move the reporting database to a larger DB server.…particularly in anticipation of V8 and more RDS processing and more reporting users.

Session #396 - RDS Security & Administration


Creating rprd on time

Creating RPRD On Time

  • …requires constant vigilance!

  • Our application administrators are paged when the reporting builds do not complete on time.

  • We monitor application availability and must provide an Incident Report when we do not achieve our application up-times. The goal is to acknowledge problems, discuss them, and to put mechanisms in place to avoid repeating them.

  • We monitor CPU utilization on the reporting DB server, and anticipate upgrading in the near future – we need more CPUs to multi-thread our reporting builds.

  • Our SQL Cost Reports show what SQL needs to be tuned;we try to tune proactively make the builds run as fastas possible.

Session #396 - RDS Security & Administration


Rds administration security

Conclusion-- Our implementation, and our ongoing administration, span several technical teams: DBAs, Storage, and UNIX/Windows System Administrators, as well as the application team responsible for our reporting portal. The variety of expertise on the virtual team is essential.-- Our reporting and transactional systems are available M-F 6am-12M, and Sat-Sun 8am-12M. To achieve this goal requires constant vigilance – continual, proactive tuning. Contact information:Carol JordanTechnical Lead, Application SupportStanford [email protected]://HEUG.ORG

Session #396 - RDS Security & Administration


  • Login