====== MS SQL ======

===== move database files to optimized storage =====

Recommended NTFS cluster size for MSSQL is 64kB. So ZFS's ''volblocksize'' also should be 64kB.

Move MSSQL guest data files to dedicated VM disk.

First create new VM disk:
<code bash>
zfs create nvmpool/data/vm-703-disk-3 -b 64k -V 16G
qm disk rescan
</code>

attach new storage to guest.

Format it under Windows guest to use 64kB cluster size.
Verify cluster size:
<code sh>
fsutil fsinfo ntfsinfo g:
</code>

==== move database files ====

Info based on 
  * [[https://www.sqlshack.com/move-sql-database-files-mdf-ldf-another-location/|How to move SQL database files (MDF and LDF) to another location]]
  * [[https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-database-files?view=sql-server-ver16|Move database files]]

<code sql>
ALTER DATABASE MY_DATABASE
    MODIFY FILE (NAME = MY_DATABASE, FILENAME = 'G:\MY_DATABASE.mdf');
GO
ALTER DATABASE MY_DATABASE
    MODIFY FILE (NAME = MY_DATABASE_log, FILENAME = 'G:\MY_DATABASE_log.ldf');  
GO
</code>

<code sql>
ALTER DATABASE MY_DATABASE SET OFFLINE;
GO
</code>

Stop MSSQL service.

Give new location rights for MSSQL service user (check what user is set in services).

Move database files to new location.

Start MSSQL service.