SQL 2017 on Linux :: Configuration Changes – Default Data Directory

Default location of data files including the log files on SQL Server with Linux goes to /var/opt/mssql/data directory. If you need to change or update your default location to some other directory or a folder, here’s how you can do it:

[aman@linuxserver data]$ pwd
/var/opt/mssql/data
[aman@linuxserver data]$ ls -ltr
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 modellog.ldf
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 model.mdf
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 tempdb.mdf
-rw-rw—- 1 mssql mssql 8388608 Jan 22 20:03 linuxdb.mdf
-rw——- 1 mssql mssql 786432 Jan 22 20:03 msdblog.ldf
-rw——- 1 mssql mssql 15400960 Jan 22 20:03 msdbdata.mdf
-rw-rw—- 1 mssql mssql 8388608 Jan 22 20:09 linuxdb_log.ldf
-rw——- 1 mssql mssql 8388608 Jan 22 20:26 templog.ldf
-rw——- 1 mssql mssql 4194304 Jan 23 00:19 master.mdf
-rw——- 1 mssql mssql 1048576 Jan 23 00:19 mastlog.ldf

New location where all your newer DB’s should be created going forward –
“/var/opt/mssql/data/newdata”

Create a new directory:
[aman@linuxserver data]$ mkdir newdata

Changing the owner and groupto mssql:
[aman@linuxserver newdata]$ sudo chown mssql /var/opt/mssql/data/newdata
[sudo] password for aman:
[aman@linuxserver newdata]$ sudo chgrp mssql /var/opt/mssql/data/newdata

[aman@linuxserver newdata]$ pwd
/var/opt/mssql/data/newdata

Currently no files are available here:
[aman@linuxserver newdata]$ ll
total 0

This is how change the default directory for data/log files for SQL Server and following this command’s execution, you got to restart SQL Service:
[aman@linuxserver newdata]$ sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/opt/mssql/data/newdata
SQL Server needs to be restarted in order to apply this setting. Please run
‘systemctl restart mssql-server.service’.

[aman@linuxserver newdata]$systemctl status mssql-server
● mssql-server.service – Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2018-01-22 20:03:00 PST; 4h 34min ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 2827 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─2827 /opt/mssql/bin/sqlservr
└─2829 /opt/mssql/bin/sqlservr
[aman@linuxserver newdata]$ sudo systemctl restart mssql-server
[aman@linuxserver newdata]$ systemctl status mssql-server
● mssql-server.service – Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2018-01-23 00:38:14 PST; 6s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 12963 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─12963 /opt/mssql/bin/sqlservr
└─12969 /opt/mssql/bin/sqlservr

Let’s now try creating a DB and see where does its data files are getting stored:

[aman@linuxserver newdata]$ sqlcmd -S linuxserver -U sa -p
Password:
1> create database newdb
2> go

1> select * from sysaltfiles
2> go
fileid groupid size maxsize growth status perf dbid name filename
—— ——- ———– ———– ———– ———– ———– ——
1 1 1024 -1 8192 2 0 6 newdb /var/opt/mssql/data/newdata/newdb.mdf
2 0 1024 268435456 8192 66 0 6 newdb_log /var/opt/mssql/data/newdata/newdb_log.ldf

Hope this helps..

Happy learning!

Cheers!
Aman Kharbanda

 

 

Advertisements

Author: sqlserverposts

Database Consultant by profession. I am a geek and a avid sports lover too. "Progress informed by the past, inspired by future".

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s