Many of my Blackboard admin colleagues’ blood pressures rise when we talk about SCORM. SCORM (Shareable Content Object Reference Model) was developed by the US Secretary of Defense as part of their Advanced Distributed Learning Initiative. These standards and specifications were set in 2000 and haven’t had a major upgrade since 2010. However many institutions developed content using this standard which can be implemented with Moodle, Sakai, and Blackboard.
Blackboard and SCORM have not been the greatest of bedfellows over the past few years. First Blackboard hasn’t been developing its own SCORM player during this time. Up until 2010, Blackboard clients used an open source SCORM building block. In late 2010, Ray Henderson announced that the company hired Rustici Software which in a 2010 blog post about Blackboard choosing them stated, “Blackboard’s adoption of our rock solid SCORM technology will make things better for Blackboard learners and those people who provide content to Blackboard.” The famous last words, right? Since 2015, SCORM in Blackboard continues to have issues. Austin Community College continues to use SCORM for interactive, gradable learning objects. However, for the first-time, issues with SCORM have stopped a planned upgrade to Blackboard Q4 2017.
I arrived at ACC in February and my manager, Seth Wilkerson, stated we were planning to upgrade to Q4 2017 in May after the Spring 2018 semester wrapped up. I was tasked with testing the Q4 install and finding any issues with the version that might stop the upgrade. I first started to look at the version with a developer vagrant which helped me plan for the new theme and a login page update. Our showstopper really hit us when we started the upgrade on our test environment.
Our test environment mimics our production database. This gives us a good indication of the install time and how long we should “block off” to upgrade our application servers. The initial run did have a few issues and a long install time of about 14 hours. I thought the issue was mostly due to the fact that I had not run some queries and removed a building block recommended by Blackboard. Every time I found an issue, I would revert back to the Q4 2016 version currently running on production and run the upgrade again. Still, I would find that the upgrade would take hours, normally between 12 and 18 hours.
When looking at the logs, the biggest delay was the upgrade of the SCORM building block. After more investigation would take 10+ hours for the upgrade of the building block alone. I took a deep dive with Blackboard support to find what was really causing this extreme install time. Unzipping the building block showed that the building block had three major parts.
First was a set of SQL queries to run prior to the building block install.
- This SQL query makes sense, it backs up all SCORM tables in the Blackboard database.
- This SQL query is odd, it cleans up duplicate entries in the SCORM tables within the Blackboard database.
- The final SQL query drops all the indexes on the SCORM tables in the Blackboard database.
As anyone with an understanding of databases knows, indexes play an important role in the efficiency of queries. If you remove indexes from a table or set of tables, you will need to add those indexes back.
Part two installs the new classes, bundles, configurations, etc. that will support the new building block. These are mostly file copies which will not take much time and maybe a few updates to the database plugins table.
The last part includes two post-SQL queries. Sadly I can’t find any documentation on which query runs first.
- This SQL query cleans up orphan SCORM attempts. This deletes from the following tables:
scormbbattemptoinstance
scormactivityrtcomment
scormactivityrtobjective
scormactivityrtintcorrectresp
scormactivityrtintobjective
scormactivityrtintlearnerresp
scormactivityrtinteraction
scormactivityrt
scormactivityobjective
scormactivity
scormaiccsession
scormlaunchhistory
scormregistrationglobalobj
scormregistrationshareddata
scormregistrationshareddataval
scormregistrationsspbucket
scortincanregistration
scormregistration
- This query builds seven indexes on various SCORM tables in the Blackboard database.
So with the approval of Blackboard Support, I removed the SCORM building block from the Blackboard upgrade to Q4 2017. The install without the building block took 30 minutes. Clearly pointing to SCORM being the cause of the exhaustive installer runtime. So I then decided to install the building block using the B2Manager script while our test system was up and running and I found the following information.
Parts 1 & 2 = 8 hours
Part 3 = 10 hours
Yes. Eighteen hours. Just think about that. You could watch the movie epic, Titanic, five and a half times (But who would do that to themselves, really? I mean you can only hear Celine Dione so much!) while this building block installs. I started to investigate the length of Part 3’s post SQL queries with Blackboard Support and found that many times, the lack of indexes were delaying the query.
We used SQL Management Studio to find execution plans and discovered where the post-SQL query could use additional indexes. Here are two examples below.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[ScormLaunchHistory] ([scorm_registration_id]) CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[ScormRegistration] ([scorm_registration_id])
This deep dive into the SCORM building block clearly shows the SQL queries written to upgrade the building block produce a show-stopping failure when upgrading a Blackboard environment which moderately uses SCORM content. Based on conversations with Blackboard, development has not developed a solution to speed up this installation which leaves clients in a very difficult place. Our decision was to stop any plans to upgrade to newer versions of Blackboard Learn until this SCORM upgrade finds an acceptable resolution for us.
For those institutions who do leverage SCORM content, proceed to upgrade to this version with caution and do your homework. No one needs a lagging SCORM showstopper to ruin an install and an upcoming semester.
Technically Yours,
The Blackboard Guru
