Monday, December 19, 2016

MSSQL: MSSQL on Linux datafiles with C prompt


Installing MSSQL on Linux is fairly straightforward.

Installation 

For MSSQL on Ubuntu simply follow this link.

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.

MSSQL: Getting more detail information for failed jobs.

Often when a job failed, it provides trivial information for diagnostic. In order to expand the detail of the failed job, it can be done th...