none
Best Practice for SQL 2016 in cluster on Windows 2016 VMs

    Question

  • Hi

    We are going to install 5 instances with 3 node (Windows 2016 VMs) SQL 2016 cluster and looking for best practice / recommended configuration especially on following topics:-

    a) Location to Keep System Databases (Master, Model and Msdb), can we keep all of them along with log files on a dedicated shared cluster disk or db & log on separate disks (if later is selected is it good to keep all these DBs in a disk and all log files on another disk)

    b) TempDB and log disk location

    **Here we believe all instances will use same system DBs and same Temp DB

    b) Size of the disk  for System DBs and Temp DBs

    c) MSDtc recommendation for each instance

    d) Autogrow, Maxsize, Autoshrink and memory recommended settings

    Thanks in advance


    LMS

    Wednesday, April 12, 2017 1:34 PM

All replies

  • a) the system databases must be on the same clustered luns as the user databses. The only exception is tempdb which should be on its own lun. Again all drives holding databases or their log files must be on clustered volumes.

    b) tempdb and its log files should be on their own luns. This is normally as they tempdb log file can grow huge. In some cases your backup software will require them to be on the same lun.

    b) I normally allocate 200 G for the system drive, 200 G system databases, 400 G for tempdb. Your results may vary. With clustered volumns it is typically difficult to expand them as they will be on RDM partitions. Expanding them can be difficult and will result in an outage.

    c) I do not cluster this but install if on both nodes - like reporting services.

    d) I do not use a percentage growth but a 500 Mg fixed amount. I don't do auto shrink.

    I follow Glenn Berry's suggestion for capping memory.

    Wednesday, April 12, 2017 1:48 PM