Install SQL Server in Homestead

Version Date Notes By
0.1 2017-06-07 Initial release FPA
0.2 2018-04-20 Updated repository url at step 1.1 FPA
0.3 2022-03-11 Updated PHP version to 8.x BRB

Source: https://www.microsoft.com/en-us/sql-server/developer-get-started/php/ubuntu

1. Install SQL Server

In order to be able to install SQL Server in homestead, you must have at least 4096 MB of memory defined in your Homestead.yaml file.

1.1 Register the Microsoft Linux repositories and add the keys

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list > /etc/apt/sources.list.d/mssql-server-2017.list
exit

1.2 Install SQL Server

sudo apt-get update
sudo apt-get install mssql-server

1.3 Setup your SQL Server

sudo /opt/mssql/bin/mssql-conf setup

The configuration setup will ask you to accept the license terms and define a password for the system administrator (sa). Please anotate the entered password because you will need it later.

1.4 Install the ODBC Driver and SQL Command Line Utility for SQL Server

sudo su
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-tools.list
exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install mssql-tools
sudo apt-get install unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

After installing SQLCMD, you can connect to SQL Server using the following command (replace yourpassword with the password entered in step 1.3):

sqlcmd -S localhost -U sa -P _yourpassword_
1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements.

2. Install the PHP Driver for SQL Server

Replace 8.x in the commands below with your php version. Run php -vcommand to identify your running version of php.

sudo pecl install sqlsrv pdo_sqlsrv
sudo echo "extension=sqlsrv.so" >> /etc/php/8.x/mods-available/sqlsrv.ini
sudo echo "extension=pdo_sqlsrv.so" >> /etc/php/8.x/mods-available/pdo_sqlsrv.ini
sudo ln -s /etc/php/8.x/mods-available/sqlsrv.ini /etc/php/8.x/cli/conf.d/20-sqlsrv.ini
sudo ln -s /etc/php/8.x/mods-available/sqlsrv.ini /etc/php/8.x/fpm/conf.d/20-sqlsrv.ini
sudo ln -s /etc/php/8.x/mods-available/pdo_sqlsrv.ini /etc/php/8.x/cli/conf.d/20-pdo_sqlsrv.ini
sudo ln -s /etc/php/8.x/mods-available/pdo_sqlsrv.ini /etc/php/8.x/fpm/conf.d/20-pdo_sqlsrv.ini

After that you must restart nginx and php services by executing the following commands.

sudo service nginx restart
sudo service php8.x-fpm restart

3. Use SQL Server in your project

3.1 Update .env file with your connection settings

Replace yourdatabasename and yourpassword with your database name and password defined in step 1.3, respectively.

# .env

DB_CONNECTION=sqlsrv
DB_HOST=127.0.0.1
DB_PORT=1433
DB_DATABASE=_yourdatabasename_
DB_USERNAME=sa
DB_PASSWORD=_yourpassword_

3.2 Add sqlsrv connection settings to config/database.php file

# config/database.php

'connections' => [

    // ...

    'sqlsrv' => [
        'driver'   => 'sqlsrv',
        'host'     => env('DB_HOST', 'localhost'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset'  => 'utf8',
        'prefix'   => '',
        'options'  => [
            17   => false, // TODO: temporary - PDO::ATTR_STRINGIFY_FETCHES
            1005 => true   // TODO: temporary - PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE
        ]
    ],

],

php artisan migrate:refresh or php artisan migrate:fresh doesn't play well with SQL Server. In order to automate the database drop and recreation you might create the following script in your project root folder. Once again, replace yourdatabasename and yourpassword with your database name and password defined in step 1.3, respectively.

# recreate-sqlsrv-database.sh

sqlcmd -S localhost -U sa -P _yourpassword_ -Q "
    drop database if exists _yourdatabasename_
    create database _yourdatabasename_ collate Latin1_General_CI_AI
"

Run the script above executing bash recreate-sqlsrv-database.sh in your project root folder.