2

I am moving a SQL Server database to a separate server from the application that uses it (currently app and SQL are on the same server).

The new instance has been configured to default data (.mdf) and log (.ldf) files to be stored on separate drives per what I understand is general best practice.

I went through the process of actually moving the non-master system database files (temp, model, etc.) to these locations, leaving the master for last because it uses a different process. See this link for the instructions I used: https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-ver16

For the master database, the instructions say that after moving it, you should update the SQLDataRoot value in the registry to point to the location of the "master database files", but since the data and log files are now in separate folders, I wasn't sure what location to specify. From there, I wondered more broadly if I should even be separating them, or if they should always remain together, and only new databases have their data and log files moved to separate drives.

I haven't tried moving the master database yet, due to the question I have on configuring the registry value, and whether any of the system database files should be separated or kept together.

Improve this question
4
  • This really belongs on dba.se, since it has nothing to do with programming per se...
    – marc_s
    Commented Dec 19, 2023 at 19:30
  • 1
    Thank you marc_s, I'm relatively new to posting on SE, so didn't realize there was a separate section for it. Looks like it was migrated already, so thanks for that.
    – onejeremias
    Commented Dec 19, 2023 at 20:06
  • As Brent Ozar likes to say ... "What problem are you trying to solve?" In other words, what about the "best practice" of using separate drives is advantageous in your situation?
    – Doug Deden
    Commented Dec 19, 2023 at 20:07
  • Mostly performance reasons. It is the recommended approach by the company that designs the application, and suggested by MS as well per the following: learn.microsoft.com/en-us/sql/relational-databases/… The application is not in particularly heavy use at the moment, but we expect to be adding additional functionality over the next year that will exponentially increase db transactions, so setting ourselves up for best performance seemed wise.
    – onejeremias
    Commented Dec 19, 2023 at 20:45

1 Answer 1

Reset to default
4

Mostly performance reasons

FWIW, there's not really any performance gains to be had by separating the system databases' Data and Log files. They aren't really changing in data much or that transactional usually where it would help anything.

The only exception is putting tempdb (both the Data and Log files) on its own drive from everything else.

So if you kept the other system databases' files all together, that answers your question:

since the data and log files are now in separate folders, I wasn't sure what location to specify.

Also, if your server and its drives are provisioned via a VM (virtual machine) then there's less to be gained (regarding performance) by separating the files between drives, since they'll likely be logically provisioned as separate drives but may still be the same physical device behind the scenes.

Improve this answer
4
  • As with journalling file systems it can pay to have the logs on fast storage and a separate channel.
    – mckenzm
    Commented Dec 20, 2023 at 4:46
  • @mckenzm That's true when the logs change frequently, such as user databases or tempdb. But for the system databases for SQL Server, which generally have little data changes and not many logs, it doesn't matter. And again, in a VM scenario where the logical provisioned drives are all the same physical drive behind the scenes, it won't make much of a difference either.
    – J.D.
    Commented Dec 20, 2023 at 5:09
  • Hi, sorry for the delayed response, was on holiday break. Thank you for your answers. I'll move the system db files back to their original locations and leave it alone.
    – onejeremias
    Commented Jan 4 at 14:25
  • @onejeremias No worries at all. Cheers!
    – J.D.
    Commented Jan 4 at 17:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.