Dec 022013
 

Having a standardized build checklist for a new SQL Server instance can help ensure that you do not forget any important steps or configuration settings as you install SQL Server, get it updated, and get it properly configured. This is very important in order to make sure you get the best performance and scalability out of your SQL Server instance.

My Pluralsight course, SQL Server 2012 Installation and Configuration, goes into even more detail about this process. The general framework is below:

  1. Rack and cable the server
    1. Make sure each power supply is plugged into a different power circuit
    2. Make sure network cables are plugged into different network switches if possible
  2. Request a regular domain account for the SQL Server Service and for the SQL Server Agent Service (along with any other SQL Server services you will be using, such as SSIS, SSRS, SSAS)
    1. You will need to know the user name and password for these accounts during the SQL Server 2012 installation
    2. Try to have these accounts use passwords that do not expire
  3. Check the main BIOS settings on the server
    1. Hyper-threading and turbo-boost should be enabled
    2. Power management should be set to OS control
    3. Memory testing should be disabled
  4. Install Windows Server 2012 R2 Standard Edition on the server
    1. Use two internal drives in RAID 1 with the integrated RAID controller
      1. Consider using SSDs for this purpose, if possible
      2. If you use SSDs, you do not need to defragment them
    2. Create a single partition, C: drive that uses all of the space
    3. Change the windows page file size to 16GB, using the C: drive
    4. Change the Windows power plan to “High Performance”
      1. Run CPU-Z on the server to confirm that the processors are running at full speed
    5. Change the drive letter for the optical drive to Z:
  5. Change the NETBIOS name on the server to the desired permanent name of the server
  6. Install the .NET 3.51 feature from Windows Server 2012 R2
  7. Install Microsoft Update on the server
    1. This is a superset of Windows Update
  8. Install all Microsoft and Windows Updates on the server
    1. This may require several rounds to get all of the required updates
  9. Manually defragment the C: drive
    1. Enable automatic defragmentation of the C: drive using the default weekly schedule
    2. Do not allow new drives to be automatically added to the schedule
  10. Create a static IP address with the correct DNS and default gateway information
  11. Join the server to the appropriate Windows domain
  12. Activate Windows on the server
  13. Install the latest version of Dell OMSA on the server
  14. Download the latest version of the Dell Server Update Utility (SUU)
    1. Mount the .iso for the SUU, and run the SUU
    2. This will ensure that you have the latest firmware and drivers for the server
  15. Use Dell OMSA to create RAID arrays for LUNs
    1. Create one LUN at a time, then go to Logical Disk Manager to create/format the drive
      1. This way you won’t get confused about which one is which
      2. Create the arrays and LUNs in the order shown below
    2. General PERC Settings in Dell OMSA
      1. Use intelligent Mirroring for RAID 10 arrays
      2. No Read Ahead Cache
      3. Enable Write-Back Cache
      4. Cache Policy should be enabled
      5. Use 64K allocation unit
  16. Use the Windows Logical Disk Manager to create Logical Disks
    1. After you create an array with OMSA, open Disk Manager
    2. You will see the Initialize Disk dialog
      1. Make sure to use the GPT partition style
      2. glenn_chk_1

    3. After you initialize the disk, right click on it and choose Create Simple Volume

      glenn_chk_2

    4. Accept the default and click Next

      glenn_chk_3

    5. Assign the appropriate drive letter and click Next

      glenn_chk_4

    6. Change the allocation unit size to 64K, and change the Volume label to the correct name
      1. Click Next
      2. Click Finish

        glenn_chk_5

  17. The new logical drive should appear in Windows Explorer
  18. All logical drives that are needed for SQL Server usage need to be created before you install SQL Server 2012
  19. Test the performance of each logical drive with CrystalDiskMark
  20. Test the performance of each logical drive with SQLIO
  21. Create this standard directory structure on each drive
    1. Data drives: SQLData
    2. Log drives: SQLLogs
    3. TempDB drives: TempDB
    4. Backup drives: SQLBackups
  22. Use Group Policy Editor (GPEDIT.MSC) to grant these Windows rights to the SQL Server Service Account
    1. Perform Volume Maintenance Tasks
      1. YourDomain\SQLServerServiceAcct
    2. Lock Pages in Memory
      1. YourDomain\SQLServerServiceAcct
  23. Install SQL Server 2012 Enterprise Edition
    1. Make sure there are no pending reboots or else SQL Server 2012 will not install
    2. Only install the SQL Server 2012 components that are required for this instance
    3. Use Mixed Mode authentication
      1. Set the sa password to a strong password
      2. Add yourself as a SQL Administrator
      3. Add any other DBAs who need to be administrators
    4. Use YourDomain\SQLServerServiceAcct for the SQL Server Service account
      1. The password is: xxxxxxxxx
    5. Use YourDomain\SQLServerAgentAcct for the SQL Server Agent account
      1. The password is: xxxxxxxxx
    6. Set the SQL Server Agent Service to Automatic startup
    7. Set the default directories to the appropriate drive letters and paths
      1. User database directory: P:\SQLData
      2. User database log directory: L:\SQLLogs
      3. Temp DB directory: T:\TempDB
      4. Temp DB log directory: T:\TempDB
      5. Backup directory: N:\SQLBackups
  24. Install SQL Server 2012 Service Pack 1
    1. Or the latest released Service Pack for SQL Server 2012
  25. Install SQL Server 2012 Service Pack 1 Cumulative Update 6
    1. The cumulative update is available from this location:
      1. http://support.microsoft.com/kb/2874879/en-us
      2. Make sure to get the x64 version of the update package
    2. Manually defragment the C: drive after installation
      1. This is not necessary if you are using SSDs
  26. Change SQL Server 2012 Instance Level Properties
    1. Enable optimize for ad hoc workloads
      1. This will allow SQL Server to use less memory to store ad hoc query plans the first time they are executed
    2. Set Max Degree of Parallelism to the number of physical cores in a NUMA node on your server
    3. Enable Default Backup Compression
      1. This will use SQL Server backup compression by default for all database backups
    4. Add trace flag 3226 as a startup option in SQL Server Configuration Manager
      1. This will suppress logging of successful database backup messages in the SQL Server error log
    5. Add trace flag 1118 as a startup option in SQL Server Configuration Manager
      1. This will help alleviate allocation contention in tempdb
    6. Enable Database Mail on the instance
      1. This will help allow SQL Server to send e-mail notifications for SQL Server Agent Alerts and when SQL Server Agent jobs fail
    7. Set Max Server Memory to an appropriate, non-default value
      1. This value depends on how much physical memory is available in the server
        1. It also depends on what SQL Server components are installed
      2. Here are some example values:
        1. 96GB Total RAM: Set Max Server Memory to 87000
        2. 64GB Total RAM: Set Max Server Memory to 56000
        3. 32GB Total RAM: Set Max Server Memory to 27000
    8. Create three additional TempDB data files in the T:\TempDB directory
      1. All TempDB data files should be 4096MB in size
        1. Set Autogrow to 1024MB
      2. The TempDB log file should be 1024MB
  27. Confirm that you can ping the SQL Server machine from another machine on the domain
  28. Using SQL Server 2012 Configuration Manager, confirm that TCP/IP is enabled for the instance
  29. Confirm that you can connect to the SQL Server instance remotely using SSMS on another machine
  30. Create a SQL Server Operator on the instance
    1. Use DBAdmin with an e-mail address of dbadmin@yourcompany.com
  31. Confirm that Database Mail is operating correctly
    1. Right-click on Database Mail and send a test message
  32. Configure SQL Server Agent Mail to use Database Mail
  33. Create SQL Server Agent Alerts for the following errors:
    1. YourServerName Alert – Sev 19 Error: Fatal Error in Resource
    2. YourServerName Alert – Sev 20 Error: Fatal Error in Current Process
    3. YourServerName Alert – Sev 21 Error: Fatal Error in Database Process
    4. YourServerName Alert – Sev 22 Error Fatal Error: Table Integrity Suspect
    5. YourServerName Alert – Sev 23 Error: Fatal Error Database Integrity Suspect
    6. YourServerName Alert – Sev 24 Error: Fatal Hardware Error
    7. YourServerName Alert – Sev 25 Error: Fatal Error
    8. YourServerName Alert – Error 825: Read-Retry Required
    9. YourServerName Alert – Error 832: Constant page has changed
    10. YourServerName Alert – Error 855: Uncorrectable hardware memory corruption detected
    11. YourServerName Alert – Error 856: SQL Server has detected hardware memory corruption, but has recovered the page
  34. A generic script to create these SQL Server Agent Alerts is available here:
    1. Make sure each agent alert has a response to notify the DBAdmin operator
  35. Create a SQL Server Agent job called Nightly Free System Cache that runs this command:
    1. DBCC FREESYSTEMCACHE ('SQL Plans');
    2. Runs every night at 12:00AM
  36. Download the latest version of Ola Hallengren’s SQL Server Maintenance Solution script:
    1. http://ola.hallengren.com/
    2. Open the MaintenanceSolution.sql script while connected to the instance
      1. Modify the @BackupDirectory variable to N:\SQLBackups
      2. Run the script to create eleven new SQL Server Agent jobs
      3. For each job, go to the Notifications property window and have the job e-mail the DBAdmin group if the job fails
      4. For each job, create a schedule for when it will run.
      5. Here is a suggested schedule for the jobs:
        1. CommandLogCleanup Sunday at 12:00AM
        2. DatabaseBackup – SYSTEM_DATABASES – FULL Daily at 11:55PM
        3. DatabaseBackup – USER_DATABASES – DIFF Daily at 12:00PM
        4. DatabaseBackup – USER_DATABASES – FULL Daily at 12:00AM
        5. DatabaseBackup – USER_DATABASES – LOG Hourly
        6. DatabaseIntegrityCheck – SYSTEM_DATABASES Saturday at 7:55AM
        7. DatabaseIntegrityCheck – USER_DATABASES Saturday at 8:00AM
        8. IndexOptimize – USER_DATABASES Sunday at 8:00PM
        9. Output File Cleanup Sunday at 12:00AM
        10. sp_delete_backuphistory Sunday at 12:00AM
        11. sp_purge_jobhistory Sunday at 12:00AM

  20 Responses to “General Database Server Build and Deployment Instructions”

  1. Very nice, thank you. I'm catching up with some of the items right now!

    Do you really recommend clearing the plan cache every night? Why?

  2. How about trace flags 4199 and 3042? 4199 is a general optimizer flag and 3042 stops compressed backups from giving you a full backup and then compressing once the backup is finished.

  3. I only recommend clearing the ad-hoc plan cache, which often has a large number of single-use plans or plan stubs in the cache, even with "Optimize for ad hoc workloads" enabled.

  4. @Chris Wood, be aware that 3042 reduces performance because the bak file has to grow repeatedly until it has reached its final size. SQL Server compresses immediately in all cases. Just the preallocation is disabled with this flag.

  5. Hi Glenn,
    Thanks for a great article. Below are a few questions:

    1. Shouldn't we have the number of tempdb files equal to the number of logical CPUs?
    2. Would it be a good idea to enable 'Forced Parameterization' to improve query performance?

    Cheers,
    Shankar

    • Setting the number of tempdb data files to the number of logical or physical CPU cores is outdated guidance. Per Bob Ward at Microsoft, I think it is much better to start at four or eight tempdb data files, and then to monitor for any signs of allocation contention in tempdb, possibly adding more data files if necessary.

      Forced parameterization can hurt query performance in some cases, so you really would want to test it with your workload. I would definitely not enable it as a default setting for a database.

      • Hi Glenn,
        Thanks for the clarification.

        On the sizing of tempdb files(data & log), do the sizes 4096M & 1024M have any significance?

        In case of an instance with a single small user database say 10G, would it be still apply?

        Cheers,
        Shankar

        • The sizes are probably just baselines or starting points. You want to avoid having TempDB grow so if those sizes are not sufficient, then adjust accordingly. On the contrary, they may be large for your environment – I have some instances where 2GB data files are fine.

  6. Shankar,

    Unless you are really worried about disk space, having your tempdb data and log files start out much smaller really does not make that much sense. You want to try to avoid having the tempdb data and log files having their autogrow kick-in during normal usage if possible.

  7. […] Berry's General Database Server Build and Deployment Instructions provides a useful build checklist for setting up new SQL Server […]

  8. Hi Glenn,

    Very comrehensive writeup!
    The only alternation for me would be splitting the multiple TEMPDB data files over different LUNs\drives if available

    Cheers
    Felix

  9. Thanks Glenn. Bookmarked. I will see what I can take from your checklist and add them to mine.

    Just a few other things on my list.
    Verify disk alignment or get server and storage guys to confirm
    Verify compressed volume is not used
    Increase number of error log and set up job to recycle it
    Enable remote admin connection
    For vitual machines, on host:
    VM-VM Affinity Rules should be applied to ensure “Separate Virtual Machines” is selected
    Enable Strict Enforcement for Affinity Rules. Specifically “ForceAffinePoweron” option is set to type 1
    DRS should be set to Partially Automated
    Pass-through RDM (physical compatibility mode)
    Reserve all guest memory (All locked) is checked (for particular vm)

  10. Why "Change the windows page file size to 16GB, using the C: drive", This seems to be a hard value. Should it not be configured in line with OS Memory ?

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">