Problem
I have written several tips on SQL Server vNext running on Linux and in this tip I will explain how to change the default data and log file directory of SQL Server databases running on Linux servers.
Solution
The process to configure SQL Server running on Linux has changed from SQL Server running Windows where we use SQL Server Configuration Manager to manage changes in TCP ports, SQL Server services, etc. Changing the default database file location for SQL Server running on Linux is also one of those activities whose process is different as compared to Windows based SQL Servers. Microsoft has provided a configuration script called “mssql-conf” for Linux based SQL Server to make such changes. The “mssql-conf” script installs with SQL Server vNext for both Red Hat Enterprise Linux and Ubuntu. This utility is very useful to set the parameters like TCP ports, default data file location, default log file location, etc.
This tip is intended to focus on only changing the default database and log file location. Stay tuned for more tips focusing on different activities using this same configuration script.
Change the Default SQL Server Database File Location in Linux
Before going ahead, you need to have SQL Server vNext running on Red Hat Linux or an Ubuntu server.
Step 1: First, I would recommend running a full backup of all your user and system databases in case anything goes wrong with the existing database files and you need to restore all your databases. As SQL Server vNext is a CTP edition, I assume you will not have any business critical applications running, but recreating a test environment still takes time.
Step 2: Now connect to the Linux server using PuTTY or login to the server locally. I connected to my Linux server where SQL Server vNext is running through PuTTY as shown below.
Step 3: The current default location of SQL Server database files is /var/opt/mssql/data which is set during the SQL Server installation. We can check the database files under this location to validate the path. Run ls -lrt /var/opt/mssql/data to display all files in this location.
#use sudo to display files under mssql folder. Enter password once you get prompt. sudo su #Display files of location /var/opt/mssql/data ls -lrt /var/opt/mssql/data
We can see all database files are in this location /var/opt/mssql/data.
We can also verify the directory by creating a dummy database without mentioning the file location and then check the database file location of this newly created database. Connect to SQL Server using sqlcmd, create a database called MSSQLTIPS and then check the database file location of the database.
#Connect to SQL Server. Enter the sa password once you get password prompt. sqlcmd -S192.168.209.128 -Usa #Create database MSSQLTIPS CREATE DATABASE MSSQLTIPS GO #Change database context to MSSQLTIPS to execute queries on this database. USE MSSQLTIPS GO #Check database file location of database MSSQLTIPS. SELECT filename from sysfiles GO
Once we execute the above commands, we can get the location of the new MSSQLTIPS database as shown below. We can see that the location of the database files of this newly created database is /var/opt/mssql/data which is same location as shown in the above screenshot.
Step 4: Now we have to change this database file location to somewhere else let’s say from /var/opt/mssql/data to /opt/mssql/data folder, so that whenever a new database is created, the database files would go to this new location (opt/mssql/data) not the old location (/var/opt/mssql/data). As the ‘data’ folder is not there i.e. /opt/mssql/ first we will create this directory then we will assign the appropriate rights to the directory so that SQL Server can access this folder.
#create a directory named data under /opt/mssql/ mkdir /opt/mssql/data
Once you execute the above command, the ‘data’ directory will be created as shown in the image below. We also executed ls -lrt for both locations to compare the files. We can see there are no files in the /opt/mssql/data directory because it is newly created whereas we can see all database files placed under the /var/opt/mssql/data directory. If you notice you can see the database files of the newly created database MSSQLTIPS in the same location.
Now we will change the owner and group of this folder, so that SQL Server can access this directory. Run the below command to change the owner of this directory.
#Run below command to change the owner and group of this directory to user "mssql" sudo chown mssql /opt/mssql/data sudo chgrp mssql /opt/mssql/data
Step 5: Now we will use the configuration script “mssql-conf” to set the default database file location. This script installs in the bin directory with the SQL Server vNext installation. We can validate this before executing this command. Run ls -lrt /opt/mssql/bin to display files in the bin folder.
We can see configuration script “mssql-conf” is there in the bin folder from the above image, so now execute this script through the below command to change the default database file location to /opt/mssql/data.
#execute below command to change the default database file location. sudo /opt/mssql/bin/mssql-conf set defaultdatadir /opt/mssql/data
Once you execute the above command, it will set the default data file location as per your choice asking you to restart the SQL Server service to apply this change on SQL Server. Run the below command to restart the SQL Server service.
#Restart SQL Server Service systemctl restart mssql-server
Step 6: Now we will connect to SQL Server using sqlcmd to validate the above change which we made. Run the sqlcmd utility to make a database connection to the Linux server. Here we are getting an issue when establishing a database connection.
This is known issue and Microsoft has already suggested a fix to this issue in the online documentation. The fix is to move the tempdb data and log file to the new location /opt/mssql/data manually. I executed the below command to move both files to the target location.
#Move tempdb data file tampdb.mdf to /opt/mssql/data sudo mv /var/opt/mssql/data/tempdb.mdf /opt/mssql/data #Move tempdb log file tamplog.ldf to /opt/mssql/data sudo mv /var/opt/mssql/data/templog.mdf /opt/mssql/data
Once the tempdb data and log files have been copied to the new default database file location, connect to SQL Server using sqlcmd utility and this time I have successfully established a database connection. Here we can check the database file location of TempDB to verify the changes. We can see now TempDB is online from the /opt/mssql/data folder whereas earlier it was from the /var/opt/mssql/data which was shown in the initial screenshots.
Step 7: Now we will create a new database named “Test” without mentioning the database file location and we will validate where the files are located. We can see the newly created database “Test” has both database files in the /opt/mssql/data directory which is our target location.
You can also check the directory /opt/mssql/data to get the files placed there by running the ls -lrt command. Below is the screenshot of this command and we can see the database files. At this point we can confirm the default database file location has been set for your instance to /opt/mssql/data.
Change the Default SQL Server Transaction Log File Location in Linux
Step 1: If you want to place your data file and log file in different directories then we need to set the default log file location as well. Let’s assume we need to set our default data directory to /opt/mssql/data/ and the default log directory to /opt/mssql/log/. We have already changed the default data file location of SQL Server in the above section, so now we will set the default log file location in this section. Create another directory named log under /opt/mssql path as it does not exist.
#Create directory log under /opt/mssql mkdir /opt/mssql/log #Check directores under /opt/mssql ls -lrt /opt/mssql/
We can see both data and log directories are there in this location in the above screenshot. Next we will change the owner and group of this directory to the mssql user as we did in the above section.
Step 2: We will run the below command to change the owner and group of this directory to the “mssql” user as we did in the section above.
#Change owner sudo chown mssql /opt/mssql/log #Change group sudo chgrp mssql /opt/mssql/log
Step 3: Now we will execute the “mssql-conf” configuration script to set the default log file location of the SQL Server instance. Once you execute the below command it will set the new value for the default transaction log file location and ask you to restart the SQL Server service which we will do next.
#Change default log file location sudo /opt/mssql/bin/mssql-conf set defaultlogdir /opt/mssql/log
Restarting the SQL Server service as suggested in the above screenshot by running the below command.
#Restart SQL Server service systemctl restart mssql-server
Step 4: Now we have set the default log file location of the SQL Server instance, so whenever we create a new database the data file should go to the /opt/mssql/data directory as we set in this configuration in the first section and the log file should go to the /opt/mssql/log directory as we set this configuration in this section. Let’s validate this change by making a database connection to SQL Server, create a new database named “Test_Log” and then check the database file location of this database. Run the commands below.
#Connect to SQL Server. Enter the sa password once get password prompt. sqlcmd -S192.168.209.128 -Usa #Create New Database Test_Log CREATE DATABASE Test_Log GO #Change database context to Test_Log USE Test_Log GO #Check database file location of Test_Log Select filename from sysfiles
We can see the data and log file location of the newly created database “Test_Log” and the directories are as expected.
Next Steps
- The configurational script “mssql-conf” is very useful and you should explore more about this. I will write more tips on this utility in coming days.
- Learn more about SQL Server vNext version and stay tuned for more tips on this topic.
- Explore more knowledge on SQL Server Database Administration Tips.
