A&S IT provisions and supports CCB's ChemGrades FMPro DB. They also created and support ChemGrades web site showing read-only sub-data from DB, restricted by NetIDs. 

See also

• P12 - ChemGrades FMPro DB

Support and maintenance expectations (DRAFT)

For Chemistry Instructional and IT staff

Useful to distinguish between something breaking (worked yesterday!) and something new needed (consultation/ design/ implementation).

• All requests (for breakage or new feature requests) are to go to ChemIT, from Instructional staff.
  
  • In an emergency (time-sensitive situation), in addition to contacting ChemIT, PatH may contact Dani or Tu.

Instructional staff are encouraged to involve A&S support staff if making structural changes to their DBs. And if Instruction staff make structural changes to any of their DBs, at the very least inform A&S staff as an FYI.

• Debugging a problem is easier if changes to the source DB is known up-front.
• There are many, many moving parts to this complex and interdependent system!
  • An example: Uploads may not work correctly or otherwise need to be tested if structural changes are made.
• Backups and restores may not work correctly if structural changes are made.

A&S's role

• A&S provides the FMPro server hosting services.
  • This includes performing and confirming back-ups and restore procedures.
  • Special focus is on addressing any infrastructure problem at their end.
• A&S staff can do Design/ Creation work on a case-by-case basis
  • This will usually require Frank's approval, with negotiated timelines.

For students

If students have questions or concerns about the web site, they are to contact Chemistry Instructional staff.

Chemistry's web page

• http://chemistry.cornell.edu/courses/grade-faq.cfm
  • https://chemlabs.arts.cornell.edu/grades/
• What is done now for the Waiting List web application? Do same thing?
• Would it not be PatH's office? If so, how communicate on the page?

Procedures and processes

Semester-start procedures and processes. DRAFT:

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.

Semester-end procedures and processes. DRAFT:

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

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

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.

Outstanding actions required?

Make appropriate changes to source FMPro data

• Change all TA access to be by NetID (and FMPro p/w), instead of their name.
  • This keeps the access process the same as it was, but:
    • Enables the correct access restrictions via the web application.
    • Facilitates transition to trueNetID-based access, as a future project.

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.

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 access is correct: Functional and secure.
  • Confirm that only permitted staff can access Grades (and other CCB) DBs.
  • Confirm Becky can see what she needs to see.
    • Add a read-only account for Becky.
    • Becky will still be able to exports her data, but no longer using PatH'/ the Admin account.
  • Confirm TAs can see/ edit what they need to edit for the TA room's computers.

Keywords, for searching results

• FileMakerPro, FMPro, FileMaker Pro