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):

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.

  • I don't know Windows servers that well but 6 disks seems to be an ideal RAID10 pattern for 3TB. Is there a measure of IO capacity for your current setup? – danblack Sep 18 '18 at 5:24
  • 1
    RAID 0 sounds nuts - do you really want to take the whole server down and be restoring from backups if 1 drive crashes? You've got 5-6 TB of storage and you need 2.2 (and a bit for OS/etc). Pool the whole lost into RAID 10 or RAID 5 and logically segment the drives into OS, TEMPDB, System, Data – Greg Sep 18 '18 at 5:36
  • Just a comment on the memory bit: 64 GB seems on the low side compared to disk layout and database side. I would investigate the possibility to go to at least 128 GB, or perhaps some 200 GB. – Tibor Karaszi Sep 18 '18 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.