Be Objective: MS SQL Server Enterprise or MySQL enterprise & HA setup recommendations

Be Objective: MS SQL Server Enterprise or MySQL enterprise & HA setup recommendations

by Jerry Lau -
Number of replies: 6

Hello folks.

Leadership has approached me with the idea of looking whether if we should go with MS SQL-Server instead of MySQL, which we've had for years.

The reason being is that we have vendor support for MS platform and tools that would enable HA features on MS SQL-Server, which we don't have on MySQL at the moment.

Current design is:

  • RHEL 6.6 64-bit with 16GB of ram. PHP 5.6.26. MySQL 5.5 community. Moodle 3.2.7+ (will go to 3.4 in August).
  • OS and Moodle and DB engine on same VM server.
  • DB files are on a network drive actually.
  • moodledata files are on a fileshare.

I recommended still to go with MySQL but with enterprise version and re-architect it to have a separate DB server with at least 32GB of RAM. I would make the moodle server 32 GB of ram as well.

What I have not figured out yet is:

  1. How to set up failover and HA best platform configuration and options in a Linux platform.
  2. Where and the best setup for /moodledata folder.

We expect a huge amount of concurrent users to the platform and while our current platform is fine, we want it to be better supported, more formalizes, enterprise-ready, scaleable, and do it right once the first time ready for growth if we need it.

Thanks




Average of ratings: -
In reply to Jerry Lau

Re: Be Objective: MS SQL Server Enterprise or MySQL enterprise & HA setup recommendations

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators
Your biggest issue will be the lack of proper PHP drivers for MS SQL in PHP 7.


Moodle may even drop MS Sql support at some point too - see:
https://tracker.moodle.org/browse/MDL-59581

If you are looking for HA then typically you will also be very interested in performance - typically running Moodle on MS Sql has been slow - the php driver has been a bottleneck and most performance testing of Moodle under high load is done with Postgres and MySQL - so many of the more complicated queries in Moodle (enrolments/quiz stuff etc) may perform better under a database environment more widely used by the larger Moodle sites.

We have some very skilled DBA's here at Catalyst and experience with some of the largest Moodle sites in the world - we (and some other Moodle partners) can help provide more specific advice for your environment or help you to configure or manage HA systems. You might be able to convince your management team to pay for some consulting time from a Moodle partner with HA experience.

Good luck!


Average of ratings: Useful (1)
In reply to Dan Marsden

Re: Be Objective: MS SQL Server Enterprise or MySQL enterprise & HA setup recommendations

by Andrew Lyons -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi Dan,

Now that Microsoft have actually released their own MSSQL PHP drivers, I don't think we'll be dropping it any time soon.

The new PHP driver performs much better than the old one. It's still half the speed of the Postgres, Maria, and MySQL drivers for unit tests but that's on a largely untuned system.

However, I would still recommend against using MSSQL. Although we test daily against all databases, most HQ developer use Postgres and most developers use Postgres, MariaDB, or MSSQL.

Andrew

Average of ratings: Useful (2)
In reply to Andrew Lyons

Re: Be Objective: MS SQL Server Enterprise or MySQL enterprise & HA setup recommendations

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators

Thanks Andrew - nice to see they finally released drivers - nice to see it on github too!

wish they had done that before I had to wrangle external DB enrolment to an ms sql db under PHP 7...

Average of ratings: -
In reply to Jerry Lau

Re: Be Objective: MS SQL Server Enterprise or MySQL enterprise & HA setup recommendations

by Mark Picker -

Hi Jerry,

Adding my 2 cents as I find a lot of the responses on these forums re MS anything vs. Linux or MySQL etc. are always one sided responses and/or responses based on old versions or out of date approaches.  There are 2 or 3 people that will always respond with negative responses - though not on this thread yet smile

We run a large Moodle instance and don't find the database stack to be a performance bottleneck. Especially once tuned - which is true regardless of what platform you use. We run separate web and database clusters. All of our environments are in Windows clusters (and are VM's in VMware) with the MSSQL cluster using MSSQL AlwaysOn High Available.  This is a far better approach than the old SQL mirroring which wasn't supported by Moodle.  With AlwaysOn HA Moodle is oblivious to the cluster.

That said, if you are already familiar with Linux and MySQL I would question changing this.  I understand you can get MSSQL platform support from a new vendor but is it really worth it?

If you do go with MSSQL one issue we sometimes come across is with plugins that haven't been checked against the reservered word tool in Moodle - checks to make sure reserves words aren't being used as table or field names.  Haven't had this issue for a long time now but it was something that would previously pop up from time to time.

Regardless of what platform you end up using - I would also spend time looking into your options re cache services e.g. memcached.

Regards
Mark


Average of ratings: Useful (2)
In reply to Mark Picker

Re: Be Objective: MS SQL Server Enterprise or MySQL enterprise & HA setup recommendations

by Dan Marsden -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators

Mark - would be interesting if you could define what you mean by "large Moodle instance"  - here's one we define as large:
https://www.catalyst.net.nz/news/2-million-users-new-training-platform

Also would be interesting to hear whether you are using PHP 7 yet?

Most of my windows hosting experience dates back to the Moodle 1.X days, and we all know the PHP drivers for MS Sql back then were a massive issue - even when the MS Sql server stack was tuned well, it was the drivers themselves that were the problem. From what I hear, the MS supported drivers are a lot better now, although it seems to have taken a really long time for them to release drivers for PHP 7 which would concern me if I was still maintaining a server - although now that the drivers are in github maybe we can expect them to be better supported by the community?

Average of ratings: Useful (2)
In reply to Mark Picker

Re: Be Objective: MS SQL Server Enterprise or MySQL enterprise & HA setup recommendations

by Jerry Lau -

Anyone can contribute on HA platform design and best practices? How should we go about it and the tools & recommended/preferred settings on such type of a set up.

Thanks




Average of ratings: -