This will address a few basic MSSQL configuration changes when running MSSQL on a linux platform.
The first one involves changing the default database and log locations:
To set the default directories:
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /{SOMENEWDATADIRECTORY}
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /{SOMENEWLOGDIRECTORY}
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /{SOMENEWBACKUPDIRECTORY}
sudo systmectl restart mssql-server
For example:
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /Data
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /Logs
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /Backup
sudo systmectl restart mssql-server
Next up is enabling the SQL Agent:
Enable the SQL Agent:
/opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server
Lastly is moving the default databases to a new location. For instance to the default directories you configured above. This is a bit more involved, so you have to pay attention to the details. Unless otherwise specified these are run from the linux command line.
First, you need to ensure that the new directory locations are owned by mssql, and group access is allowed for mssql group as well.
sudo chown mssql.mssql /Data
sudo chown mssql.mssql /Logs
sudo chown mssql.mssql /Backup
Determine where the tempdb data and logs files are currently:
Access the SQL server and update the tempdb location:
sqlcmd -S localhost -U SA -P {PASSWORD}
> SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N’tempdb’);
> GO
> USE master;
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = ‘/Data/tempdb.mdf’);
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = ‘/Logs/templog.ldf’);
> GO
Restart the MSSQL server:
sudo systemctl restart mssql-server.service
You can make subdirectories for each database if you want as well. You just have to make certain that the ownership is correct.
sudo mkdir /Data/msdb
sudo mkdir /Logs/msdb
sudo chown -R mssql.mssql /Data/
sudo chown -R mssql.mssql /Logs/
Access the SQL server:
sqlcmd -S localhost -U SA -P {PASSWORD}
Determine where the msdb data and logs files are currently:
> SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N’msdb’);
> GO
Continuing from the SQL server:
Move msdb:
> USER master
> GO
> ALTER DATABASE msdb
> MODIFY FILE (NAME = MSDBData, FILENAME = ‘/Data/msdb/MSDBData.mdf’);
> GO
> ALTER DATABASE msdb
> MODIFY FILE (NAME = MSDBLog, FILENAME = ‘/Logs/msdb/MSDBLog.ldf’);
> GO
Move the existing database files to their new locations:
sudo sudo cd /var/opt/mssql/data/
sudo systemctl stop mssql-server.service
sudo mv msdbdata.mdf /Data/msdb/
sudo mv msdblog.ldf /Logs/msdb/
sudo systemctl restart mssql-server.service
sudo systemctl status mssql-server.service
Access the SQL server:
sqlcmd -S localhost -U SA -P {PASSWORD}
Determine where the model data and logs files are currently:
> SELECT name, physical_name AS CurrentLocation FROM sys.master_files where database_id = DB_ID(N’model’)
> GO
Continuing from the SQL server:
Move model:
> USE master
> ALTER DATABASE model
> MODIFY file (NAME = modeldev, FILENAME = ‘/Data/model/model.mdf’)
> GO
> ALTER DATABASE model
> MODIFY file (NAME = modellog, FILENAME = ‘/Logs/model/modellog.ldf’)
> GO
Move the existing database files to their new locations:
sudo sudo cd /var/opt/mssql/data/
sudo systemctl stop mssql-server.service
sudo mkdir /Data/model
sudo mkdir /Logs/model
sudo chown mssql.mssql /Data
sudo chown mssql.mssql /Logs
sudo mv msdbdata.mdf /Data/model/
sudo mv msdblog.ldf /Logs/model/
sudo systemctl restart mssql-server.service
sudo systemctl status mssql-server.service
Access the SQL server:
sqlcmd -S localhost -U SA -P {PASSWORD}
Determine where the master data and log files are currently:
> SELECT name, physical_name AS CurrentLocation FROM sys.master_files where database_id = DB_ID(N’master’)
> GO
The master database is a little different than the others. You set the configuration after stopping the mssql-server process and moving the files.
sudo cd /var/opt/mssql/data
sudo mkdir /Data/master
sudo mkdir /Logs/master
sudo chown -R mssql.mssql /Data
sudo chown -R mssql.mssql /Logs
sudo mv master.mdf /Data/master
sudo mv master.mdf /Logs/master
sudo /opt/mssql/bin/mssql-conf set filelocation.masterdatafile /Data/master/master.mdf
sudo /opt/mssql/bin/mssql-conf set filelocation.masterlogfile /Logs/master/mastlog.ldf
sudo systemctl stop mssql-server
sudo systemctl start mssql-server
sudo systemctl status mssql-server