MSSQL: MSSQL on Linux datafiles with C prompt

Installing MSSQL on Linux is fairly straightforward.

Installation 

https://www.microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux

For MSSQL on Ubuntu simply follow this link.

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu

SQL Server installation does enforce the 3.25Gig memory size. So, prior to start the installation makes sure your environment or VM do have around 4gig of memory to work with. I allocated 60Gig on my VMs. After the Ubuntu Server 64 bits deployment and MSSQL installation, I still have 52gig left.

The main point of this blog is not to guide the user to deploy MSSQL on Linux but to show one of the interesting behavior I noticed.

administrator@ubuntu:/var/opt$ sqlcmd -U SA

Password:

1> select @@version;

2> go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Microsoft SQL Server vNext (CTP1.1) - 14.0.100.187 (X64)

Dec 10 2016 02:51:11

Copyright (C) 2016 Microsoft Corporation. All rights reserved.

on Linux (Ubuntu 16.10)

(1 rows affected)

1> select name from sys.databases;

2> go

name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

(4 rows affected)

1>

As you can see here, the datafile path starts with C:\ prompt.

SELECT cast(DB_NAME(database_id) as varchar(20)) AS "Database Name",

       cast(Name as varchar(25)) AS "Logical Name",

       cast(Physical_Name as varchar (100)) as "Datafiles Path",

       (size * 8) / 1024 as "Size in MB",

       (size * 8) / 1024/ 1024 as "Size in Gig"

FROM sys.master_files;

Database Name        Logical Name              Datafiles Path                                                                                       Size in MB  Size in Gig

-------------------- ------------------------- ---------------------------------------------------------------------------------------------------- ----------- -----------

master               master                    C:\var\opt\mssql\data\master.mdf                                                                               4           0

master               mastlog                   C:\var\opt\mssql\data\mastlog.ldf                                                                              2           0

tempdb               tempdev                   C:\var\opt\mssql\data\tempdb.mdf                                                                               8           0

tempdb               templog                   C:\var\opt\mssql\data\templog.ldf                                                                              8           0

model                modeldev                  C:\var\opt\mssql\data\model.mdf                                                                                8           0

model                modellog                  C:\var\opt\mssql\data\modellog.ldf                                                                             8           0

msdb                 MSDBData                  C:\var\opt\mssql\data\MSDBData.mdf                                                                            13           0

msdb                 MSDBLog                   C:\var\opt\mssql\data\MSDBLog.ldf                                                                              0           0

A user can access the physical datafile with sudo.

administrator@ubuntu:/var/opt$ sudo ls -las /var/opt/mssql/data/

total 53320

    4 drwxr-xr-x 2 mssql mssql     4096 Dec 19 10:54 .

    4 drwxrwx--- 7 mssql mssql     4096 Dec 19 10:55 ..

 4096 -rw-r----- 1 mssql mssql  4194304 Dec 19 11:06 master.mdf

 2048 -rw-r----- 1 mssql mssql  2097152 Dec 19 12:06 mastlog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 modellog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 model.mdf

13632 -rw-r----- 1 mssql mssql 13959168 Dec 19 10:55 msdbdata.mdf

  768 -rw-r----- 1 mssql mssql   786432 Dec 19 10:55 msdblog.ldf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 10:55 tempdb.mdf

 8192 -rw-r----- 1 mssql mssql  8388608 Dec 19 11:00 templog.ldf

Interesting that SQL Server still keeping the Windows behavior.