setting up freetds and php to access multiple mssql server

This tutorial use 2 microsoft sql server 2008 as database server target. server1 ip is 172.16.8.42 and server2 ip is 172.16.8.51. both mssql must listen on the same tcp/ip port 1433. freetds would be installed at server3 where php + apache are already exist.

AT SERVER3:

1. wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz or

ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz

2. tar -xzfv freetds-stable.tgz

3. cd freetds-0.91

4. ./configure – -enable-msdblib  – -with-tdsver=8.0 – -prefix=/usr/local/freetds

5. make && make install

6. cp include/tds.h /usr/local/freetds/include

7. cp src/tds/.libs/libtds.a /usr/local/freetds/lib

8. cd to php source or download the same version with the one currently installed. mine is php-5.2.4

9.  cd ext/mssql

10. phpize

11. ./configure – -with-mssql=/usr/local/freetds

12. make

13. php -i | grep extension_dir

14. ls {folder}/mssql.so

to check if mssql.so is there where it should be. {folder} should be the same as the result of command number 13

15. edit php.ini and add

extension=mssql.so

16. restart apache

17. cd /usr/local/freetds/etc

18. vi freetds.conf

add these :

[global]
#dump file = /tmp/freetds.log

[172.16.8.42]
host = 172.16.8.42
instance = WIN-KMLN76J5IFD
port = 1433
tds version = 8.0

[172.16.8.51]
host = 172.16.8.51
instance = WIN-VMJN77S5ISF
port = 1433
tds version = 8.0

 

and then save and exit. dump file is very useful for debugging in case freetds is not working. remove the # when you need to see freetds log. instance can be found at microsoft sql server management studio.

19. export TDSVER=8.0

20. export TDSPORT=1433

21. to make them loaded on boot time:

 vi /etc/rc.local

and add :

export TDSVER=8.0
export TDSPORT=1433

save and exit

22. testing server1 with tsql ( don’t forget to create user on server1 and server2 with enough privileges) :

/usr/local/freetds/bin/tsql -S 172.16.8.42 -p 1433 -U someuser

enter password. if you get these lines below, that means freetds is working from tsql:

Password:
locale is “en_US.UTF-8”
locale charset is “UTF-8”
using default charset “UTF-8”
1>

type quit to end session

23. do the same test for server2

24. testing with php script :

at documentroot of domain.com do vi mssqltest.php and add :

<?php

//testing server2
$conn = mssql_connect(“172.16.8.51”, “someuser”, “password”) or die(“Couldn’t connect to SQL Server”) ;
mssql_select_db( “dbname”, $conn );

//testing server1

//$conn = mssql_connect(“172.16.8.42”, “someuser”, “password”) or die(“Couldn’t connect to SQL Server”);

//mssql_select_db( “dbname”, $conn );

$query_result = mssql_query( “SELECT filedname FROM tablename”, $conn );
echo “The field number one is: “;
echo mssql_result ($query_result, 0, 0);
mssql_close($conn); // close connection
?>

25. from browser go to http://domain.com/mssqltest.php and you should see some characters after The field number one is: