Windows server: php free tds to talk to MSSQL

Windows server: php free tds to talk to MSSQL

by Heather P -
Number of replies: 8

We are in the process of moving to IIS7.5 and windows 2008 R2 from Apache and windows 2003. The moodle database is on MySQL but we make a few calls to two different Microsoft SQL databases in two additional blocks so we need the appropriate extension in the php.ini file. We are also upgrading php.
http://docs.moodle.org/en/Installing_MSSQL_for_PHP says for the free TDS extension to create a conf file with the database detail in, but this is describing it as if it is the main moodle database. I'm new to this bit and confused, can I put details of two different SQL databases, potentially on 2 different servers into this file? Or will that just cause the server to get confused too?

Previously we just used the standard MSSQL extension that came with php.

Thanks

Heather

Average of ratings: -
In reply to Heather P

Re: Windows server: php free tds to talk to MSSQL

by Jason O'Brien -

If you are on Moodle 2.0, you definitely want to use the php_sqlserv extension from microsoft.

 

If you are still on 1.9, you need to use FreeTDS.  The standard MSSQL extension won't cut it for moodle.  This requires a conf file in the ROOT of C:\, not the moodle root.  As far as I know you should only have 1 DB server piece in there.  If you need to switch between servers, I'd create 2 files and switch them out as needed.

Average of ratings: -
In reply to Jason O'Brien

Re: Windows server: php free tds to talk to MSSQL

by Heather P -

Ahh. I don't think that will work.

We have the moodle configured to use MYsql for its database. I then use the ilp block that pulls data from and SQL database on server Fred, and a time table block that pulls data from an SQL databse on server George.

If you can only have one SQL server in your config file with the Free  TDS version that would imply we can't do that anymore. We are currently on a 1.9 version with an eye to the future for version 2.

Actually if I think about it that would also mean if you were using SQL for your moodle database would you have issues trying to put more than one moodle on the same server?

Average of ratings: -
In reply to Heather P

Re: Windows server: php free tds to talk to MSSQL

by Jason O'Brien -

The FreeTDS conf file goes on the web server, not the DB Server.

 

If you were entirely on MSSQL, you could link the servers together and use 4-part naming from inside moodle. Then moodle would only really be talking directly to 1 MSSQL server but be able to access any DB on any MSSQL server you wanted it to.

 

I don't know much about MySQL and if it's capable of something similar.

Average of ratings: -
In reply to Jason O'Brien

Re: Windows server: php free tds to talk to MSSQL

by Luis de Vasconcelos -
Picture of Particularly helpful Moodlers

To say it differently, the FreeTDS .conf file goes on the server where you're running PHP.

On Windows you can actually put the FreeTDS conf file anywhere you want! To make this work set an environment variable called FREETDS and then point it to the location where you have the .conf file.

After a restart PHP will be able to locate the .conf file and load FreeTDS.

http://www.freetds.org/userguide/envvar.htm

Average of ratings: -
In reply to Heather P

Re: Windows server: php free tds to talk to MSSQL

by Luis de Vasconcelos -
Picture of Particularly helpful Moodlers

In Moodle 2 you should connect to your main Moodle database using the SQL Server Driver for PHP from Microsoft

PHP is able to load both the FreeTDS driver AND the Microsoft SQL Server Driver for PHP. First install FreeTDS as normal by enabling the FreeTDS .dll in your php.ini file and setting the freetds.conf file. Then install the Microsoft SQL Server Driver for PHP using the installer.

Then restart the web server. PHP will recognise both drivers and will be able to connect to MSSQL using either of the two. Both will be available to your scripts. For the Moodle setup you should select SQLSERV as the $cfg->dbtype so that the Microsoft driver is used.

Are your blocks not able to use the Microsoft driver? Do they have their own connection strings? Use mssql_n to connect using FreeTDS. However, I don't know what kind of impact that using two different database connection protocols to connect to MSSQL will have. I suspect it might be problematic. You'll have to test it thoroughly... That's if you really can't use the Microsoft driver to connect the blocks to MSSQL...

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Windows server: php free tds to talk to MSSQL

by M Johns -

Thank you for this information.  I'm creating a new installation for (WIMP) which is a requirement in our environment.  However, the issue I'm running into is reporting.  I cannot seem to get reporting to work properly.

Whenever I activate stats and configure reporting options - I get an error pointing to line #17 in the /custom/showreports.php file.  I'm clueless about this and wonder if there is some issue with the MSSQL database.

I typically do WAMP installs so this FreeTDS vs php_sqlserv info is great to have.  Is there some resource on optimal settings for reporting purposes on a WIMP stack?

Average of ratings: -
In reply to M Johns

Re: Windows server: php free tds to talk to MSSQL

by Luis de Vasconcelos -
Picture of Particularly helpful Moodlers

What code does line 17 in the /custom/showreports.php file contain?

Is showreports.php a custom report that you have developed?

And what is the error that you're getting? The best way to get the most information about errors is to switch on the debugging option in Moodle. It will display detailed information about the problem. See http://docs.moodle.org/en/Debugging for details.

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Windows server: php free tds to talk to MSSQL

by M Johns -

Hi Luis;

the showreports.php is a reporting page that is part of the Moodle 1.9.8 environment.  I've upgraded to 2.0 and am not getting the errors, though I've yet to get statistics working.

Average of ratings: -