Move System Databases in the SQL Server Failover Cluster

Total: 2 Average: 3

Recently, I was assigned to a project – our customer purchased a new SAN, and they wanted us to replace the old one with a new SAN. User and system databases utilized SAN, meanwhile, the operating system and SQL were installed on the server’s local disks. Lucky us..!! The idea was to move the existing user and system databases to a new SAN and keep the downtime as minimum as possible. So, to minimize the downtime, instead of reinstalling OS and SQL, we decided to use the following approach.

  1. Attach a new SAN to an existing server, create new drives on it, and add those drives to an existing failover cluster instance.
  2. Stop SQL Server, copy master, model, and msdb databases on new drives and start the SQL services.
  3. Move User databases to new drives and remove the old SAN.

In my previous article, I explained the step-by-step process of adding new storage to an existing failover cluster. Moreover, I demonstrated the process of moving user databases to new drives.

In this article, I am going to demonstrate the process of moving the system databases to new drives that I utilized in my project. In my previous article, we added the drive to an existing failover cluster and in this article we are going to use the same drive. The volume label of the drive is “E:\”, so I created a directory named “SystemDatabases” in E:\ drive. See the following image:

Currently, system databases are on “F:\MSSQL14.MSSQLSERVER\MSSQL\DATA” directory. We want to move these databases to the “E:\SystemDatabases” directory. Unlike user databases, moving system databases is not a simple process. We must follow the correct sequence and steps otherwise SQL instance won’t start.

 First, let’s review the current location of system databases. Execute the following query to get the current location of the system database.

The following is the output:

Now I will explain the process of moving databases to another directory in the following manner.

  1. Explain the steps of moving msdb and model databases.
  2. Explain the steps of moving the tempdb database.
  3. Explain the steps of moving the master database.

This demo will show you how to move all system databases to E:\ drive, hence to reduce downtime, so I will restart the SQL services at the end of the process.

Steps to move the msdb and model databases

To move the msdb and model databases, first, we need to update the file locations in the system catalog by executing the ALTER Database query. To do that, execute the following query in PowerShell Or SSMS.

The output is as follows:

Now execute the following query to verify the paths are updated in the system catalog. To do that, execute the following query:

Following is the output:

Steps to move the tempDB database

When we restart the SQL Server – it will automatically recreate the tempdb and log files hence we don’t need to move the tempdb files manually – all we have to do is alter the path of the database files. To alter the path of the tempdb database files, execute the following query in PowerShell / SSMS.

The output is as follows:

Now execute the following query to verify the paths are updated in the system catalog:

 Following is the output:

Steps to move the master database

The process of moving the master database to another drive is different from moving other system databases. To move the system database, we will perform the following steps:

  1. Open SQL Server configuration manager 2017 and expand click on SQL Server Services. See the following image:
  2. Right-click on SQL Server (MSSQLSERVER) and click on properties. In the properties dialog box, select the “Startup Parameters” tab. See the following image:
  3. As you can see in the image above, in existing parameters the text box contains the default location of the data file, Log file, and error log. Here -d indicates the default path of the datafile of the master database similarly, -l indicates the default path of the log file of the master database. We must replace these parameters with a new location. So click on the datafile parameter and replace it with “–  dE:\Systemdatabases\master.mdf” value and click on Update. Similarly, click on the log file parameter and replace it with “-lE:\Systemdatabases\mastlog.ldf” value and click on update. See the following image:

        4. Click OK to close the dialog box.

Note:

As we are performing this task on SQL Server failover instance, we must perform all the above changes on secondary nodes (SQL02.Dc.Local)

Restart the SQL Server services.

Once the new path of msdb, model, and tempdb are updated in the SQL Server catalog, we must stop the services to copy the database files on new drives. To do so, open SQL Server configuration manager 2017 >> Click on Services >> Right-click on SQL Server (MSSQLSERVER) and click on “Stop.” See the following image:

Now copy all the system database files on the new drive. See the following image: 

Now start the service from the SQL Server configuration manager. If the services started successfully then the path of the system and the databases have been updated successfully. Execute the following query to review the path of the master database:

Following is the output:

Summary

In this article, I have explained the step by step process of moving system databases to a new clustered disk.

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 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 Bachelors in Information Technology from Ganpat University.
Nisarg Upadhyay