THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

SQL Server Installation Checklist

The other night I was asked on Twitter by Todd McDonald (Twitter), for a build list for SQL Server 2005 and 2008.  My initial response was to provide a link to the SQL Server Build List Blog, which documents all of the builds of SQL Server and provides links to the KB articles associated with the builds.  However, this wasn’t what Todd was after, he actually wanted a reference for an installation checklist for SQL Server.  I have a number of these that I use in my job, and they vary based on the server being configured.  I use an Excel spreadsheet as a pre-production use Audit sheet to ensure that all of the critical steps have been performed, and that the server meets the minimum standard configuration I have for SQL Server.

Since I have never actually blogged my lists, but I have shared them by email with people requesting them in the past, I jumped over to Brent Ozar’s (Blog/Twitter) blog, because this is just the kind of thing that Brent would have blogged at some point and did a quick search for Checklist and sure enough Brent had a Pre-Installation Checklist as well as a Post-Installation Checklist for SQL Server 2005.  My personal installation/configuration steps differ slightly from Brent’s but for someone looking for a checklist to follow, Brent’s two posts are great references.

My own checklist has been shaped by personal experiences as well as the methods used by the Server Administrators at my previous jobs.  I like to have a cookie cutter installation for all of my SQL Servers, where things are, to the extent possible, identical for all of the servers in my environment.  This starts off with the installation of the Operating System, and continues on through the installation, and subsequent post installation configuration of SQL Server.  I have had the luxury of having really good mentors along my path to becoming a Sr. DBA on both the Windows and SQL Server sides of administration.  It doesn’t matter if I am installing SQL Server on a VM or a physical server, the end configuration is generally identical.

Windows OS Installation

  1. All drives partition aligned.
  2. Hyper threading disabled in the Bios.
  3. OS and installed applications drive use RAID 1 and use NTFS with default Allocation Unit Size.
  4. OS installed to C Drive.
  5. Domain Administrators group added to the Local Administrators group.
  6. Account Policies enforced by GPO or set explicitly.
    1. Password Policy
      1. Enforce password history = Last 10
      2. Maximum password age = 90 days
      3. Minimum password age = 7 days
      4. Minimum password length = 8
      5. Password must meet complexity requirements = Enabled
    2. Account Lockout Policy
      1. Account lockout threshold = 5 invalid login attempts
  7. Local Policies enforced by GPO or set explicitly.
    1. Audit Policy set to audit Success and Failure of
      1. Audit account logon events
      2. Audit account management
      3. Audit logon events
      4. Audit policy change
      5. Audit system events
    2. Security Options
      1. Interactive logon: Do not display last user name – Enabled
      2. Interactive logon: Message text for users attempting to log on – Set to Legal Disclaimer for access to production servers
      3. Interactive logon: Message title for users attempting to log on – Set to Legal Message Titled for access to production servers.
  8. Everyone User removed from non-C drives.
  9. All applications installed to D Drive and not C Drive.
  10. Windows Updates configured to download but not install.
  11. NIC’s configured as teamed (if appropriate), set to Full Duplex and maximum network speed (usually 1GB).
  12. Validate IO Subsystem configuration is optimal using SQLIO and test alternate configurations to determine optimum configuration for SQL.
  13. If using SAN Storage test HBA Queue Depth settings at 64 and 128 in conjunction with SAN admin to determine the optimal setting for the server based on IO demands and impact to other systems using the SAN, ensure that MPIO is configured properly.  (Going to high on the SQL Server can allow it to dominate the SAN, reducing performance of other systems using SAN storage on different disk arrays)
  14. Anti-Virus Software installed and configured to update from root server.
  15. System Added to SCOM for monitoring.

Pre-Installation of SQL Server

  1. Separate RAID Arrays for Data and Log files.  Tempdb on dedicated array.
  2. Data, Log, and Tempdb drives formatted with 64K Allocation Unit Size.
  3. SQL Server Admins Group added to the Local Administrators Group.
  4. Create AD Service User Account, or Local User Account for non-domain servers, with no permissions.
    1. Add the readServicePrincipalName and writeServicePrincipalName permissions to the Service Account in AD (http://support.microsoft.com/kb/319723)
  5. Configure the Data drive with Drive letter E in Windows.
  6. Configure the Log drive with Drive letter L in Windows.
  7. Configure the TempDB drive with Drive letter T in Windows.
  8. Configure additional Data drives with Drive letter F, G, etc. skipping previously reserved Drive letters and M (cluster MSDTC) and Q (cluster Quorum).
  9. Add the AD Service User Account to the Root path with Full Control of D, and List Folder Contents Permissions for Data, Log and Tempdb Drives.
  10. Create SQLData folder on Data and Tempdb Drives
  11. Add the AD Service User Account with Full Control of SQLData folder on Data and Tempdb Drives
  12. Create SQLLogs folder on Log Drive
  13. Add the AD Service User Account with Full Control of SQLLogs folder on Log Drive

SQL Server Installation

  1. Use the previously configured Service Account as the startup account for the SQL Service.
  2. Install the binaries to the D Drive.
  3. If installing SQL Server 2008 set the default file paths according to the previous drive configuration.
  4. Set SQL Server, and SQL Agent to startup Automatically.  Disable the Browser Service unless installing Named Instances or multiple instances on the Server.
  5. Apply latest Service Pack and Cumulative Update based on SQL Server version.
  6. Provision SQL Admins group in the sysadmin fixed server role.

Post-Installation Steps

  1. Add the SQLServerMSSQLUser$<ServerName>$<InstanceName> group to the Root path with Full Control of D, and List Folder Contents Permissions for Data, Log and Tempdb Drives.
  2. Add the SQLServerMSSQLUser$<ServerName>$<InstanceName> group with Full Control of SQLData folder on Data and Tempdb Drives.
  3. Add the SQLServerMSSQLUser$<ServerName>$<InstanceName> group with Full Control of SQLLogs folder on Log Drive.
  4. Remove the AD Service User Account from the Root Path. (This decouples the Service Account explicitly and relys on the group)
  5. Add the SQLServerMSSQLUser$<ServerName>$<InstanceName>, SQLServerSQLAgentUser$<ServerName>$<InstanceName>, or other group accounts to any Backup, or processing folders as needed.
  6. In the Local Security Policy, add the SQLServerMSSQLUser$<ServerName>$<InstanceName> group to the Perform Volume Maintenance Tasks and Lock Pages in Memory objects.
  7. Exclude Data, Log, Tempdb, any Backup file paths, and the SQL Server Binaries folders from AntiVirus Scans.
  8. Remove Builtin\Admins from sysadmin fixed server role.
  9. Enable Failed Login Auditing in the SQL Server Security Settings
  10. Enable TCP/IP and change default port from 1433.
  11. Enable remote DAC connections.
  12. Enable as required xp_cmdshell, SQLCLR, and OLE Automation for the SQL Server Instance.
    1. Configure xp_cmdshell proxy account as required.
  13. Enable DatabaseMail and configure default public and private accounts.
  14. Configure SQL Error Log retention for 30 log files
  15. Configure SQL Agent job to perform nightly log rollover.
  16. Configure SQL Agent jobs for database backups, CHECKDB, index maintenance, statistics updates, backup cleanup, and history cleanup.
  17. Move MSDB Database files to SQLData and SQLLogs respectively.
  18. Reconfigure Tempdb with data files equal to 1/2-1/4 the physical CPU’s on the server based on load characteristics.  Set data files to the same size based on load characteristics in 4096MB increments for Datafiles, and 1024MB increments for Log files.  Set AutoGrowth to 1024MB for data files and 512MB for Log file.
  19. Enable Trace Flag 1118 on SQL Server 2000 and SQL Server 2005 for Tempdb.
  20. Set Model database to SIMPLE recovery, 2048MB default datafile size and 1024MB default logfile size.  Set AutoGrowth to 1024MB for data files and 512MB for Log file.
  21. Set Max Server Memory based on installed RAM and installation type (Newer Servers are all 64bit, but enable AWE as needed on 32 bit servers).
    1. 8GB RAM = 6144 Max Server Memory
    2. 16GB RAM = 12228 Max Server Memory
    3. 32GB RAM = 28672 Max Server Memory
    4. These are base values that will later be adjusted based on the Memory\Available MBytes counter being > 150 on the Server.
  22. Set max degree of parallelism sp_configure option based on the number of physical CPU cores installed and anticipated workload
    1. For OLTP, generally set to 1/2 or 1/4 of the physical cores available on the server.
    2. Adjusted up or down based on wait stats and load.
  23. Set cost threshold of parallelism sp_configure option based on the anticipated load.
    1. General default value of 5 is low for most OLTP workloads and should be increased.
    2. Base value of 20-25 used for most server installs.
  24. Add AD login (standard for environment and locked out in AD by default) for patching and emergency server access to Local Administrators Group.
  25. Set SA user password to standardized password that is changed quarterly on all servers and maintained in password safe.
  26. Have Server Team remove SQL Admins from Local Administrators Group. ( Not yet implemented in my environment but coming very soon! )

As you can see there are a number of steps involved in provisioning a new SQL Server.  Checklists such as this one ensure that all of the require steps have been accomplished and ensure that the servers are configured identically.   This simplifies documentation and management of the systems in the environment.  If you read Brent’s blog posts, you will notice that there are steps included in his checklist, such as setting up alerts for critical errors, that aren’t in my own list.   The reason for this is that we use SCOM for monitoring, and have built custom Rules into SCOM that capture these errors and generate alerts through SCOM for those events.  This simplifies the configuration of individual servers since the rules are standard for all SQL Servers managed by SCOM.

Published Monday, March 22, 2010 11:31 PM by Jonathan Kehayias

Comments

 

Shawn Johnson said:

Jonathan.  

Awesome checklist--thanks for sharing!  

One thing that I noticed is that I didn't see anything for disabling services that aren't required on the server.  Do you do this or just leave them alone or is that part of the AD policies?  

Thanks

SJ

March 22, 2010 11:39 PM
 

Jonathan Kehayias said:

Shawn,

I haven't gotten down to that level yet, but I am always looking at ways to improve my configuration process, so by all means reply back with a comment about what services to disable and why, and I will make sure to look at the recommendations.

March 23, 2010 12:05 AM
 

Chris Randall said:

Very nice! I saw you mention this in Twitter today and hoped you'd post it. Thanks for sharing.

March 23, 2010 12:34 AM
 

Jonathan Kehayias said:

Chris,

Thanks, if you have anything to add to the list, please feel free to leave comments.  I am always looking for gaps in my process.

March 23, 2010 12:42 AM
 

Uri Dimant said:

Hi Jonathan

Well done. I have a question about 18.. I assumed  you meant Set AutoGrowth to 1024MB for data files and 512MB for Log file --pointed to the datafiles/log file of tempdb not a users database?

Also , why do we need to enable Trace 1118  for SP >2?

March 23, 2010 1:58 AM
 

Sankar Reddy said:

Jonathan,

Wow. This is going to be extremely valuable to the community. Its going to take sometime to fully understand all the information you shared but on a quick glance you haven't mentioned about the disk controller cache. Do you always keep them 100% write cache?

March 23, 2010 2:10 AM
 

Jonathan Kehayias said:

Uri,

Point 18 deals entirely with Tempdb and its default configuration.  For Trace Flag 1118, this has been a point of question and debate as to whether it makes a difference on later builds of 2005 and 2008.  Linchi Shea put a challenge out last year to prove the need for this in 2005/2008:

http://sqlblog.com/blogs/linchi_shea/archive/2009/10/01/sql-server-challenge-show-me-trace-flag-1118-is-significant.aspx

This may very well be something that gets removed from the list at some point, but its not proven to be problemattic so for now it stays in the list.

March 23, 2010 7:42 AM
 

Eric Wisdahl said:

Thanks Jonathan!  Very helpful and interesting article.

March 23, 2010 8:38 AM
 

Jonathan Kehayias said:

Sankar,

That's a good question and its not one that I actually mess with, the hardware is configured by a member of the server team (not necessarily correctly in all cases).  I'll have to double back and check on this.

March 23, 2010 9:24 AM
 

WIDBA said:

Good stuff.  My team has been working on this for disaster recovery for 2k8 servers.  I am going to do a compare and see if we are missing anything.  Interesting that you don't set DOP to 1 - which seems to be a standard for OLTP servers.

Thanks -

March 23, 2010 9:46 AM
 

Paul Sligar said:

Jonathan:

Thanks for the list. Question about #8 in OS Install section: Did you mean that the Everyone user <b>permissions</b> were to be removed from all non-C drives?

March 23, 2010 11:45 AM
 

Jonathan Kehayias said:

Paul,

Yep, Everyone and the local Users group both get removed from the root.

March 23, 2010 12:35 PM
 

Nick Beagley said:

Without Local Admin access for your sql admins group you'll need to set full control permissions on the non-c drives for 'sql admins' when you set them for the built in sql server local groups.

Otherwise you may hit problems since you've removed 'user' from having access.. unless that was your intention. But no access at all to the file system makes moving backups around difficult, you can use xp_cmdshell but sometimes the situation just calls for you to remote desktop on and map to some other location as your self to copy files to/from your SQL Server

March 23, 2010 7:50 PM
 

Jonathan Kehayias said:

Nick,

No, under normal circumstances I don't need full control permissions for the SQL Admins group.  I don't need to move backups around, they are stored in a centralized remote storage system.  Step #24 provides a method of gaining access as a local administrator to the server(s) if necessary through a documented request for access (I am currently working on application in .NET to automate this with a workflow.) This specific configuration might not work for every shop, especially if there isn't a segregation of responsibilities between teams.

If there is an emergency that I can't solve through SQL Server, the Windows Admins get involved.  SCOM manages monitoring the system so I know when a problem occurs, or when disk space is running low, CPU is high, etc.  I got a tour of the data center once, it had a bunch of lights and cables inside of these steal cages bolted to the floor, and it was quite noisy and cold. (There is a bit of truth behind the sarcasm here sadly.)

March 23, 2010 10:40 PM
 

dugi said:

Yep very nice list, a lot of stuff to check from start to end installation for SQL Server.

March 25, 2010 5:41 AM
 

josantia said:

Hi,

Nice list, I would add disabling TCP Chimney, RSS and NetDMA: http://support.microsoft.com/kb/951037/en-us

And disabling TCP Offload in Nics:

http://blogs.msdn.com/psssql/archive/2010/02/21/tcp-offloading-again.aspx

Regards,

Jose Santiago Oyervides.

March 25, 2010 12:33 PM
 

Dale123 said:

Also, I configure the page file.

Min 1/2 x RAM

Max 2 x RAM

March 25, 2010 1:12 PM
 

John Dunleavy said:

Nice list.  I don't see any mention of the sizing for the page file. What would you reccommend for that?

March 28, 2010 1:37 AM
 

Ewan said:

Hi Jonathan

Thanks for the list - nice work.

On thing I've recently added to our build is to *force* all servers (SQL2005 and above) to listen on 1433. This may not be appropriate for all situations from a security point of view, but it does allow me to finally get away from named instance name-resolution. I'll qualify this by saying that I only have named instances on clustered installs, so every instance has a dedicated IP.

I've done this specifically in relation to Linchi's recent post about aliasing database connections.

http://sqlblog.com/blogs/linchi_shea/archive/2009/12/28/bad-database-practices-allowing-apps-to-connect-to-the-hostname.aspx

Ewan

March 29, 2010 6:26 AM
 

xs2varun said:

Hi Jonathan

Thanks a lot for such a great site.

Is it safe to add increase the write cache on disks in the pre installation checklist.

I read that For SQL Server systems, reserving a larger percentage of cache for writes is likely to result in better I/O performance.

What are the impacts of increaing the write cache on storage and should it be done on the log drives?

September 2, 2010 3:45 PM
 

Rick said:

Perhaps it's outside the scope of this checklist, but I would add:

1. Stopping SQL Server and copying master.mdf to a standard location on your network (easier than rebuilding it if your server crashes)

2. If installing Reporting Services, backup your encryption keys to a standard location on the network, as well.

Great checklist!

January 14, 2011 3:17 PM
 

Mahanaz said:

Excellent John.Really helpful,Keep it up.

February 3, 2011 11:22 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement