Tag: SqlSrv

MS SQL on Vagrant Ubuntu Homestead box for development on Laravel

Hi everyone!

Recently I had a chance to setup and successfully run development environment with Microsoft SQL Server on Laravel’s Homestead Vagrant box. I used Homestead installation per project. And here is how I’ve done it.

Prepare Homestead box

First step is to install Homestead for a project:

composer require laravel/homestead --dev
php vendor/bin/homestead make

Then you need to change Homestead.yaml file.

Firstly, memory limit have to be changed to 4096: memory: 4096

Then hostname: hostname: mysite.dev

And probably name for convenience: ‘name: mysite.dev’

In sites section make sure that you have configuration like this:

sites:
    - map: mysite.dev
      to: "/home/vagrant/project/public"

Then download and install Vagrant box

vagrant up

And add to hosts file domain for a this box 192.168.10.10 mysite.dev.

Install, setup and import database

To setup database login to Vagrant box

vagrant ssh

Install MS SQL server and tools:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

sudo apt-get update
sudo apt-get install -y mssql-server
sudo apt-get install -y mssql-tools unixodbc-dev

sudo ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd
sudo ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp

Then run initial MS SQL setup script

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

Use password “Secret123”, start server and enable starting on boot

Then it will require to install Sybase php extension

sudo apt-get install -y php7.1-sybase

When it will be updating some packages, select to keep the local version of config files on prompts.

To connect your Laravel application to MS SQL server you may need to add following configuration to your config/database.php file to connections section:

'sqlsrv' => [
    'driver' => 'sqlsrv',
    'host' => env('DB_HOST', 'localhost'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'sa'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
],

After this application should be able to successfully connect to MS Sql server.

To import database, copy database *.BAK files to project folder, open sqlcmd console

sqlcmd -U sa -P 'Secret123'

and run following queries to import MYDB.BAK database backup file

RESTORE DATABASE [mydb] FROM DISK='/home/vagrant/project/MYDB.BAK'
WITH  FILE = 1,  
MOVE N'MYDB_dat' TO N'/var/opt/mssql/data/mydb.mdf',
MOVE N'MYDB_log' TO N'/var/opt/mssql/data/mydb.ldf',
NOUNLOAD,  REPLACE,  STATS = 1
GO

That’s basically all to install and run MS SQL on your Homestead Vagrant box, if you have any questions, feel free to contact!