Nisarg Upadhyay
New disk to create mount point added

Move SQL databases to a different mount point: Create a new mount point in Linux

March 26, 2021 by

This article is the 1st article of the Move SQL Database to different mount point series. This article focuses on creating a new partition using the fdisk command and formatting it with an ext4 file system using the mkfs command.

In my previous article, Move SQL database files in SQL Server 2019 on Linux, I have explained different ways to move the databases to another directory of Linux. In this article, I am going to cover the following topics:

  1. Add a new virtual disk
  2. Create a new partition on the disk
  3. Format the partition with ext4 file system
  4. Update the /etc/fstab file
  5. Mount the partition on a directory
  6. Create required directories on new mount point to save the database files

For the demonstration, I have created a virtual machine using Oracle VM virtual box. I have installed Ubuntu 18.04 and SQL Server 2019 on it. First, let’s create a new mount point.

Add a new virtual disk

I have attached a new virtual hard disk to my existing virtual machine. To add a disk, On the VirtualBox Manager screen, right-click on Virtual Machine and select Settings.

Add disk in existing VM

On the virtual machine settings screen, click on the Storage option. Click on Controller: SATA and click on the Hard-disk icon.

Add SATA virtual hard disk

A dialog box opens that option to create a new disk or choose an existing disk. We are creating a new drive, so click on Create new disk.

Create new disk

A wizard to create virtual hard disk starts. We are creating a VirtualBox Disk Image, so choose VDI (VirtualBox Disk Image) and click on Next.

Select VDI disk

We can create two types of virtual disks

  1. Fixed Size
  2. Dynamically allocated

We will create a Dynamically allocated disk, so select Dynamically allocated and click on Next.

Dynamically allocated disk

On the File location and size screen, I have specified the location to save the new *.VDI file. The name of the *.VDI file is SQLDatabase.vdi. The maximum size of the disk is set to 20 GB. Click Create.

Specify the virtual disk location

The new disk is added to the SATA controller. The new disk is visible in the storage screen of settings of the virtual machine, which indicates that the disk has been created successfully.

New disk has been added

You can see the new disk in VirtualBox Manager.

New disk to create mount point added

Now, let us create a partition. Start the virtual machine.

Create a new partition

I am not explaining the process of adding a virtual drive. After adding a new hard disk, we must create a partition. First, let us view the existing block devices using the lsblk command.

The lsblk command shows the information about specific or all the block devices except RAM disks. It uses the sysfs file to collect the information of block devices. Execute the below command to view the Name of the block device, size, and mount point.

root@LinuxSQL02:~# lsblk -o NAME,SIZE,MOUNTPOINT

New disk is added

As you can see in the above screenshot, the command’s output returns a lot of information. In the list of block devices, you can see the new disk /dev/sdb. To use the disk, we must create a disk partition, then format it, and mount it on a specific directory. To create a partition, I am using the fdisk command.

To create a new partition, we must specify the disk name in the fdisk command. To invoke it, run the below command.

root@LinuxSQL02:~# fdisk /dev/sdb

fdisk command to create a new mount point

To create a new partition, enter the n option.

New partition

We want to create another primary partition, so enter P.

Create a primary partition

The new partition number is 2, so enter 2.

Assigning partition type

We do not want to change the first sector, so specify 2048 (default) or press the Enter key to continue to the next step.

specify first sector

We do not want to change the value of the last sector as well, so specify 41943039 (default) or press the Enter key to continue to the next step.

specify last sector

The new partition has been created successfully.

New Partition has been added

Once the partition has been created, we must format it. Press the Ctrl+C key to exit from the fdisk utility. To view the new partition, run the following command

root@LinuxSQL02:~# fdisk -l /dev/sdb

View partition

As you can see, the new partition has been created. Now let us format it as an ext4 file system. To format the partition, we will use the mkfs command.

The full name of the mkfs is Make File System. It is used to make a file system on a storage device or a partition on a hard disk. Using the mkfs command, we can create Linux compatible partitions (ext2, ext3, and ext4) and Windows compatible partition. The mkfs command creates a FAT partition that can be utilized by the windows operating system.

  • Tip: The Windows operating system cannot access the Linux filesystem using native tools. If you want to use windows compatible filesystem in a Linux drive, you must format it as FAT32 filesystem. If you are using a dual boot system and want to keep one partition in common between Windows and Linux, you can create a partition and format it with FAT32 filesystem

Run the below command to format the /dev/sdb partition in the ext4 filesystem.

root@LinuxSQL02:~# mkfs -t ext4 /dev/sdb

The command has been executed successfully.

Create ext4 file system

Now, to view the new partition, use the lsblk -l command.

root@LinuxSQL02:~# fdisk -l /dev/sdb

view disk and partition details

As you can see, the new partition has been created. Now, we will assign a label to the partition. To do that, we are using the e2label command. The label of the partition is SQLDatabases.

root@LinuxSQL02:~# e2label /dev/sdb /SQLDatabases

Specify the label

The updated label can be viewed by executing the lsblk command.

root@LinuxSQL02:~# sudo lsblk -o name,mountpoint,label,size

New disk is mounted on /SQLDatabase mount point

When we boot the machine, the operating system reads the volume and partition information from the fstab file, so we must add the details of the new partition in fstab. Open the file using VI editor.

root@LinuxSQL02:~# Vi /etc/fstab

After the file opens, to edit the file, press “I”, add the following lines.

LABEL=/SQLDatabases /SQLDatabases ext4 defaults 1 2

Screenshot of fstab file

Specify the details of disk in fstab file

Press Esc and then:wq to Save and close the files. Now let us mount the disk on /SQLDatabases directory.

Mount the disk

First, to mount the disk on the/SQLDatabases directory, create a directory named SQLDatabases in the root directory.

root@LinuxSQL02:~# mkdir /SQLDatabases

If required, change the ownership of the directory.

root@LinuxSQL02:~# chown nisarg /SQLDatabases

Now, to run the MOUNT command to mount the disk on /SQLDatabases directory.

root@LinuxSQL02:/# mount /dev/sdb /SQLDatabases

Run lsblk -l command to verify that the disk has been mounted or not.

root@LinuxSQL02:/# lsblk -o name,label,size,mountpoint /dev/sdb

Partition has been created and mounted

As you can see, the disk is successfully mounted, and it is ready to use.

Create required directories in the /SQLDatabases mount point

Now, we will create directories on the new partition to save the database files. The details are the following:

File Type

Database file location

Primary data file location

/SQLDatabase/Data

Secondary data file location

/SQLDatabase/Log

TempDB data files location

/SQLDatabase/TempDB

Backup location

/SQLDatabase/Backup

I am using the mkdir command to create the directories on /SQLDatabases mount point.

Command to create the /SQLDatabases/Data directory:

root@LinuxSQL02:/# mkdir /SQLDatabases/Data

Command to create the /SQLDatabases/Log directory

root@LinuxSQL02:/# mkdir /SQLDatabases/Log

Command to create the /SQLDatabases/TempDB directory

root@LinuxSQL02:/# mkdir /SQLDatabases/TempDB

Command to create the /SQLDatabases/Backup directory

root@LinuxSQL02:/# mkdir /SQLDatabases/Backup

Once the directories have been created, we will grant the users the appropriate permissions to access the directories.

Summary

In this article, we have learned how we can add a new virtual disk to an existing virtual machine. I have also covered how we can create a new ext4 partition in Linux using fstab and mkfs utility. In my next article, I will explain how we can grant the directory level permission, move the user database and system database files to the new mount point. Additionally, I will explain how we can change the default location of the data file, log file, TempDB files, and backup files by using the configuration file named mssql-config file.

Table of contents

Move SQL database files in SQL Server 2019 on Linux
Move SQL databases to a different mount point: Create a new mount point in Linux
Move SQL databases to a different mount point: Granting permissions and moving System and User databases

Nisarg Upadhyay
Linux, Maintenance, SQL Server on Linux

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

412 Views