SQL Server Installation Checklist

This is my version of SQL Server installation check-list.

Pre-SQL Installation

 

  1. Service Account
  2. Procure all AD Service accounts. Preferably one for each SQL Server service.
  3. Register an SPN. DBAs often do not have permission to do this. Have this done by a domain admin now so it is ready to go after the installation:
  4. Add the readServicePrincipalName and writeServicePrincipalName permissions to the SQL Server service account in AD.
  5. Run the following:
    setspn -S MSSQLSvc/ServerName SQLServiceAccountName
    setspn -S MSSQLSvc/ServerName:1433 SQLServiceAccountName
    setspn -S MSSQLSvc/ServerName.root.DomainName:1433 SQLServiceAccountName
  6. Server Configuration
  7. Install the latest applicable Windows Updates. Ensure there are no pending reboots before continuing. Run Disk Defragmenter afterwards.
  8. Add the SQL Server service account to the following Local Security Policy.
  9. Replace a process level token
  10. Lock Pages in Memory (LPIM).  May not be necessary in Windows 2008 or newer. Do it anyway.

iii.    Perform Volume Maintenance Task

  1. Set the queue depth to an appropriate level. Preferably 128 or higher.
  2. Set any virus scan software to not scan any MDF, NDF, LDF, BAK or TRN files.
  3. Set all NICs, physical or VM, to full duplex.
  4. Set BIOS or VM power management to OS managed, then set to full power.
  5. Format data drives with an allocation unit size of 64k, and 8192 bytes for log drives.
    Verify using the fsutil utility: fsutil fsinfo ntfsinfo [drive letter]:
  6. Set the pagefile to 150% of the total RAM. If this is too large, then set it to 100% + 1 MB.
  7. Create local “SQL Server Admins” group, add applicable users.
  8. Add applicable users to local Administrators and RDP groups.
  9. Disk Configuration
  10. Drives and Directories – Create the following directory structure for a single SQL instance. Also consider directory structure requirements for other services, such Analysis Services.
  11. OS – C:
  12. SQL  Installation – D:

iii. Data drive(s) – E:\SQLData, add more as needed.

  1. Log drive – L:\SQLLogs
  2. Backup drive – N:\Backups
  3. TempDb drive – T:\TempDb
  4. Disk format configuration
    1. Disable Indexing on disk volumes
    2. Disable generation of 8.3 names (command: FSUTIL BEHAVIOR SET DISABLE8DOT3 1)
  • Disable last file access time tracking (command: FSUTIL BEHAVIOR SET DISABLELASTACCESS 1)
  1. Keep some space empty (let us say 15% for reference) on drive is possible
  2. Defragement the volume (Only on Filestream Data storage volume)
  3. Service Account Permissions
  4. Remove “Everyone” from non-C drives.
  5. Grant the following permission to the SQL Server and SQL Agent service accounts
  6. SQL Server
  7. D: – Full control
  8. E: – List folder contents
  9. L: – List folder contents
  10. T: – List folder contents
  11. E:\SQLData – Full control
  12. L:\SQLLogs – Full control
  13. T:\TempDb – Full control
  14. SQL Server Agent
  15. D: – Full control
  16. N: – List folder contents
  17. N:\SQLBackups – Full control

SQL Installation

  1. Features – Install the features needed for the instance. Do not install something “just in case.”
  2. Shared feature directory: D:\Program Files\Microsoft SQL Server\
  3. Shared feather directory (x86): D:\Program Files (x86)\Microsoft SQL Server\
  4. Instance root: D:\Program Files\Microsoft SQL Server\
  5. Service Accounts: add applicable AD service accounts with Automatic Startup
  6. SQL Server Bowser: Disabled if installing only one instance.
  7. Authentication Mode: Mixed, or as appropriate.
  8. Add “SQL Server Admins” local group to SQL Server Administrators
  9. Data Directories
  10. Root: E:\SQLData
  11. User database: E:\SQLData\
  12. User database logs: L:\SQLLogs
  13. Tempdb data and logs: T:\TempDb
  14. Backup: N:\Backups

Post-SQL Installation

  1. Install any SQL Server or Visual Studio service packs and updates.
  2. Check that Lock Pages in Memory is being used. Restart SQL service if not done after updates.
  3. Confirm the SPN was registered:
  4. Restart SQL service if not done after updates.
  5. The query below should return “KERBEROS” if registration was successful. Run from a remote connection:
    SELECT auth_scheme
    FROM sys.dm_exec_connections
    WHERE session_id = @@spid
  6. Added applicable users to sysadmin server role.
  7. Set Model database settings. Adjust the sizes as appropriate. Here is a good start:
  8. SIMPLE recovery
  9. Data file: 2048 MB Initial Size, 1024 MB autogrowth, unlimted size
  10. Log file: 1024 MB Initial Size, 512 MB autogrowth, unlimited size
  11. Move .mdf and .ldf if necessary.
  12. Set master and msdb database settings. Adjust the sizes as appropriate. Here is a good start:
  13. SIMPLE recovery
  14. Data file: 1024 MB autogrowth, unlimted size
  15. Log file: 512 MB autogrowth
  16. Set tempdb database settings. Use Glenn Berry’s suggested settingsfor a starting point.
  17. SIMPLE recovery
  18. Add additional data files as appropriate:
    Logical name = tempdev2, File name = tempdb2.ndf.
  19. All tempdb data files: 4096 MB Initial Size, 1024 MB autogrowth, unlimted size
  20. Log file: 1024 MB Initial Size, 512 MB autogrowth, unlimited size
  21. Set trace flag -T1118. This helps elevate contention in tempdb. There is some debate on this, but it looks like it does not hurt to have it on. Paul Randal’s blog goes into detail.
  22. Enable the DAC. (sp_configure ‘remote admin connections’, 1)
  23. Set Optimize for Ad hoc Workloads to True
  24. Set Min and MAX Server Memory to an appropriate level. Use Glenn Berry’s suggested settingsfor a starting point.
  25. Set Cost Threshold for parallelism as appropriate.
  26. Set Max Degree Parallelism as appropriate.
  27. Setup database mail.
  28. Create Administrator Operators.
  29. Enable Alert System in SQL Agent using the profile just created.
  30. Enable fail-safe operator for notification types.
  31. Configure SQL Error Log retention for 31 log files, or whatever is appropriate.
  32. Create job to cycle error logs: sp_cycle_agent_errorlog, sp_cycle_errorlog.
  33. Configure alerts for severity 16 through 25 as well as specific alerts for 823, 824 and 825 errors.
  34. Confirm SQL Server service account is NOT in the local Administrators group.
  35. Install Brent Ozar’s sp_blitz. Address issues.
  36. Install and MS Baseline Config Analyzer and SQL Server 2012 BPA on your workstation, run. Address issues.
  37. Install Ola Hallengren’s Maintenance Solution
  38. Setup backups
  39. Setup integrity checks
  40. Setup index maintenance
  41. Setup history maintenance

 

Advertisements
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s