Making CCB's very important ChemGrades DB more robust, and enabling necessary increased functionality, especially for teaching faculty and students. Ensuring its long-term support and operation. Representing customer is Pat Hine's (PatH).

Links

When completed, here's the URL for the read-only web access:

• https://chemlabs.arts.cornell.edu/grades/

Eval from 8/13/13:

• https://chemlabs.csotest.arts.cornell.edu/grades/

Updates

8/16/13

• Cname of grades.chem.cornell.edu from as-chm-vsrv-09.ad.cornell.edu to sf-as-data-001.serverfarm.cornell.edu has been submitted and will take effect at the 11am DNS update.

To do's

See the following section for context related to these to-do's.

Enable Pat's "push" capabilities so her DB info can be seen on the web

• Tu will work with Roger.
  • Tu needs IP addresses. Roger can do the installs.
  • And Roger may have better alternatives. :-)

Test PatH's restore procedure.

• Do ~end of Sept? (No later, please!)

Enable PatH's remote access

• ChemIT must do this.
• Oliver must do or assign.

Enable ChemIT staff's access to CCB FMPro DBs.

• Do via AD group. Enable only "Admin" accounts, not ChemIT staff's user accounts. (Right, Roger?)
• Tu to work with Roger.

Ensure FMPro access by TAs and a few staff

• Ensure that all "single account set up for a whole course for use by many TAs" is cleaned up. (Legacy capability which is no longer needed. And confuses things now.)
  • Dani and PatH
• Ensure works from systems: TA room, DanL, CynthiaK, and Sam's.
  • ChemIT staff:MichaelH.

Decisions, processes, and procedures

Establish procedure for PatH to update web-hosted data. "Push the button".

As of 8/16/13, function doesn't exist for PatH.

• Requires ODBC connection. Not possible at FMPro server; must be set up at FMPro client's computer.
  • Do for PatH's computer. As backup, do on Sam's computer.
  • Current plan: For each computer, set ODBC up as system's Admin (vs. user) so ODBC accessible/ configured for any computer account.

Establish semester-start procedures and processes. Includes:

This fall, PatH's first pull is no sooner than 8/29/13 (Thur). Later for lecture-only courses.

• Initially populating the DB with PeopleSoft data via a PeopleSoft import.This is a one-time event each semester for each course. (Courses done at different times, depending on course.)
  • Do imports in productions course DBs.
    • Can zero out if mistake made. If major problem, contact Dani or Tu to revert to prior DB.
• At the beginning of the semester (add/ drop period), PatH will periodically grab copies of the back-ups so she can created statistics re: rate of enrollment changes.

Establish semester-end procedures and processes. Includes:

• PatH pulls the final data from the backups.
  • NOTE: If an edit is done, must wait until that edit shows up in a backup (~ half a work day) and then that backup becomes the "final data" for that course.
• PatH zeroed out each of the ChemGrades DBs before the start of their next use.
  • PatH does this within FMPro, when she needs it done (varies by course).
• Cut off TA's access to FMPro.
  • PatH does this within FMPro, when she needs it done (varies by course?).
• Some courses must not show the grade of the final exam, per faculty member's requirement.
  • In those cases, PatH will simply not "push" her final data to the web (varies by course).
    • For these courses, grade changes for a student done after the last push (right before final exam grades entered into FMPro, ideally) will thus not show up on the web.
      • These are edge/ rare cases and PatH will inform the student of this fact.

Establish DB revert procedure. Initiating request and subsequent restoration from back-up.

PatH has access to last 7 calendar days of backups, 2 times per day. So, 14 backup files per course at steady-state, whether changes were made or not.

• Backups are made for each course.
• Backups are made around noon (morning data changes) and in the evening (afternoon data changes).
• Each backup's name contains a date and time-stamp.
• Files are located on Instruction's file share (R:\ drive).

PatH can do any restore herself using FMPro, ensuring "low friction" access to the backup files (nobody to contact, not hoops to jump through).

• Zero out course data on the A&S FMPro server.
• Importing data from the correct course backup file.
  • Latest version would be from ~half a work day before.
• If PatH needs backup data older than what she has access to herself (> 7 days, so rare), she must contact Dani or Tu for them to get via back-ups of the SQL server data (or from EZ-Backup).
  • Note that A&S's EZ-Backup would itself only have the 3 prior versions. If course DB was changed each day, that's just three backups. If course DB rarely changed, then that can be a long time.

Enable Pat's view of DB data from home.

The need for this has been reduced since many folks can self-serve, and PatH herself has view-only web access.

• Although the web version may be OK for view-only of some data, it's obviously not as flexible for searching. And there is no way for PatH to make changes through the web version.
• On accasions when PatH needs full access, we will enable Remote Desktop on PatH's main work computer. This way she can do everything she can do when she's at work.
  • She access her computer from off-campus via VPN.

As an alternative, if she knows she'll need it ahead of time, PatH can take a backup copy of any course on a FMPro computer for stand-alone access. This works as long as PatH doesn't make changes on the stand-alone DB that she later expects to see on the production, server-based DB, of course!

Rejected idea, for future reference: With FMPro installed on dept. laptop, access AS's FMPro server off-campus and do the work that way.

Ensure security of FERPA data in Grades DB.

See Michael Hint's emails with Tu and Frank for more on this. Some thoughts, initially:

• FERPA-like data so ChemIT would appreciate understanding the ACL (see for subnet?) and firewall configurations.
  • ChemIT thinks there shouldn't be any off-campus access except via VPN. Thus take off of public net and put into 10-space. But there may be other factors to consider.
• Access to the FMPro DB is controlled by NetID (AD groups). Once in, who else has access to Grades DB FMPro DB itself?
  • Answer: Only accessible to authorized FMPro users or groups.
    • If a user isn't authorized for a DB, they dont' even get to see that the DB is available.
    • If a user enters the fully qualified name to the DB they can't see (because they aren't authorized within the DB), the DB still wont' open.
• Ideal: Make all access via NetID/ NetID p/w, not via FMPro DB's own controls.
  • Control NetID membership via Active Directory groups.
    • Start with Grad TA access, via theTA room kiosks? Also start with faculty access?
    • Once process confirmed, establish procedure for enabling and disabling NetID access to Grades FMPro DB by grads and faculty
      • Can take ChemIT staff less than a minute to do this, and is effective immediately.

• Confirm FMPro server and admin groups support required staff access rights.
  • Currently it authorized CCB staff to see all PatH sees.
  • Confirm Becky can see what she needs to see.
    • Exports data using PatH's account.
  • Confirm TAs can see/ edit what they need to edit.

Clarify support and maintenance expectations

• Clarify to what degree A&S staff (Dani and Tu) are available and willing to perform support.
  • Hosting vs. Design/ Creation vs. Support
• Distinguish between something breaking (worked yesterday!) and new need (consultation/ design/ implementation).

Map out transition steps remaining

• Change direction of cname, <grades.chem.cornell.edu> from old to new instance.
  • ChemIT needs a date.
  • Doing this means other systems (2 computers in TA room; Becky's computer) automatically cut-over. :-)
• Shut off access to old ChemIT-run instance (Win7 VM). "Legacy Grades".
  • ChemIT needs a date.
• When add access via AD groups?