Databases: Native Support for Microsoft SQL please?

Databases: Native Support for Microsoft SQL please?

by Chris Simpson -
Number of replies: 9

We have just installed Moodle at our college on a Test Server, and have started tinkering about with it. Excellent Product!

However, being an FE College, our systems are pre-dominantly Microsoft based, and the support mechanisms for alternative databases are not in place.

Is there any reason why Moodle doesn't natively support Microsoft SQL? PHP supports Microsoft SQL, therefore the task shouldn't mean major redevelopment work.

I do understand that there are ardent "Microsoft" haters out there in the Open Source Fraternity, and some of their arguments may be true, however, it would be of great advantage to colleges such as ours (FE) for support and integration reasons to have Moodle using Microsoft SQL as its database engine.

I would be very keen for Moodlers in a similar situation to support my request, and hopefully the Moodle Developers will consider this route in further developments.

Average of ratings: -
In reply to Chris Simpson

Re: Databases: Native Support for Microsoft SQL please?

by Michael Penney -
I do understand that there are ardent "Microsoft" haters out there in the Open Source Fraternity

Hi Chris, I'd say its more a case of MSSQL costing a good deal of money and developers not necessarily having a pile of spare cash lying around to spend on an MSSQL license.

One thing you could do is look at how much your institution is saving by using Moodle compared to Blackboard or WebCT and put some of that savings back into the system by contracting a Moodle parter or other programmer to write the MSSQL table descriptions and keep them updated.
Average of ratings: -
In reply to Michael Penney

Re: Databases: Native Support for Microsoft SQL please?

by Chris Simpson -

Hi Michael (Mike?)

Being an Educational Establishment, we do get big discounts on Microsoft Software, and SQL Server is therefore a more affordable option.

All our management systems, and the majority within the UK FE Education Sector, are developed by a 3rd Party Companies, and are all predominantly based on Microsoft SQL and it would just make sense to enable Open Source Products, particularly those based at the educational market to be compliant / supportable with the "Structures" and "Systems" that already exist within our College Networks.

As for your other suggestion, Moodle is a rapidly developing software application, and I don't think it would be practical to keep the database php scripting functions continually updated.

Average of ratings: -
In reply to Chris Simpson

Re: Databases: Native Support for Microsoft SQL please?

by John Papaioannou -
As for your other suggestion, Moodle is a rapidly developing software application, and I don't think it would be practical to keep the database php scripting functions continually updated.

Without meaning to sound aggressive, it doesn't sound fair to me to say "I want this feature developed, but it's not practical for me to help out".

You can always try Moodle with MySQL, and if after this trial you still believe that a) you need Moodle, b) you need it with MSSQL, c) you don't want to get bogged down with doing it in-house, you can sponsor moodle.org to develop this feature. This way you will have both the feature you want, and the proverbial warm fuzzy feeling associated with giving back to the Moodle community. Michael already pointed this out, but I couldn't resist. wink

Regards,
Jon
Average of ratings: -
In reply to Chris Simpson

Re: Databases: Native Support for Microsoft SQL please?

by Samuel Cochran -
Actually Moodle almost already supports MSSQL. The database engine being used is ADODB which supports a whole host of databases, so abstractiong isn't a problem. The problem is database/table creation scripts.

All databases have different SQL syntax variations, and while MSSQL is fairly good in terms of SELECT, INSERT, UPDATE and DELETE operations, it is totally different when doing CREATE DATABASE and CREATE TABLE operations. For this reason Moodle doesn't yet support MSSQL: it doesn't know how to install itself.

Hiring a developer to create an MSSQL script therefore becomes somewhat practical: Get them to write an install script for MSSQL (ie in <moodle>/lib/db) and modify the install.php and admin/index.php files, check through the code for mysql/postgresql-specific queries and modify if neccessary, check for other database-specific settings.

It is a fair task, but certainly not impractical.

I think a developer's database guide that outlines the intricacies of each database and how to make sure queries work with each would be a good development which could ease the task of keeping everything compatible.
Average of ratings: -
In reply to Samuel Cochran

Re: Databases: Native Support for Microsoft SQL please?

by Richard Wendel -
Actually, if the database schema were generated and kept in a ER diagraming model (Entity Relationship model for those of you in Palm Beach County), it would be as easy as generating the creation scripts directly from the model, as most contain drivers for multiple databases (like Visio for Enterprise Architects, but b/c the model is a reverse engineer via ODBC, its not a good model to then forward engineer Oracle or MSSQL from (I have tried and there were something  like 400 manual edits I had to go through, mostly because a) Visio didn't assume 'not null' col. attr for primary key col b) the primary key and indexes were not 'named' so the driver for the Oracle generate blew up c) mapping col types for the different databases. AND my machine locked up while I was doing it...).
Average of ratings: -
In reply to Chris Simpson

Re: Databases: Native Support for Microsoft SQL please?

by Martín Langhoff -
There isn't any particular hate towards anyone. It's just that we focus on the tools we use everyday, and support for the Moodle installs we run. As Michael points out there's a serious financial impact to running MSSQL.

If there's enough people using MSSQL, the MSSQL patch will naturally evolve and get better (you've tried it, right?) and perhaps even become official. But the truth is that it's a bit of work to get it going, and ongoing involvement to keep it running correctly over time, as the Moodle code evolves.

You can also look at it as an opportunity: once you get your MySQL setup past your admins, imagine all the cool applications you'll be able to run! All the LAMP applications are out there, rocking the world, and most of them are free wink

(In any case, there's a good change I'll end up working a bit on the MSSQL patch in the medium term, but not soon.)
Average of ratings: -
In reply to Martín Langhoff

Re: Databases: Native Support for Microsoft SQL please?

by David Evans -

I also am looking into mssql as a database for moodle. Our backend database is mssql and to have our IT department support us we need to connect to that db. I am considering a temporary Mysql connection with batch tranfer of certain tables to maintain interconnections. However, potentially sponsering a mssql addition has merit, perhaps a group of corporate sponsers could join together on this. I am willing to look into this further.

regards

Dave 

Average of ratings: -
In reply to Chris Simpson

Re: Databases: Native Support for Microsoft SQL please?

by Gregory Krohne -
We're trying to get MS SQL working with Moodle version 1.5 in this thread:

http://moodle.org/mod/forum/discuss.php?d=31312

There are code attachments in there that will get you a good part of the way. The install script still needs work, at the very least. If you get more of it completed, kindly post it back for the rest of us.
Average of ratings: -
In reply to Chris Simpson

Re: Databases: Native Support for Microsoft SQL please?

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

My $00.02...

  • MySql is free, dead easy to install and you can get an awful long way with the amount of 'support' knowledge that an afternoon's reading and a couple of cups of coffee will get you.
  • The database is a central (read important) part of Moodle. Like it or not there are no practical standards for SQL databases so different systems require not insignificant additional code and testing. This dilutes the efforts of the developer community that could be better expended doing something else.
  • MsSql support in PHP is, perhaps, not one of its best and most consistent features. This in itself is likely to be a source of support problems.
  • MsSql is Micro$oft - 'nuff said smile
Average of ratings: -