Tag: sql server services

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

 

 

SQL Operations Studio (Public Review) is now available

SQL Operations Studio is now available. Here’s the link to download and install it’s December (Latest release as of this writing) release:

Get SQL Operations Studio (preview) for Windows

This release of SQL Operations Studio (preview) includes a standard Windows installer experience, and a .zip:

Installer

  1. Download and run the SQL Operations Studio (preview) installer for Windows.
  2. Start the SQL Operations Studio (preview) app.

.zip file

  1. Download SQL Operations Studio (preview) .zip for Windows.
  2. Browse to the downloaded file and extract it.
  3. Run \sqlops-windows\sqlops.exe

Get SQL Operations Studio (preview) for macOS

  1. Download SQL Operations Studio (preview) for macOS.
  2. To expand the contents of the zip, double-click it.
  3. To make SQL Operations Studio (preview) available in the Launchpad, drag sqlops.app to the Applications folder.

Supported Operating Systems

SQL Operations Studio (preview) runs on Windows, macOS, and Linux, and is supported on the following platforms:

Windows

  • Windows 10 (64-bit)
  • Windows 8.1 (64-bit)
  • Windows 8 (64-bit)
  • Windows 7 (SP1) (64-bit) – Requires KB2533623
  • Windows Server 2016
  • Windows Server 2012 R2 (64-bit)
  • Windows Server 2012 (64-bit)
  • Windows Server 2008 R2 (64-bit)

macOS

  • macOS 10.13 High Sierra
  • macOS 10.12 Sierra

Linux

    • Red Hat Enterprise Linux 7.4
    • Red Hat Enterprise Linux 7.3
    • SUSE Linux Enterprise Server v12 SP2
    • Ubuntu 16.0

Allows us to connect well to the DB and it gives a nice feel of GUI integration.
I’ll pen down more in next writeup about this tool!

    • Screen Shot 2018-01-04 at 5.32.35 PM.png

 

Happy learning!

Cheers!
Aman Kharbanda

 

Having a corrupt Model DB after movement of system DB datafiles to separate drives.

Have you ever wondered how movement of system DB datafiles, could affect your SQL Server?

Recently, I came across a situation wherein I had moved the Model/MSDB datafiles from the contemporary location (C:\…\MSSQL\Data) to a separate drive using the following method-

1) ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = ‘new_path\os_file_name’ ) 2) Stopped SQL Server services. 3) Moved the file or files to the new location. 4) Restart services using Config. Manager and services came up fine. 5) Verified the file changes by running- SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files

Everything seems to be working fine as per expected and changes were reflected in sysaltfiles table after the above process was followed.

But the moment I removed the files from the actual location (C:\…\MSSQL\Data) & restarted the Windows server, SQL Server services didn’t come up automatically (Tried Manually as well) and in turn, there was an error in Event Viewer/Error log –

Event ID: 9003
The log scan number (19:492:1) passed to log scan in database ‘model’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

It clearly indicated the corruption of Model DB.

I couldn’t start SQL Server (Not in single user mode with trace 3608 and with -m option), so there was no point in restoring the Model DB with previous night backup.

To overcome this issue, I copied the model datafiles from the same server (Under C:\…MSSQL\Binn\Templates) to my desired location and tried starting SQL Server services. They came up smoothly without any issues :).

Now, you must restore model backup because you have taken files from the RTM build.

Things are working fine and as per expectations.

Moral of the story:: Remember, we have a template folder in default installation directory from where we can extract the datafiles, in case of such issues.

Hope this helps you too.

Cheers!
Aman Kharbanda