Thursday, March 30, 2017

SQL Server on Linux. Set default Data and Log folders.

Continue to get through SQL Server on Linux Configuration documentation:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf#datadir

It is pretty clear, but still I want to prove it works and that it works MY way.

First step: You need to create two new folders for your DATA and LOG files.
I'll do it under default SQL Server folder and I'll do it by two simple commands:
sudo -u mssql mkdir /var/opt/mssql/userdata
sudo -u mssql mkdir /var/opt/mssql/userlogs
IMPORTANT:
1. To use command "mkdir" (make directory) you have to use "sudo" account, otherwise system will not allow you to do that.
2. New folders must be under ownership of "mssql" account. In order to do so we are using parameter "-u mssql".

Second step: Setting default data and log directories to the newly created folders:
sudo /opt/mssql/bin/mssql-conf set defaultdatadir /var/opt/mssql/userdata
sudo /opt/mssql/bin/mssql-conf set defaultlogdir /var/opt/mssql/userlogs

Third step: Restart SQL Server service:
sudo systemctl restart mssql-server

If there was no mistyping there shouldn't be any errors.

Creating a New Database!

Lets check how it works. Run "sqlcmd" and create new test database:
sqlcmd -S localhost -U sa
Then enter "sa" password to the prompt and create database:
1> CREATE DATABASE LinuxTest2;
2> GO

Again, if everything was done correctly, there shouldn't be any error.
To prove that during database creation data and log files were placed into correct folders will run two following command:
sudo ls -l /var/opt/mssql/userdata
sudo ls -l /var/opt/mssql/userlogs

It should return newly created database filenames with their properties.

Here is how all of that worked in my virtual box:

No comments:

Post a Comment