As you might know Linux file system is different from Windows.
Linux does not have drive letters like "A:\","B:\","C:\","D:\","E:\" etc.
It has only root folder: "/" and all devices, services, mapped network resources are linked as underlying sub-folders.
SQL Server files are located under Linux following folder: "/var/opt/mssql/".
Knowing that, you can reference SQL server files in that "Linux way".
However, Microsoft did extremely good job to satisfy current customers, it also translated "Linux path" in "Windows way" just by adding drive "C:\" letter instead of root folder. Now "C:\" is our root!
So, in that manner, SQL Server files will be located in "c:\var\opt\mssql\" directory!
Isn't it simple?!!!
There will be no effort at all to refurbish old SQL SQL Server code for new Linux platforms!
Here I'll give you an example of Database creation using different addressing methods:
1. I use Windows notation for data file and Linux notation for log file.
2. Because Linux systems are case sensitive it is very important to know it when you work with Linux. However, Microsoft allows you to be reluctant. You can use upper or lower case in the order you want.
Use Master GO CREATE DATABASE [LinuxFilePathDemo] ON ( NAME = 'LinuxFilePathDemo_dat', SIZE = 1, MAXSIZE = 1, FILENAME = 'c:\var\opt\mssql\USERDATA\LinuxFilePathDemo.mdf') LOG ON (NAME = 'LinuxFilePathDemo_log', SIZE = 1, MAXSIZE = 1, FILENAME = '/var/opt/mssql/USERLOGS/LinuxFilePathDemo.ldf'); GO SELECT name, type_desc, physical_name FROM sys.master_files WHERE database_id = DB_ID('LinuxFilePathDemo'); GO
As you can see SQL Server accepted your request!
At that point Linux Geeks will start laughing at you:
Do you know now WHERE you files really are?
Lets check our folders:
What a surprise! Both folders are completely empty!!!!
Lets check somewhere else:
Do you think SQL Server just converted upper case to lower case, but still reported to us that folder names "USERDATA" and "USERLOGS" in Upper case?
It is kind of true, but still wrong. SQL Server does something completely different.
It looks like if file system has two folders with similar names, which differentiate only by case than SQL Server chooses case insensitively the one, but there is no correlation which one will be chosen.
Error Case with "case" different folders.
In this case I'll show huge potential problem you could have:Step 1. Create new folder in lower case "/var/opt/mssql/testfolder":
sudo -u mssql mkdir /var/opt/mssql/testfolder
Step 2. Create New database "Test":
CREATE DATABASE [Test] ON ( NAME = 'Test', FILENAME = '/var/opt/mssql/testfolder/Test.mdf') LOG ON (NAME = 'Testlog', SIZE = 1, MAXSIZE = 1, FILENAME = '/var/opt/mssql/testfolder/Test.ldf'); GO
Step 3. Verify that new "Test" database is Online:
SELECT LEFT(name,22) Name, LEFT(state_desc,20) State FROM sys.databases WHERE name = 'Test'; GO
Step 4. Verify that database files are in the place where they supposed to be:
sudo ls -l /var/opt/mssql/testfolder
Step 5. Create new folder in upper case "/var/opt/mssql/TESTFOLDER"
sudo -u mssql mkdir /var/opt/mssql/TESTFOLDER
Step 6. Restart SQL Server service
sudo systemctl restart mssql-server
Step 7. Verify that new "Test" database is Online:
SELECT LEFT(name,22) Name, LEFT(state_desc,20) State FROM sys.databases WHERE name = 'Test'; GO
Step 8. Delete "Test" database:
DROP DATABASE [Test]; GO
Step 9. Check for missing database files where they are supposed to be:
sudo ls -l /var/opt/mssql/testfolderAnd database files are still in the folder.
See screen shot of all 9 steps:
I'm assuming that after SQL Server restart it was looking for "Test" database files in new "/var/opt/mssql/TESTFOLDER" and not in "/var/opt/mssql/testfolder", where we actually created them.
Conclusion:
While working with SQL Server on Linux avoid "duplicated" folder and file names differentiated only by case.
