SQL Server On Linux – Post Installation – Configuration – Set Folders

Background

Upon installation, SQL Server on Linux utilizes the same storage folders for data, log, and backups.

Outline

  1. Review Current Settings
  2. Provision Folders
  3. Register Folder Settings on SQL Server
  4. Restart SQL Server
  5. Review new folder settings

Processing

Review Current Settings

SQL Server Management Studio ( SSMS )

Let us use our GUI Tool, SSMS, to determine current default folder settings.

Server Properties

Server Properties – Database Settings
Images

configuration.defaultLocations.ssms.20181127.1135AM

Explanation
  1. Data, Log, Backup
    • /var/opt/mssql/data/

Provision Folders

Outline

  1. Create An Array
  2. Fill Array with list of folders
  3. Iterate Array List
    • Get Folder
    • Determine if folder exists
    • If Folder does not exist, please create it
    • Set Folder Attributes
      • Set Folder Owner to mssql
      • Set Folder Group to mssql

Script


#!/bash/bash

echo "Preparing folders"

declare -a _folderList=(
                          "/app/Microsoft/sqlServer/data"
                          "/app/Microsoft/sqlServer/log"
                          "/app/Microsoft/sqlServer/backup"
                          "/app/Microsoft/sqlServer/dumpCrash"
)

#Get Number of elements
_iNumberofElements=${#_folderList[*]}

echo "Number of elements $_iNumberofElements "

_i=0

while [ $_i -lt $_iNumberofElements ]
do

  #Current Folder
  _folder=${_folderList[_i]}

  _folderExistence=false

   #Does Folder Exist
   if [ -d $_folder ]
   then

     _folderExistence=true

   fi

   #Speak Folder
   echo "$_i ) $_folder "

   #if Folder does not exist, create it
   if [ $_folderExistence = false ]
   then

       echo -e "\tCreating Folder $_folder"

       mkdir --parents $_folder

  fi

  #Change Folder owner
  echo -e "\tChange Folder Owner"
  chown mssql $_folder

  #Change Folder Group
  echo -e "\tChange Folder Group"
  chgrp mssql $_folder

  #Move to next array element
  _i=$((_i+1))

done

Configure SQL Server Default Folders

Outline

  1. Issue folderlocation.default[x]
    • Data
    • Log
    • Backup
    • Dump

Script


/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /app/Microsoft/sqlServer/data

/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /app/Microsoft/sqlServer/log

/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /app/Microsoft/sqlServer/backup

/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /app/Microsoft/sqlServer/dumpCrash

Restart SQL Server Service

Outline

Restart SQL Server Service to effect changes

Script


sudo systemctl restart mssql-server.service

Review new settings

SQL Server Management Studio ( SSMS )

Let use again use SSMS to review folder settings.

Server Properties

Server Properties – Database Settings
Images

configuration.defaultLocations.ssms.post.20181127.0245PM

Explanation
  1. We were able to change our targeted folders
    • Data, Log, Backup

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s