1 / 15

RDS Administration & Security

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

norah
Télécharger la présentation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. RDS Administration & Security Session #396 Monday, 3/17/2003 11:45am HEUG 2003 Conference - Dallas

  2. Carol Jordan Technical Lead, Application Support Stanford University Kevin Dale Functional Analyst, Application Support Stanford University Session #396 - RDS Security & Administration

  3. 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

  4. 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

  5. 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

  6. 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

  7. Technical Infrastructure – Graphical overview Session #396 - RDS Security & Administration

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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 Universitycarol.jordan@stanford.eduHTTP://HEUG.ORG Session #396 - RDS Security & Administration

More Related