Goal
This article describes how to configure your project so you can connect to MSSQL server. This article specifically looks at the drupal module sqlsrv https://www.drupal.org/project/sqlsrv but it can be adapted to work for any PHP application
Install
The install steps mentioned on the sqlsrv page https://www.drupal.org/docs/contributed-modules/drupal-driver-for-sql-server-and-sql-azure/install mention the following:
Linux Installation
- Install Microsoft SQL Server
- Install the Microsoft ODBC Drivers
- Install the PDO drivers, sqlsrv and pdo_sqlsrv
- Verify that the php modules are enabled with phpinfo() from both CLI and web.
- Copy the drivers directory from the module to the web root.
- Install Drupal from the web interface.
Let us go through each one and see how they translate to Platform.Sh
0. Platform.Sh project
We need a Platform.Sh project to work with. If you already have a drupal installation set up, great!
If not, feel free to start with a fresh one based on the drupal 8 template https://github.com/platformsh-templates/drupal8/
Or click the deploy on Platform button below:
1. SQL Server
Iām going to assume you already have one. But if not, you can set one up on azure if you like.
2. MS SQL ODBC Driver
While the official instructions do mention unixODBC https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu17 we are not going to be installing those.
In stead, we are going to install FreeTDS https://github.com/FreeTDS/freetds an opensource implementation of the unixODBC that is easier to install and is actively maintained.
We are going to leverage brew brew.sh to save us the hassle of compiling it ourselves.
Create a file install-odbc.sh and commit it to your repository.
run() {
# Run the compilation process.
cd $PLATFORM_CACHE_DIR || exit 1;
if [ ! -f "${PLATFORM_CACHE_DIR}/.linuxbrew/bin/isql" ]; then
install_brew
install_unixodbc
copy_lib_to_cache
else
copy_lib_from_cache
fi
}
copy_lib_to_cache() {
echo "Copy to cache..."
cp -Rfv $PLATFORM_APP_DIR/.linuxbrew $PLATFORM_CACHE_DIR
}
copy_lib_from_cache() {
echo "Copy from cache..."
cp -Rfv $PLATFORM_CACHE_DIR/.linuxbrew $PLATFORM_APP_DIR
}
install_brew() {
echo "Installing homebrew"
mkdir -p /app/.linuxbrew
echo "Downloading tarball from master repository"
curl -SsL https://github.com/Homebrew/brew/tarball/master -o brew.tar.gz
echo "Unpacking into .linuxbrew folder"
tar xzf brew.tar.gz --strip-components 1 -C /app/.linuxbrew/
rm brew.tar.gz
eval $(/app/.linuxbrew/bin/brew shellenv)
brew analytics off
}
install_unixodbc() {
brew install openssl
brew install freetds
}
run
3. Install PDO drivers
The PDO drivers can be found on github https://github.com/Microsoft/msphpsql/ but we cannot use pecl to install them. We can however, compile them from source.
Create a new file install-mssql-php.sh that does the compilation
run() {
# Run the compilation process.
cd $PLATFORM_CACHE_DIR || exit 1;
if [ ! -f "${PLATFORM_CACHE_DIR}/msphpsql/modules/sqlsrv.so" ]; then
ensure_source
checkout_version "$1"
compile_source
fi
copy_lib
enable_lib
}
enable_lib() {
# Tell PHP to enable the extension.
echo "Enabling extension."
echo "extension=${PLATFORM_APP_DIR}/sqlsrv.so" >> $PLATFORM_APP_DIR/php.ini
echo "extension=${PLATFORM_APP_DIR}/pdo_sqlsrv.so" >> $PLATFORM_APP_DIR/php.ini
}
copy_lib() {
# Copy the compiled library to the application directory.
echo "Installing extension."
cp -rv $PLATFORM_CACHE_DIR/*/source/*/modules/*.so $PLATFORM_APP_DIR
}
checkout_version () {
# Check out the specific Git tag that we want to build.
git checkout "$1"
}
ensure_source() {
# Ensure that the extension source code is available and up to date.
if [ -d "msphpsql" ]; then
cd msphpsql || exit 1;
git fetch --all --prune
else
git clone https://github.com/microsoft/msphpsql.git
cd msphpsql || exit 1;
fi
}
compile_source() {
# Compile the extension.
export LIBRARY_PATH=/app/.linuxbrew/lib
cd source
bash packagize.sh
cd pdo_sqlsrv
phpize
./configure --with-libdir=/app/.linuxbrew/include CXXFLAGS="-I/app/.linuxbrew/include"
make
ls
cd ..
cd sqlsrv
phpize
./configure --with-libdir=/app/.linuxbrew/include CXXFLAGS="-I/app/.linuxbrew/include"
make
ls
cd ..
cd ..
}
ensure_environment() {
# If not running in a Platform.sh build environment, do nothing.
if [ -z "${PLATFORM_CACHE_DIR}" ]; then
echo "Not running in a Platform.sh build environment. Aborting installation."
exit 0;
fi
}
ensure_arguments() {
# If no version was specified, don't try to guess.
if [ -z $1 ]; then
echo "No version of the extension specified. You must specify a tagged version on the command line."
exit 1;
fi
}
ensure_environment
ensure_arguments "$1"
run "$1"
Putting 2 and 3 together In the build hook.
We can now call the bash scripts from our build hook
# The hooks executed at various points in the lifecycle of the application.
hooks:
# The build hook runs after Composer to finish preparing up your code.
# No services are available but the disk is writeable.
build: |
set -e
#install odbc via brew
bash install-odbc.sh
#install mssql by compiling from source (pass specific version to compile)
bash install-mssql-php.sh v5.9.0
4. Verify installation (php_info)
platform ssh 'php -i | grep sqlsrv'
This should return something like this:
Registered PHP Streams => https, ftps, compress.zlib, php, file, glob, data, http, ftp, compress.bzip2, phar, zip, sqlsrv
PDO drivers => mysql, sqlite, sqlsrv
pdo_sqlsrv
pdo_sqlsrv support => enabled
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240
pdo_sqlsrv.log_severity => 0 => 0
pdo_sqlsrv.set_locale_info => 2 => 2
sqlsrv
sqlsrv support => enabled
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.SetLocaleInfo => 2 => 2
sqlsrv.WarningsReturnAsErrors => On => On
SSH_ORIGINAL_COMMAND => php -i | grep sqlsrv
$_SERVER['SSH_ORIGINAL_COMMAND'] => php -i | grep sqlsrv
$_ENV['SSH_ORIGINAL_COMMAND'] => php -i | grep sqlsrv
Connection to ssh.eu-3.platform.sh closed.
5. Copying the drivers
We already did that
6. Install Drupal
Out of scope for this article.
Conclusion
Now you are ready to input your remote MSSQL server connection details, and connect to it via PHP.