I have begun looking at using Moodle at my school and how we can integrate it into our existing databases so to remove as much administrative burden as possible. I have used Moodle on and off for many years now and one of the first things I always do is set it up to use LDAP Authentication for its log-ons. This makes it really easy to allow users to use their Active Directory log-ons to access your Moodle install. I will not go in to details of that here as it is already well documented over on the Moodle Docs site.

However, one thing that had always proved harder for me with Moodle was the integration of course / class enrolments with the MIS that the school was using. Previously that has normally been Capita SIMS. Capita’s licensing meant I was not allowed to play around in the database back-end of that system to try and create a streamlined process. My current school however uses iSAMS for its MIS system and with this system all custom reporting is done through Microsoft’s SSRS. The upshot of this is that you have full access to the MSSQL database back-end of your MIS and you can begin to manipulate the data using the tools that Microsoft provide you with MSSQL. With this in mind I began looking at the Moodle External Database Enrolment Module to see if we could get a live link between our Moodle install and the course enrolment data already being held and maintained in our school MIS, iSAMS.

There are a number of steps involved in getting this working and a couple of assumptions / prerequisites:

1. Prerequisites and Assumptions
2. Part 1 – Setting up your MSSQL Server
3. Part 2 – Setting up your Apache / PHP server
4. Part 3 – Setting up Moodle

---

Prerequisites and Assumptions

• We are using Microsoft SQL Server Standard 2008 R2 for my iSAMS DB Server; all SQL is only tested against that
• We are running Moodle on a Ubuntu 14.04 64 bit Server. All packages are installed from apt-get
• You have already configured your authentication and are using LDAP / Active Directory Authentication or another type which allows automatic synchronisation of your user accounts
• All Course Categories on Moodle have already been created prior to carrying these steps out. We set our categories up to reflect the “Faculty > Department > Subject” structure that already exists in the school

If you have not yet set up your Moodle server, then please either set it up as per the official Moodle documentation or as per my previous post; then come back here.

Setting up your MSSQL Server

So first off you will need to carry out a few steps on your iSAMS MSSQL server; this is probably the trickiest (at least it was in my experience) part of the set up as there is really no documentation available.

Create a View to get the student and teacher enrolments

Moodle expects the enrolment data to exist in a single table for the whole school. iSAMS has this data stored across a number of different tables. The solution to this is to create a view in MSSQL on your iSAMS database which returns the data Moodle expects. Now in theory this view is all you would need to create to allow Moodle to retrieve its enrolment data from iSAMS. However there is an issue! In my (fairly small) school the table is almost 6000 lines. In a bigger school this table could get much larger very quickly. Moodle is going to query this table at every login to check that its enrolment data matches that of iSAMS. We are going to need some indexes on this view for it to return results efficiently!

This is where the first issue arose; you cannot create an index on a view without locking the view and index to the tables which the view is selecting its data from. As this view is unknown and unsupported by iSAMS, there would nothing to stop them producing an upgrade which changes the underlying table structure of their database. If I had gone ahead and created the indexes for this view it is more than likely that such an upgrade would fail. This is certainly not something we want to happen, so I had to rethink my strategy a little.

My solution is to create a copy of this data from iSAMS into another MSSQL database on the same server which is scheduled to run twice daily upon which we can create the required indexes to enable Moodle to efficiently search this table.

The SQL for my view is here on PasteBin. Expand your iSAMS database in Microsoft SQL Server Management Studio and right click on the “Views” folder and choose “New View…” In the “Add Table” window which opens click “Close”. In the third section of the View designer delete the “SELECT” and “FROM” which is there and paste the SQL from over at PasteBin in it’s place. Press “Ctrl + S” to save the View, giving it a sane name. I am calling mine “VwMoodleEnrolData”. Click “Ignore” to the warning about not being able to represent the query in the diagram and criteria panes; this is because we are using the SQL UNION command to join two separate queries together. One for Student enrolment data and one for teacher enrolment data.

If you now right click on your newly saved view in the Object Explorer pane and choose “Script View As > SELECT To > New Query Editor Window” and then click “! Execute” you should be returned a list of all course enrolments for all teachers and students from your iSAMS system. You may want to spend some time at this point checking that this is as expected.

Create a View to get the list of courses to create

Moodle’s External Database Enrolment plug-in will also let us automatically create the courses which are found in the Student and Teacher enrolment data set if they do not already exist. Again Moodle wants this data in a single table and wants some information which is not readily available in iSAMS. We follow the same process as before to create a new view on the iSAMS database which prepares the data in the way Moodle wants to see it. We then follow the same process of importing the data from the iSAMS database into our new iSAMS-to-Moodle database, as described above.

The SQL for my version of this view is available here. Now this is where the prerequisite for having set your course categories up in Moodle comes into play! As you can see in this SQL there is a rather large CASE statement. This is trawling through the returned subject names from the iSAMS query and setting up the category id for which Moodle course category they should belong in. You will need to edit this section to match your Moodle course categories and your subject names as defined in iSAMS. The final “ELSE 8” in the CASE statement is a category we made called “Unsorted Courses” on Moodle which will act as a “catch-all” should any courses not match anything in the CASE statement. The id number is the numeric id which you can see in the URL when you visit the category area in Moodle. For example: http://www.your-moodle.com/course/index.php?categoryid=5

You will probably want to adjust how the txtFullName and txtShortName fields are being created too. Just edit the SQL to suit what you want for your course set up. The final WHERE clause in the SQL statement is being used to exclude certain subjects from the course creation table, in my case “Private Study” lessons will not be included. Again, edit this section (or remove entirely) to match your requirements.

Create your database to copy the enrolment and course data into

The first step here is to create a new database to copy the results of your views over to. I have called mine “iSAMS-to-Moodle” and pretty much left everything as default. Once you have the database created we then need to import the data from the iSAMS views into this database.

1. Right click on your database in the Object Explorer pane and choose “Tasks > Import Data…”. This will launch the “SQL Server Import and Export Wizard”.
2. Click “Next >” to bypass the welcome page and then enter your SQL server name and choose between Windows Authentication and SQL Authentication. Once authenticated you need to choose your main iSAMS database and then click “Next >”.
3. Now choose the database you just created (iSAMS-to-Moodle in my case) and click “Next >”.
4. On the next screen select “Copy data from one or more tables or views” and click “Next >”.
5. Find the two views you created earlier and select them
6. Highlighting each destination table in turn, click “Edit Mappings…”
7. Ensure that “Create destination table” is selected and click “OK” for each table
8. Click “Next >” to move to the “Save and Run Package” screen. Ensuring that only “Run immediately” is selected, click “Next >”
9. Confirm the steps are as expected and then click “Finish” to copy the first import of data over
10. Click “Close” to close the wizard

Add indexes and schedule the data import operation

Having initially copied the results of our view over to our new database we can set up our indexes and schedule the data import operation to run in the background.

Indexes

Expand your new database in the Object Explorer pane and then expand the newly created tables. If you now right click on the Indexes folder and choose “New Index…” you can set up the required indexes for each table. As Moodle will be searching by the unique user attribute and by the course attribute I created a separate index for the txtEmailAddress field and the txtSetCode field on the EnrolData table. On the CourseData table I created a view for the txtCourseID field.

Scheduling the data import

Now we have set up our tables and indexes we return to the Data Import Wizard to create a SSIS package which we can then schedule to run at set times as a job in the SQL Server Agent.

1. Right click on your database in the Object Explorer pane and choose “Tasks > Import Data…”. This will launch the “SQL Server Import and Export Wizard”.
2. Click “Next >” to bypass the welcome page and then enter your SQL server name and choose between Windows Authentication and SQL Authentication. Once authenticated you need to choose your main iSAMS database and then click “Next >”.
3. Now choose the database you just created (iSAMS-to-Moodle in my case) and click “Next >”.
4. On the next screen select “Copy data from one or more tables or views” and click “Next >”.
5. Find the two views you created earlier and select them
6. Highlighting each destination table in turn, click “Edit Mappings…”
7. Ensure that for each destination table “Delete rows in destination table” is selected and click “OK”
8. Click “Next >” and then deselect “Run immediately” and select “Save SSIS Package”. Select “SQL Server” for the save location and choose your package protection level; then click “Next >” to continue
9. Give the package a name and description and authenticate against the server to store it on and click “Next >”
10. Confirm the settings and click “Finish” to save your package
11. Click “Close” to close the Data Import Wizard

Now we need to set up a scheduled job on the SQL server to run this SSIS package at regular points through the day.

1. Expand the “SQL Server Agent” section of the tree in “Microsoft SQL Server Management Studio” and then right click on the “Jobs” folder and choose “New Job…”
2. Give the job a sensible name and a description
3. Select the “Steps” page from the menu on the left hand side of the window
4. Click “New…” to open the “New Job Step” window
5. Give the step a name and then choose “SQL Server Integration Services Package” for the type
6. Type your server name in and authenticate against the server then click the “…” to locate the package you saved in step 9 of the previous section and click “OK” to return to the “New Job Step” window
7. Click “OK” to return to the “Job Properties” window and select the “Schedules” page from the menu on the left hand side of the window
8. I have defined two schedules for this job to run, one every weekday morning at 5:30 AM and another to run every weekday at 1:40 PM. Set this up however you see best for your environment
9. Select the “Notifications” link in the menu for the job properties and set up a notification for when the job fails. I have set it to email me
10. Click “OK” to close the “New Job” window
11. Right click the newly created job and choose “Start Job at Step…” to execute the job. If it fails at this point go back and work out why!

That is it for the set up required on your MSSQL server. In the next article I will look at the steps required on your Ubuntu Apache server to get it talking to your MSSQL Server.