model_replicatedmaster.ldf move to another drive

Question

Hi all,
I'm curios about this 2 new DB (system).
I'm moving all ".ldf" file to a single drive... but they are related (seems to me) to HA.

I read this post
https://stackoverflow.com/questions/60971100/sql2019-with-sqlengine-installation-4-new-system-files-are-showing-but-no-new-sy

but I did not understand if it is possible to move them and how? Or is it a no sense?

model_msdblog.ldf
model_msdbdata.mdf
model_replicatedmaster.mdf
model_replicatedmaster.ldf

Thanks in advice, Alen.

Answer 1

Recently I had to move all my database data and log files etc. to a new HDD because of disk failure.

These databases are linked to "Big Data Cluster", "High Availability", etc. (as stated in the comments above). But note that these files are present even if you don't have this feature enabled. If these files are missing or moved to a new location there will be errors in the Event Log saying MSSQL can't find these databases. Like in my case:

• I had "High Availability" and "Big Data Cluster" disabled on my SQL Server 2022 Enterprise
• I moved these databases to a new HDD by copying the files
• When starting the MSSQL service it created error entries in the Event Log saying it can't find these database files in the old location (because I removed the faulty disk):
  • Event Id 17204; Source MSSQLSERVER; FCB::Open failed: Could not open file Y:\Old_Location\model_replicatedmaster.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
  • Event Id 17207; Source MSSQLSERVER; FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'Y:\Old_Location\model_replicatedmaster.ldf'. Diagnose and correct the operating system error, and retry the operation.
  • Event Id 17204; Source MSSQLSERVER; FCB::Open failed: Could not open file Y:\Old_Location\model_msdbdata.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
  • Event Id 17207; Source MSSQLSERVER; FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'Y:\Old_Location\model_msdblog.ldf'. Diagnose and correct the operating system error, and retry the operation.

To fix this when moving the database files to a new location you have to update their paths just like for user databases. Just run this script:

ALTER DATABASE model_msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'X:\New_Location\model_msdbdata.mdf');

ALTER DATABASE model_msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'X:\New_Location\model_msdblog.ldf');

ALTER DATABASE model_replicatedmaster MODIFY FILE ( NAME = replicatedmaster, FILENAME = 'X:\New_Location\model_replicatedmaster.mdf');

ALTER DATABASE model_replicatedmaster MODIFY FILE ( NAME = replicatedmasterlog, FILENAME = 'X:\New_Location\model_replicatedmaster.ldf');

Source: https://www.seangallardy.com/model_msdbdata-and-log-model_replicatedmaster-and-log-what-are-they/

Answer 2

Those files are only used by "Big Data Cluster" installations.

See:
https://learn.microsoft.com/en-us/sql/big-data-cluster/deployment-high-availability?view=sql-server-ver15

Answer 3

Hi @Alen Cappelletti ,

When you deploy SQL Server Big Data Cluster with high availability, all databases are automatically added to the availability group, including all user and system databases like master and msdb. This capability provides a single-system view across the availability group replicas. Additional model databases - model_replicatedmaster and model_msdb - are used to seed the replicated portion of the system databases. Refer to MS document Deploy SQL Server Big Data Cluster with high availability.

> but I did not understand if it is possible to move them and how? Or is it a no sense?

I did not find any MS document talking about this. Quote from this thread that you offered;

You simply cannot copy and paste the db files to a different location when sql has a handle on it.