Moodle

good news indeed!
please have a look at the recent rewrite of moodle database abstraction

Hello,

Thanks for the response. I will be reviewing the documentation over the week to regain my bearings.

Is there any possibility for this to be released under the next incremental (1.9.2) as well (or first)? I am expected to complete this project prior to June 20th, 2008.

Hello,
we can not do changes like this in stable branch If you want to make it work in 1.9.x you will have to write a new adodb driver, I would recommend coordinating it with adodb people upstream.

Petr

Assigned to Mike as requested. Thanks!

Just a quick update.

We have a patch ready that incorporates feedback from Eloy, it also passes the db unit tests.
Awaiting final review and approval from Eloy.

Yippeeeeeee!

We've had some excellent feedback from Eloy, now we are waiting for final feedback. Attaching the zip file here in case others want to take a look at it and provide feedback.

EDIT: this patch was tested using Moodle 2.0 Preview 1 (Build: 20100514), and SQL Server Driver for PHP 1.1. We are testing with Preview 3 today, and will post our results. We even welcome testing with our recently released v2.0 CTP2 binaries.

Moodle unit tests: lib/dml
1/1 test cases complete: 470 passes, 0 fails and 0 exceptions.
Run at Monday, 14 June 2010, 02:33 PM. Time taken: 4 secs. Using SimpleTest version 1.0.1.

Run functional database tests
Show passes as well as fails.
Databases:
•Current database (native/sqlsrv)

Running tests on: Current database (native/sqlsrv)
2/2 test cases complete: 687 passes, 0 fails and 0 exceptions.
Run at Monday, 14 June 2010, 02:38 PM. Time taken:

I reviewed it quickly today too, looks ok
the only potential problem I found is that it is not verify the OS, because the sqlsrv driver works only in Windows, right?

thanks for your contribution!

Petr

Yes, our drivers (SQLSRV & PDO_SQLSRV) work only on Windows.
It is written to inherit from the appropriate moodle and mssql base classes and extend only those required to use the driver effectively, and to retain current support for Linux/Apache via the MSSQL driver.

Test results from Moodle 2.0 Preview 3:

We ran into a bunch of issues getting the test system set up with Preview 3, then the tests wouldn't even pass on some other databases. Finally, this morning we got the system up and running with no changes to our tests required (and no change to our patch), and ran the tests. The results look great.

Moodle unit tests: lib/dml
1/1 test cases complete: 470 passes, 0 fails and 0 exceptions.
Run at Tuesday, 22 June 2010, 11:58 AM. Time taken: 11 secs. Using SimpleTest version 1.0.1.

Moodle unit tests: lib/ddl
1/1 test cases complete: 219 passes, 0 fails and 0 exceptions.
Run at Tuesday, 22 June 2010, 12:01 PM. Time taken: 3 secs. Using SimpleTest version 1.0.1.

Run functional database tests on: Current database (native/sqlsrv)
2/2 test cases complete: 689 passes, 0 fails and 0 exceptions.
Run at Tuesday, 22 June 2010, 12:02 PM. Time taken: 58 secs. Using SimpleTest version 1.0.1.

Woohoo!!

We are new to the process here, so bear with the question: now, do we need to reolve the issue and then check in the fix or do we check it in first and then resolve?

Hi Ashay,

let me perform here the last check (I had planned to do it today, but finally got the day out without doing so, sorry).

So tomorrow it will be the 1st thing I'll do, then you can commit the driver stuff and mark this as resolved (I've read Mike is having some problems with our cvs server lol, hopefully will be fixed by sys admin in a few hours).

Once you commit the driver we'll add the "extra" stuff to have it integrated with Moodle (environment, install, and a few new strings if I'm not wrong).

The last point is about to create some Docs (steps quide) in order to help people to get everything installed in their Windows boxes (PHP driver, SQL*Net native driver...) so Moodle can use it.

I would suggest one new section in the http://docs.moodle.org/en/Installing_MSSQL_for_PHP page to do that. There is already one section that can be replaced to have those instructions. Are you going to do that, Ashay?

I've already talked with Luis de Vasconcelos (he uses to maintain that page) and we have agreed about to erase some other sections (ODBTP, ODBC...) to another page, keeping exclusively the FreeTDS and the MS-PHP alternatives as the recommended ones. That will be done once the MS-PHP is documented.

Does everything above sound ok?

Congrats, sorry for the delay last week and ciao

PS: For my tests tomorrow... should I use the driver attached in this issue or the one I received by email on June 15th (from you)? I guess it's the same, but just to be 100% sure. TIA!

Hi Eloy,

Look forward to you giving it a good pounding for another round.

We'll commit as soon as we get the go-ahead from you tomorrow.

Do we need another issue for the "extra integration stuff" commit? If so, can you file one and have it assigned to the right person to commit?

Regarding documentation, I'd like to understand in further detail on what is required for the SQLSRV option content and I'll take it offline with Luis/you and our team's documentation expert.

For your testing, use the driver attached to this issue as we believe it is ready for commit.

Hi, FYI I've just created these:

• MDL-22906: About the integration of the driver with Moodle 2.0
• MDL-22907: About the documentation of the driver installation (OS)

Ciao

15093 changes committed:
We'll wait for the corresponding check-in for 22906 and the subsequent build for our testing and documentation effort.

Woohoo!! Thanks for all the help and guidance!!

This commit adds the following files;

sqlsrv_native_moodle_database.php
sqlsrv_native_moodle_recordset.php
sqlsrv_native_moodle_temptables.php

Yay!

Congrats! Just saw your driver coming in my last cvs update!

Working on MDL-22906 right now! Thanks!

Closing this. Any new bug/improvement will be a new issue under the Database MS SQL component. Here it's the summary page:

http://tracker.moodle.org/browse/MDL/component/10633

Ciao

Eloy, maybe I should wait for the next Preview 3 build and test this again, but here's what happened while I was testing this patch this morning (just before you closed this issue):

I installed SQL Server Driver for PHP 2.0 CTP2 (SQLServerDriverForPHP20.exe)

I installed Moodle 2.0 Preview 3 (Build: 20100624)

I applied MDL-15093_SQLSRV-Patch.zip (I replaced the build 20100624 .php files with those in this patch)

I loaded the CTP2 driver in PHP by adding this to php.ini:

;SQLServerDriverForPHP11:
extension=php_sqlsrv_52_ts_vc6.dll
;SQLServerDriverForPHP20
extension=php_pdo_sqlsrv_52_ts.dll
extension=php_sqlsrv_52_ts.dll
extension=php_pdo.dll

I also left FreeTDS running i.e.
extension=php_dblib.dll

When I ran the Moodle installation the following two options were available on the Choose database driver page:

SQL*Server Microsoft (native/sqlsrv)
SQL*Server FreeTDS (native/mssql)

I selected the SQL*Server Microsoft (native/sqlsrv) option and clicked next.
I entered my database details on the Database settings page.
When I clicked Next I got the following message:

-----------------------------------------------------------------------------------------------------
Error: Database connection failed

It is possible that the database is overloaded or otherwise not running properly.

The site administrator should also check that the database details have been correctly specified in config.php

SQLState: IMSSP
Error Code: -49
Message: The SQL Server Driver for PHP requires the SQL Server 2008 Native Client ODBC Driver (SP1 or later) to communicate with SQL Server. That ODBC Driver is not currently installed. Access the following URL to download the SQL Server 2008 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712
SQLState: IM002
Error Code: 0
Message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------------------------------------------------------------------

It's interesting that it's complaining about a SQL Server 2008 Native Client ODBC Driver. What is this?
And why is it looking for a MSSQL 2008 driver when I'm running MSSQL 2005?

Also, that message is slightly misleading. The part that says: "The site administrator should also check that the database details have been correctly specified in config.php" is not correct because, at this stage in the setup, no config.php file has been created yet.

If I restart the Moodle setup and pick the FreeTDS library then the installation proceeds normally.

If I disable FreeTDS then only SQL*Server Microsoft (native/sqlsrv) is available when I run the Moodle setup. It still produces the above Error Code: -49 error and I can't finish the Moodle setup.

Michael,

I just noticed something on the System Requirements (SQL Server Driver for PHP) page at:
http://msdn.microsoft.com/en-us/library/cc296170(SQL.90).aspx.

It lists the following as a requirement for running the SQL Server Driver for PHP:

• Microsoft SQL Server 2008 Native Client installed on the same computer on which PHP is running.

So I'm wondering, is it really necessary to have the 2008 client installed on my web server when I'm actually running MSSQL 2005?

Note (maybe this is relevant???):
On my dev setup MSSQL is installed on one virtual box (SQLDEV) and IIS, PHP and Moodle on another virtual box (WEBDEV). The SQL Server Driver for PHP is installed on SQLDEV, i.e. the same box as MSSQL.

Yes Luis,

I found exactly the same problem first time I installed it here. Microsoft SQL Server 2008 Native Client is required (by PHP/sqlsrv) always. No matter you're using 2005 or no. Surely it provides support for different bits in the driver. Once installed it connects like a charm.

It doesn't matter if it's the same box than the one running the SQL*Server or no. It must be in the same box you are running php (webserver). And always be, at least the 2008 Native client.

It's one of the important things that docs will need to have in bold/underline/uppercase/red

Ciao

> It's interesting that it's complaining about a SQL Server 2008 Native Client ODBC Driver. What is this?
> And why is it looking for a MSSQL 2008 driver when I'm running MSSQL 2005?
Our drivers, SQLSRV and PDO_SQLSRV, are designed to be built on top of SQL Server Native Access (SNAC) ODBC API. See our v2.0 CTP2 announcement blog post that provides some details on the architecture: http://blogs.msdn.com/b/sqlphp/archive/2010/04/19/sql-server-driver-for-php-2-0-ctp-adds-php-s-pdo-style-data-access-for-sql-server.aspx

SNAC is our unmanaged code (C/C++) library implementing ODBC, it is a very robust & performant connectivity option for SQL Server, and it is designed to support N & N-1 versions of SQL Server. Meaning, SNAC 2008 will support SQL Server 2008 and SQL Server 2005. At this point it will be good to note that "R2" releases don't change major version number, so SNAC 2008 R2 can also be used with our drivers. This provides my team to link to the "right" version of SNAC ("right" being influenced by several factors) to best support our PHP community.

This is why we linked to it in the commit comments too, and on our v1.1 & v2.0 CTP2 download pages. Arguably, we could include the SNAC driver with our package but it is best for the DBA/developer to decide which one best fits their needs.

And, as Eloy correctly says, the drivers (SNAC & SQLSRV/PDO_SQLSRV) always need to be installed on the machine/vm where PHP is installed.

BTW, our drivers also support connecting to SQL Azure - it's just s small connection string change. I wasn't sure how Moodle 2.0 is being positioned for the "cloud", so I didn't push for this support in this patch. Let me know if you all are interested in the SQL Azure option in Moodle 2.0 installation.

Ashay

Hi Luis,

Some notes;
• I have not tested using the 2.0 driver. Its definitely not built to work with PDO, yet. I haven't tried to use the 2.0 native either. So for now, if you can use the 1.1 driver.

• You can find all the details you need about download, installation and operation of the MS SQL driver for PHP here: http://msdn.microsoft.com/en-us/library/ee229548(v=SQL.10).aspx you can download the 1.1 driver : http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9

• It must be a non-thread-safe (NTS), not TS. I used PHP 5.32, so I use the VC9 driver. My php.ini is such:

...
[PHP_SQLSRV]
extension=php_sqlsrv_53_nts_vc9.dll
...

• I run IPv6 so I occasionally have problems with 'localhost' translating into ':1' which gums up the works. I use an explicit IPv4 of '127.0.0.1' and everyone is happy.

• For development and initial testing I have everything on my local windows 7 box. PHP script -> IIS/FastCGI -> PHP-CGI.exe -> sqlsrv v 1.1 driver/SNAC -> SQL*Server 2008.

• What was committed for MDL-15093 yesterday is only the added sqlsrv_* files. Additional work must be done to integrate. See MDL-22906 for more info on that.

Here is what I use to install/setup:

0. You will need 15093 (sqlsrv_*) and 22906 (integration) applied.

1. Prior to installing moodle, choose a name for your database and create the database on SQL*Server 2005+. Insure you have provided server login and database access right. During installation Moodle will create the entire needed schema. You may wish to use an administrative account for creating the database. After which create an account with only sufficient rights to read and write but not create/alter/drop.

2. Execute the following SQL commands: (this is for transaction isolation)

USE MASTER
GO
ALTER DATABASE <your-database-name> SET READ_COMMITTED_SNAPSHOT ON
GO

3. Begin installation of Moodle. See <link> for more information.

4. At the 'Select database', select SQL*Server Microsoft (sqlsrv/native).

• Steps 1 & 2 can be done from the Microsoft SQL Server Management Studio

Thats it.

This is my .patch file which I use to implement both MDL-15093 and MDL-22906. Please note ths is not the official integration.

Will the "ALTER DATABASE <your-database-name> SET READ_COMMITTED_SNAPSHOT ON" script be part of the Moodle installer, or will site admins have to run it manually as part of their Moodle 2 upgrade?

The "ALTER DATABASE <name-of-db> SET READ_COMMITTED_SNAPSHOT_ON" command will have to be done by site/db admin after setting up the new Moodle 2.0 db and before Moodle 2.0 install. We have not tested upgrade scenarios as we are not familiar enough with the process to fit into our timelines, we can definitely vouch for new installs.

Note the ALTER DATABASE applies both for the FreeTDS and the Native drivers, and it is already in the common section of the Docs (as one pre-installation step, to be executed manually):

http://docs.moodle.org/en/Installing_MSSQL_for_PHP#Installation_overview

Ciao

If I upgrade a Moodle 1.9 website to Moodle 2.0 and that 1.9 site is currently connecting to MSSQL via FreeTDS, how do I switch the upgraded 2.0 site over to the new Microsoft SQL driver when I do the upgrade?

I've got both the FreeTDS driver and the new Microsoft driver loaded in my php.ini file on my dev box and they seem to run well together. I have a 1.9 instance of Moodle connecting to MSSQL via FreeTDS and a 2.0 instance connecting to MSSQL via the new Microsoft SQL driver. Both instances of Moodle are running nicely. But now the next phase of testing involves UPGRADING the 1.9 site to 2.0 AND switching over to the new Microsoft SQL driver. How should this be done?

I disabled the FreeTDS extension in php.ini and now the 1.9 site is obviously unable to connect to the 1.9 database. I get the dreaded 'Database connection failed' error:

Error: Database connection failed.
It is possible that the database is overloaded or otherwise not running properly.
The site administrator should also check that the database details have been correctly specified in config.php

Obviously, this is because Moodle 1.9 doesn't work with the Microsoft SQL Driver. What must I do to make the upgraded 2.0 site work with the Microsoft SQL driver?

Pretty simple:

FreeTDS = mssql (or mssql_n for 1.9 upgraded sites)
MS PHP Driver = sqlsrv

just change it in the config.php file and you'll be, automatically, using it in the next requested page.

Ciao

Thanks. So you can switch between the two drivers at any time by pointing $CFG->dbtype to the driver that you want to use. I will try it...

Eloy crosses his fingers and toes...