SQL Server on Linux: Specifying File Path. Possible problems.

NOTE: Please be aware that all scripts work only as of publishing date and with SQL Server on Linux CTP 1.4. In future releases some features/bugs may disappear.

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:

SQL server placed our file in folders with lower case "userdata" and "userlogs"!

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

At that step we already have an error in the error log: "The system cannot find the file specified"

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/testfolder

And 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.