SQLServer

From DreamFactory
Jump to: navigation, search
(Windows)
 
(23 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
==General Notes==
 
==General Notes==
 
*When connecting to Azure SQL, you'll need to include the hostname in the user credentials (e.g. [email protected], rather than just username).
 
*When connecting to Azure SQL, you'll need to include the hostname in the user credentials (e.g. [email protected], rather than just username).
 
  
 
==Linux==
 
==Linux==
 
===Notes===
 
===Notes===
Beginning in 2016, Microsoft made the official SQLSRV driver available for Linux. If you are using Ubuntu 16.04 or later, Debian 8 or later, RHEL7/CentOS7 or later you can install this driver. This is '''highly preferred''' over the DBLIB/FreeTDS driver (php-sybase.)
+
Beginning in 2016, Microsoft made the official '''SQLSRV''' driver available for Linux. If you are using Ubuntu 16.04 or later, Debian 8 or later, RHEL7/CentOS7 or later you can install this driver. This is '''highly preferred''' over the DBLIB/FreeTDS driver (php-sybase.)
  
 
===SQLSRV===
 
===SQLSRV===
Line 11: Line 10:
 
**[[../../../APT/Ubuntu_16.04/Modules/SQLServer|SQLSRV on Ubuntu 16.04]]
 
**[[../../../APT/Ubuntu_16.04/Modules/SQLServer|SQLSRV on Ubuntu 16.04]]
 
*Microsoft Documentation, including instructions for other platforms:
 
*Microsoft Documentation, including instructions for other platforms:
 +
** [https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server MS Platform Instructions]
 
** [https://github.com/Microsoft/msphpsql Microsoft PHP SQLSRV on GitHub]
 
** [https://github.com/Microsoft/msphpsql Microsoft PHP SQLSRV on GitHub]
  
 
===DBLIB/FreeTDS===
 
===DBLIB/FreeTDS===
 
* In your package manager, install the PHP Sybase package.
 
* In your package manager, install the PHP Sybase package.
* Ubuntu/Debian example: <source lang=bash>$ sudo apt-get install php5-sybase</source>
+
* Ubuntu/Debian example: <source lang=bash>$ sudo apt-get install php7.1-sybase</source>
 
* No other configuration is necessary, unless you intend to use Microsoft SQL Server as your DreamFactory system database.
 
* No other configuration is necessary, unless you intend to use Microsoft SQL Server as your DreamFactory system database.
  
Line 22: Line 22:
  
 
If you intend to use Microsoft SQL Server as the DreamFactory system database, you will need to make a couple of FreeTDS configuration changes.
 
If you intend to use Microsoft SQL Server as the DreamFactory system database, you will need to make a couple of FreeTDS configuration changes.
First, locate your freetds.conf file. In a default Debian/Ubuntu/Mint install, this file is in /etc/freetds/freetds.conf. You'll be editing the [global] section of the file. Here's what the default looks like:
+
First, locate your freetds.conf file. In a default Debian/Ubuntu/Mint install, this file is in '''/etc/freetds/freetds.conf'''. You'll be editing the '''[global]''' section of the file. Here's what the default looks like:
 
<source lang=bash>
 
<source lang=bash>
 
[global]
 
[global]
Line 53: Line 53:
 
</source>
 
</source>
  
 +
Next you will need to set up a '''locales.conf''' file. There is an example one included in the freetds examples. Copy it to '''/etc/freetds/''':
  
Next you will need to set up a locales.conf file. There is an example one included in the freetds examples. Copy it to /etc/freetds/
 
 
<source lang=bash>
 
<source lang=bash>
 
sudo cp /usr/share/doc/freetds-common/examples/locales.conf /etc/freetds/
 
sudo cp /usr/share/doc/freetds-common/examples/locales.conf /etc/freetds/
 
</source>
 
</source>
 +
 
Then you need to edit this file with date formatting that matches that of your DreamFactory configuration. Since I use the default DreamFactory configuration, my changes to this file look like this:
 
Then you need to edit this file with date formatting that matches that of your DreamFactory configuration. Since I use the default DreamFactory configuration, my changes to this file look like this:
 
<source lang=bash>
 
<source lang=bash>
Line 67: Line 68:
  
 
Now you can being using Microsoft SQL Server as your system database.
 
Now you can being using Microsoft SQL Server as your system database.
 
 
==Windows==
 
'''Note:''' Starting in DreamFactory 2.3, drivers and extensions required for PHP 7 will need to be used, as reflected in these instructions.
 
*Download and install Microsoft ODBC Driver 11 for SQL Server (if not already installed). Currently this is available [http://www.microsoft.com/en-us/download/details.aspx?id=36434  here]. In 32-bit environments, install the x86 package. In 64-bit environments, install the x64 package.
 
*Download and install the appropriate SQL Server Native Client (if not already installed and SQL Server is not hosted on the same machine as DreamFactory). In 32-bit environments, install the x86 package. In 64-bit environments, install the x64 package. Currently the Microsoft SQL Server 2012 Native Client is available [http://www.microsoft.com/en-us/download/details.aspx?id=29065  here], and the Microsoft SQL Server 2008 R2 Native Client is available [http://www.microsoft.com/en-us/download/details.aspx?id=16978  here].
 
*DreamFactory uses PHP PDO drivers for DB connections. Full documentation on PDO_SQLSRV is available [http://php.net/manual/en/ref.pdo-sqlsrv.php  here]. You will need to download and extract the Microsoft Drivers for PHP for SQL Server. Currently these are available [https://github.com/Microsoft/msphpsql/releases/tag/4.1.0  here]. Be sure to download BOTH x86 zip files
 
*Copy the DLLs for your version of PHP into your PHP's extensions directory. E.g., for PHP 7, the DLLs are php_pdo_sqlsrv_7_ts.dll and php_sqlsrv_7_ts.dll (which were extracted from the download in the third step). (Rare case: if you are integrating with IIS as your web server, use the non-thread-safe DLLs instead (php_pdo_sqlsrv_7_nts.dll and php_sqlsrv_7_nts.dll).)
 
*Enable the copied DLLs in your php.ini file. Using the example DLLs from step #4, you would add the following lines to C:\Bitnami\dreamfactory-2.3.0-1\php\php.ini and save:
 
**  ''extension=php_pdo_sqlsrv_7_ts.dll''
 
**  ''extension=php_sqlsrv_7_ts.dll''
 
<br>
 
'''Note:''' If you are using DreamFactory 2.1.0-4 or earlier, you will require drivers and extensions for PHP 5.5 (see below).
 
*Enable the copied DLLs in your php.ini file. Using the example DLLs from step #4, you would add the following lines to C:\Bitnami\dreamfactory-2.1.0-4\php\php.ini and save:
 
**  ''extension=php_pdo_sqlsrv_55_ts.dll''
 
**  ''extension=php_sqlsrv_55_ts.dll''
 
*Restart your web server to apply these PHP configuration changes.
 

Latest revision as of 19:07, 9 January 2019

General Notes

  • When connecting to Azure SQL, you'll need to include the hostname in the user credentials (e.g. [email protected], rather than just username).

Linux

Notes

Beginning in 2016, Microsoft made the official SQLSRV driver available for Linux. If you are using Ubuntu 16.04 or later, Debian 8 or later, RHEL7/CentOS7 or later you can install this driver. This is highly preferred over the DBLIB/FreeTDS driver (php-sybase.)

SQLSRV

DBLIB/FreeTDS

  • In your package manager, install the PHP Sybase package.
  • Ubuntu/Debian example:
    $ sudo apt-get install php7.1-sybase
  • No other configuration is necessary, unless you intend to use Microsoft SQL Server as your DreamFactory system database.

Using Microsoft SQL Server as the system database with DBLIB/FreeTDS

NOTE: These changes only apply if you are using the dblib/freetds driver to connect to SQL Server. Now that Microsoft has made the sqlsrv driver available on Linux, Mac, and Windows, we highly recommend using it rather than dblib. These instructions are not necessary if you use sqlsrv.

If you intend to use Microsoft SQL Server as the DreamFactory system database, you will need to make a couple of FreeTDS configuration changes. First, locate your freetds.conf file. In a default Debian/Ubuntu/Mint install, this file is in /etc/freetds/freetds.conf. You'll be editing the [global] section of the file. Here's what the default looks like:

[global]
        # TDS protocol version
;       tds version = 4.2
 
        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff
 
        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10
 
        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.  
        # Try setting 'text size' to a more reasonable limit 
        text size = 64512

You need to edit/add values for the tds version, text size, and client charset. If any of these lines start with a semi-colon (;) it will need to be removed.

[global]
        tds version = 7.2
 
        text size = 20971520
 
        client charset = UTF-8

Next you will need to set up a locales.conf file. There is an example one included in the freetds examples. Copy it to /etc/freetds/:

sudo cp /usr/share/doc/freetds-common/examples/locales.conf /etc/freetds/

Then you need to edit this file with date formatting that matches that of your DreamFactory configuration. Since I use the default DreamFactory configuration, my changes to this file look like this:

[default]
   date format = %Y-%m-%d %H:%M:%S.%z

Last step: you need to restart your php processing service (if you're using one, such as php5-fpm) and your web server process (nginx or apache2.)

Now you can being using Microsoft SQL Server as your system database.