This will address a few basic MSSQL configuration changes when running MSSQL on a linux platform. <\/p>\n
The first one involves changing the default database and log locations:
\nTo set the default directories:<\/p>\n
sudo \/opt\/mssql\/bin\/mssql-conf set filelocation.defaultdatadir \/{SOMENEWDATADIRECTORY}
\nsudo \/opt\/mssql\/bin\/mssql-conf set filelocation.defaultlogdir \/{SOMENEWLOGDIRECTORY}
\nsudo \/opt\/mssql\/bin\/mssql-conf set filelocation.defaultbackupdir \/{SOMENEWBACKUPDIRECTORY}
\nsudo systmectl restart mssql-server<\/p><\/blockquote>\nFor example:<\/p>\n
sudo \/opt\/mssql\/bin\/mssql-conf set filelocation.defaultdatadir \/Data
\nsudo \/opt\/mssql\/bin\/mssql-conf set filelocation.defaultlogdir \/Logs
\nsudo \/opt\/mssql\/bin\/mssql-conf set filelocation.defaultbackupdir \/Backup
\nsudo systmectl restart mssql-server<\/p><\/blockquote>\nNext up is enabling the SQL Agent:
\nEnable the SQL Agent:<\/p>\n\/opt\/mssql\/bin\/mssql-conf set sqlagent.enabled true
\nsudo systemctl restart mssql-server<\/p><\/blockquote>\nLastly 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.<\/p>\n
First, you need to ensure that the new directory locations are owned by mssql, and group access is allowed for mssql group as well.<\/p>\n
sudo chown mssql.mssql \/Data
\nsudo chown mssql.mssql \/Logs
\nsudo chown mssql.mssql \/Backup <\/p><\/blockquote>\nDetermine where the tempdb data and logs files are currently:
\nAccess the SQL server and update the tempdb location:<\/p>\nsqlcmd -S localhost -U SA -P {PASSWORD}
\n> SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N’tempdb’);
\n> GO <\/p>\n> USE master;
\n> GO
\n> ALTER DATABASE tempdb
\n> MODIFY FILE (NAME = tempdev, FILENAME = ‘\/Data\/tempdb.mdf’);
\n> GO
\n> ALTER DATABASE tempdb
\n> MODIFY FILE (NAME = templog, FILENAME = ‘\/Logs\/templog.ldf’);
\n> GO <\/p><\/blockquote>\nRestart the MSSQL server:<\/p>\n
sudo systemctl restart mssql-server.service<\/p><\/blockquote>\n
You can make subdirectories for each database if you want as well. You just have to make certain that the ownership is correct.<\/p>\n
sudo mkdir \/Data\/msdb
\nsudo mkdir \/Logs\/msdb
\nsudo chown -R mssql.mssql \/Data\/
\nsudo chown -R mssql.mssql \/Logs\/<\/p><\/blockquote>\nAccess the SQL server:<\/p>\n
sqlcmd -S localhost -U SA -P {PASSWORD}<\/p><\/blockquote>\n
Determine where the msdb data and logs files are currently:<\/p>\n
> SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N’msdb’);
\n> GO<\/p><\/blockquote>\nContinuing from the SQL server:
\nMove msdb:<\/p>\n> USER master
\n> GO
\n> ALTER DATABASE msdb
\n> MODIFY FILE (NAME = MSDBData, FILENAME = ‘\/Data\/msdb\/MSDBData.mdf’);
\n> GO
\n> ALTER DATABASE msdb
\n> MODIFY FILE (NAME = MSDBLog, FILENAME = ‘\/Logs\/msdb\/MSDBLog.ldf’);
\n> GO<\/p><\/blockquote>\nMove the existing database files to their new locations:<\/p>\n
sudo sudo cd \/var\/opt\/mssql\/data\/
\nsudo systemctl stop mssql-server.service
\nsudo mv msdbdata.mdf \/Data\/msdb\/
\nsudo mv msdblog.ldf \/Logs\/msdb\/
\nsudo systemctl restart mssql-server.service
\nsudo systemctl status mssql-server.service<\/p><\/blockquote>\nAccess the SQL server:<\/p>\n
sqlcmd -S localhost -U SA -P {PASSWORD}<\/p><\/blockquote>\n
Determine where the model data and logs files are currently:<\/p>\n
> SELECT name, physical_name AS CurrentLocation FROM sys.master_files where database_id = DB_ID(N’model’)
\n> GO<\/p><\/blockquote>\nContinuing from the SQL server:
\nMove model:<\/p>\n> USE master
\n> ALTER DATABASE model
\n> MODIFY file (NAME = modeldev, FILENAME = ‘\/Data\/model\/model.mdf’)
\n> GO
\n> ALTER DATABASE model
\n> MODIFY file (NAME = modellog, FILENAME = ‘\/Logs\/model\/modellog.ldf’)
\n> GO<\/p><\/blockquote>\nMove the existing database files to their new locations:<\/p>\n
sudo sudo cd \/var\/opt\/mssql\/data\/
\nsudo systemctl stop mssql-server.service
\nsudo mkdir \/Data\/model
\nsudo mkdir \/Logs\/model
\nsudo chown mssql.mssql \/Data
\nsudo chown mssql.mssql \/Logs
\nsudo mv msdbdata.mdf \/Data\/model\/
\nsudo mv msdblog.ldf \/Logs\/model\/
\nsudo systemctl restart mssql-server.service
\nsudo systemctl status mssql-server.service<\/p><\/blockquote>\nAccess the SQL server:<\/p>\n
sqlcmd -S localhost -U SA -P {PASSWORD}<\/p><\/blockquote>\n
Determine where the master data and log files are currently:<\/p>\n
> SELECT name, physical_name AS CurrentLocation FROM sys.master_files where database_id = DB_ID(N’master’)
\n> GO<\/p><\/blockquote>\nThe master database is a little different than the others. You set the configuration after stopping the mssql-server process and moving the files.<\/p>\n
sudo cd \/var\/opt\/mssql\/data
\nsudo mkdir \/Data\/master
\nsudo mkdir \/Logs\/master
\nsudo chown -R mssql.mssql \/Data
\nsudo chown -R mssql.mssql \/Logs
\nsudo mv master.mdf \/Data\/master
\nsudo mv master.mdf \/Logs\/master
\nsudo \/opt\/mssql\/bin\/mssql-conf set filelocation.masterdatafile \/Data\/master\/master.mdf
\nsudo \/opt\/mssql\/bin\/mssql-conf set filelocation.masterlogfile \/Logs\/master\/mastlog.ldf
\nsudo systemctl stop mssql-server
\nsudo systemctl start mssql-server
\nsudo systemctl status mssql-server<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"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 [&hellip<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[48,218,132],"class_list":["post-1865","post","type-post","status-publish","format-standard","hentry","category-documentation","tag-linux","tag-mssql","tag-ubuntu"],"share_on_mastodon":{"url":"","error":""},"_links":{"self":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1865","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1865"}],"version-history":[{"count":5,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1865\/revisions"}],"predecessor-version":[{"id":1870,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1865\/revisions\/1870"}],"wp:attachment":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1865"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1865"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1865"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}