General Database Server Build and Deployment Instructions

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

    

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

       

    4. Accept the default and click Next

       

    5. Assign the appropriate drive letter and click Next

       

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

          

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