SQL 2017 on Linux :: Configuration Changes – Default Data Directory

Default location of data files including the log files on SQL Server with Linux goes to /var/opt/mssql/data directory. If you need to change or update your default location to some other directory or a folder, here’s how you can do it:

[aman@linuxserver data]$ pwd
/var/opt/mssql/data
[aman@linuxserver data]$ ls -ltr
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 modellog.ldf
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 model.mdf
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 tempdb.mdf
-rw-rw—- 1 mssql mssql 8388608 Jan 22 20:03 linuxdb.mdf
-rw——- 1 mssql mssql 786432 Jan 22 20:03 msdblog.ldf
-rw——- 1 mssql mssql 15400960 Jan 22 20:03 msdbdata.mdf
-rw-rw—- 1 mssql mssql 8388608 Jan 22 20:09 linuxdb_log.ldf
-rw——- 1 mssql mssql 8388608 Jan 22 20:26 templog.ldf
-rw——- 1 mssql mssql 4194304 Jan 23 00:19 master.mdf
-rw——- 1 mssql mssql 1048576 Jan 23 00:19 mastlog.ldf

New location where all your newer DB’s should be created going forward –
“/var/opt/mssql/data/newdata”

Create a new directory:
[aman@linuxserver data]$ mkdir newdata

Changing the owner and groupto mssql:
[aman@linuxserver newdata]$ sudo chown mssql /var/opt/mssql/data/newdata
[sudo] password for aman:
[aman@linuxserver newdata]$ sudo chgrp mssql /var/opt/mssql/data/newdata

[aman@linuxserver newdata]$ pwd
/var/opt/mssql/data/newdata

Currently no files are available here:
[aman@linuxserver newdata]$ ll
total 0

This is how change the default directory for data/log files for SQL Server and following this command’s execution, you got to restart SQL Service:
[aman@linuxserver newdata]$ sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/opt/mssql/data/newdata
SQL Server needs to be restarted in order to apply this setting. Please run
‘systemctl restart mssql-server.service’.

[aman@linuxserver newdata]$systemctl status mssql-server
● mssql-server.service – Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2018-01-22 20:03:00 PST; 4h 34min ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 2827 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─2827 /opt/mssql/bin/sqlservr
└─2829 /opt/mssql/bin/sqlservr
[aman@linuxserver newdata]$ sudo systemctl restart mssql-server
[aman@linuxserver newdata]$ systemctl status mssql-server
● mssql-server.service – Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2018-01-23 00:38:14 PST; 6s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 12963 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─12963 /opt/mssql/bin/sqlservr
└─12969 /opt/mssql/bin/sqlservr

Let’s now try creating a DB and see where does its data files are getting stored:

[aman@linuxserver newdata]$ sqlcmd -S linuxserver -U sa -p
Password:
1> create database newdb
2> go

1> select * from sysaltfiles
2> go
fileid groupid size maxsize growth status perf dbid name filename
—— ——- ———– ———– ———– ———– ———– ——
1 1 1024 -1 8192 2 0 6 newdb /var/opt/mssql/data/newdata/newdb.mdf
2 0 1024 268435456 8192 66 0 6 newdb_log /var/opt/mssql/data/newdata/newdb_log.ldf

Hope this helps..

Happy learning!

Cheers!
Aman Kharbanda

 

 

Advertisements

Fixing SQL Server Log Shipping after new DB file has been added to Primary

If you are maintaining a standby copy of your PRD DB enabled with log shipping mechanism, and it breaks after a data file was added to your Primary database, refer to the below to overcome this kind of situation.

You might see restore errors like these on your secondary DB server-

Error: Could not apply log backup file ‘\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’ to secondary database ‘DBName’.(Microsoft.SqlServer.Management.LogShipping) ***  2015-11-12 13:30:11.98 *** Error: Directory lookup for the file “E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf” failed with the operating system error 3(The system cannot find the path specified.).  File ‘DBName_Data3’ cannot be restored to ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’. Use WITH MOVE to identify a valid location for the file.  Problems were identified while planning for the RESTORE statement. Previous messages provide details.  RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Check which transaction log has been last applied on Secondary DB (Via T-SQL or Log Shipping Status Report).
You may use this t-sql to get the status:

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
Now to validate which secondary file was lately added to your DB, use the below T-SQL:
RESTORE FILELISTONLY FROM DISK=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’

And the final step would be to restore the transaction log with norecovery and move option:
RESTORE log DBName FROM Disk=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’
with norecovery,
MOVE ‘DBName_Data3’
TO ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’

Once DB is restored, you should be able to restart your log shipping restore automated jobs.

Happy learning!

Cheers!
Aman Kharbanda