Table of Contents

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: 

zfs create nvmpool/data/vm-703-disk-3 -b 64k -V 16G
qm disk rescan

attach new storage to guest.

Format it under Windows guest to use 64kB cluster size. Verify cluster size: 

fsutil fsinfo ntfsinfo g:

move database files

Info based on 

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
ALTER DATABASE MY_DATABASE SET OFFLINE;
GO

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.