Windows server: Is this the end of MS-SQL support?

Windows server: Is this the end of MS-SQL support?

by James Cracknell -
Number of replies: 7

http://connect.microsoft.com/VisualStudio/feedback/details/612369/misleading-error-message-in-sqlexception-when-using-entity-framework

Hmm, it seems that MSSQL can not begin+commit transactions when there is open recordset, that would be an ugly restriction. It might be easier to migrate to PostgreSQL or MySQL because if that is true problems like this are going to pop up randomly all over the place.

I suppose that a work around could be wrap our recordsets with transactions but that is going to break places that are not compatible with transactions and it could result in changes of behaviour too.

Alternatively we could read all recordset data into memory in sqlsrv drivers and close the recordsets immediately, but that could easily cause out of memory problems in some areas.

I am sorry for the bad news.

 
James Cracknell added a comment - 01/Jul/12 10:22 AM

Hi Petr

It does beg the question why Moodle continues to support MSSQL if there are so many issues with it.

Is there a guide out there for moving Moodle install from MSSQL to MySQL?

 
Petr Škoda (skodak) added a comment - 01/Jul/12 10:38 AM

It is very easy to say that XYZ supports ABC, but in the end it depends on what developers know, test and use and what the biggest installations install on their servers. All core and contrib developers would need to understand all those little and big problems we have with Oracle and MSSQL, so far that does not seem to be the case and I doubt it is going to change any time soon - for now if you do not want problems I would recommend PostgreSQL.

Migration is possible using the /admin/tool/dbtransfer/ tool. There are still some pending issues there (such as missing log action rewriting), hopefully it will be improved in the near future.

Average of ratings: -
In reply to James Cracknell

Re: Windows server: Is this the end of MS-SQL support?

by Petr Skoda -
Picture of Core developers Picture of Documentation writers Picture of Peer reviewers Picture of Plugin developers
You should have probably linked the original tracker issue MDL-34069 to prevent confusion caused by incorrect quoting of our communication in the tracker.

I have already posted a link to some new unit tests that illustrate the problem, I tried to work around this driver limitation myself, but so far did not find any solution.

Please note that this issue does not affect the freetds based mssql *nix driver, it seems to affect only the native Windows sqlsrv driver...
Average of ratings: -
In reply to Petr Skoda

Re: Windows server: Is this the end of MS-SQL support?

by James Cracknell -

I've now moved over to PostgreSQL today.  Going to shift the courses over via backup and restore and relaunch a new style at the start of the school year.  At the moment, no grades etc need to be transferred.

Just the cost of everyone having to run MS software to get everything working OK is prohibative enough.  I have to stick with IIS - but the issue of tinymce when it says .advanced-font-family or something like that instead of the usual language settings is also solved.  I wonder how many of the other issues I've been having were MSSQL related?  I am going to go through the issues in the next few days to see what was MSSQL and what is moodle itself being the cause.

Well 23rd July will be the end of MSSQL for me....

Average of ratings: Useful (1)
In reply to James Cracknell

Re: Windows server: Is this the end of MS-SQL support?

by Petr Skoda -
Picture of Core developers Picture of Documentation writers Picture of Peer reviewers Picture of Plugin developers
Sorry for the disappointment.
Average of ratings: -
In reply to Petr Skoda

Re: Windows server: Is this the end of MS-SQL support?

by Adam Van Auken -

Thank you James for starting this post and Petr for your response about freetds. I built a 2008 R2, MSSQL 2012, IIS, Moodle 2.3 system using the Microsoft Drivers 3.01 for PHP for SQL Server. My site was running fine but then I started having fastcgi timeouts when I tried restoring courses. I also timed out when trying to backup a course. Cron jobs also would fail. I researched this for dozens of hours and finally saw this post and switched to FreeTDS following these directions: http://docs.moodle.org/23/en/Installing_MSSQL_for_PHP#Using_FreeTDS_on_Windows

Now everything is working.

Average of ratings: -
In reply to James Cracknell

Re: Windows server: Is this the end of MS-SQL support?

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

My impression is that MS SQL generally causes few problems compared to some other DBs, and we have worked around worse problems than this in Moodle code. So, I don't think this is really cause for doom and gloom. Just yet one more pain to be aware of while writing Moodle code.

Average of ratings: Useful (1)
In reply to Tim Hunt

Re: Windows server: Is this the end of MS-SQL support?

by Richard Karel -

So I’m wondering how the above could impact someone’s efforts (like mine) to build a Moodle 2.x install on a Windows VM?  I don't want to get into database errors or the problems with blocks and modules not working with the MSSQL syntax. 

In other words, what should be my level of concern?  You say, oh it's nothing compared to other DBs, and I respect that, but I'd rather ask this question then get into the middle of a MSSQL problem scenario that cannot be fixed unless I change to postgre or mysql.  Does this make sense?

Average of ratings: -
In reply to Richard Karel

Re: Windows server: Is this the end of MS-SQL support?

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi

If your Windows Server is a VM anyway, why don't you try Linux? The Moodle developers work mainly on Open Source and as a result Linux/Apache/MySQL get tested along the way.

There are countless resources in the net. Here is my version: http://docs.moodle.org/22/en/Installing_Moodle_on_Debian_based_distributions.
Average of ratings: -