I need to migrate a 2.2 TB database to a better organized single server with 12 physical cores. New server has 5 SATA SSDs 1 TB each, will upgrade to 6 SSDs 1 TB each.
My current design is (this is new server):
- Disk 1: Windows and SQL server binaries, transaction log for main database (this is temporary, going to move log to Disk 6).
- Disk 2: TempDB split into 8 data files and 1 log file that take all the space evenly per this article https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/
- Disk 3-4-5: RAID 0 with main db of 2.2 TB. Raid is created via Windows Server 2016's "Storage Pools" functionality.
- Disk 6: In the future will contain main db log.
There will be no redundancy, this is all I can have right now. Current recovery plan is daily backup to external storage, and full db restore if something fails.
What will be the best approach to place DB of this size over 3 available SSD disks?
- 1 filegroup, 1 file on a raid?
- 1 filegroup, several files on same raid (there is some info that this can improve IO when server has many physical cores). If I go in that direction, do I need to rebuild clustered indexes so that all my data would be evenly distributed across all files? is there any benifits?
- 1 filegroup, 3 (or more?) files on 3 individual disks? Again, rebuilt to evenly distribute all data?
- Multiple filegroups on separate disks?
Of course I expect "it depends" answer, but I need a place to start to finally migrate the DB, and then look into analyzing and tuning it furthier.
Software & hardware: Windows Server 2016, Sql Server 2017. Machine is Dell Precision Tower 7810 with 12 physical cores (2 processors). Currently 32 GB ram, hoping to upgrade to 64 soon.