Step by Step: Install SQL server 2008 R2 for SharePoint 2010

This article contain detailed steps to install SQL server 2008 R2 with best practices and guidelines from Microsoft and other many blogs over the internet, and I try to put all these best practices and guidelines in one article with step by step installation guide.

Pre-Installation Steps

Before install SQL server 2008 R2, we need to check the pre-install requisites:

Operating Systems Requirements:

• Plan to install SQL on a NTFS file system
  
• Do no try to install SQL Server on a compressed, encrypted or read-only drive, because setup will block the installation
  
• Do not install SQL Server on a Domain Controller.
• Verify Windows Management Instrumentation service (Control Panel -> Administrative Tools -> Services) is running
  
• Configure your firewall to allow SQL Server access. Click here for more information about protocols and port numbers
  
• Create dedicated service account for each service to be installed. Ensure that these accounts are not member of Local Administrator because it will give unnecessary rights to these accounts, see SQL services accounts
  
• The user account that is running SQL Server Setup must have administrative privileges on the computer, see SQL services accounts
  
• Group all SQL services accounts in one AD Organization Unit for easy management and understandable hierarchy.
  
• At least 6.0 GB of disk space are required by SQL Server setup

Additional software requirements and hardware requirements:

• Enable .NET Framework 3.5.
  
• PowerShell 2.0.

Note: run Windows Updates to get latest updates after install and check all previous requirements.

SQL services accounts

For a SharePoint installation, this section recommends the following best practices and naming conventions for service accounts. In your deployment, you may not need all these accounts

SQL_Admin	SQL Admin on the SQL Server. Used to Install the SQL Server.	Local Administrator on the SQL Server	Domain User
SQL_Agent	It is the service account for the following SQL Server services: SQL SERVER AGENT.	None	Domain User
SQL_Engine	It is the service account for the following SQL Server services: Database Engine.	None	Domain User
SQL_Reporting	It is the service account for the following SQL Server services: SQL Reporting Service.	None	Domain User
SQL_Analysis	It is the service account for the following SQL Server services: SQL Analysis Service.	Non	Domain User

From practical experience, create a security group for SQL Administrators “SQL_Admins_Grp“, and add the “SQL_Admin” to this security group. In future if you want to want to give SQL Admin permission to any user; just add the user to this group and it will inherit all permission setting from this group in SQL server.

After create the Organization Unite and Service Accounts in Active Directory, you should have the following service accounts in AD:

Login to all SQL Server(s) as domain admin, and add SQL_Admins_Grp security group to Local Administrators Group on all SQL Server(s).

Now, you are ready to next step: install SQL Server 2008 R2.

---

Installation Steps

The following is the steps to install SQL server 2008 R2 on Windows Server 2008 R2 with SP1

1. Login to SQL Server using SQL_Admin account
   
2. Insert SQL Server Setup media and Open it, select “setup.exe” file, and Right-Click on it and select
   Run as administrator
   
   
3. The “SQL Server Installation Center” will be open, Select “Installation” from left menu, then select first option for new SQL server installation
   
   
4. The “Setup Support Rules” form will open and check some SQL pre-requests to identify problems that might occur when you install SQL Server Setup Support files. Make sure all checks are passed, then click “OK” button to move for next step.
   
   
5. The “Product Key” form will open, you should enter the Product Key in this form or select “Evaluation” as a free edition, click “Next” to move for next page.
   
   
6. Accept the License Terms and click on “Next” button
   
   
7. Click on “Install” button, to start installation of Setup support Files
   
   
8. Installation of “Setup Support Files” will start
   
   
9. After finish Installation of “Setup Support Files”, click on “Next” button to move for next Page.
   Please note the Windows Firewall warning, make sure you add “Inbound rule” in Fire to allow SQL Access ports
    
10. In “Setup Role” page, select first option “SQL Server Feature Installation” to select what features you want to install. Click on “Next” to move to next page
    
11. In “Feature Selection” page, select needed services and SQL management tools. Click “Next” button to move for next page
    
    Please to make sure you change the installation directory drive. Do not install SQL server on system drive (C: Drive)
    
    
12. In “Installation Rules” page, make sure all rules are passed or skipped (green checked), click on “Next” button if there is no Failed check.
     
13. In “Instance Configuration” page, select “Default Instance” and change the directory path of “Instance root directory”, you can change the instance name for advanced setting and scenarios.
    
    Please to make sure you change the installation directory drive. Do not install SQL server on system drive (C: Drive)
    
    
14. In “Disk Space Requirements” page, just check if you have available space and click on “Next” button
     
15. In “Server Configuration”, in “Service Accounts” Tab, enter the service account username and password for each SQL service as following
     
16. In “Server Configuration”, in “Collation” Tab, click “customize” button and select the following collation “Latin1_General_CI_AS_KS_WS“, this is the recommended collation for SharePoint Content Databases
    
    when click on “customize” button, make sure you select the following option:
     
17. After finish the Service accounts and Collation settings, click on “Next” button.
    
18. In “Database Engine Configuration” page, In “Account Provisioning” Tab, Select “Mixed Mode” to provide alternative access to Database engine using default “SA” account (SQL Authentication), enter the password for “SA” account.
    Add the Security Group “SQL_Admins_Grp” as SQL server administrators, to set SQL administration permissions for this group and all members of this group
     
19. In “Database Engine Configuration” page, In “Data Directories” Tab, make sure the directories path is not a system drive.
     
20. In “Database Engine Configuration” page, In “FILESTREAM” Tab, enable FILESTREAM feature for advance and future needs like enabling Remote Blob Storage with FILESTREAM. You can skip this feature if there is no plan for FILESTREAM.
     
21. Click on “Next” button, to move for next page
    
22. In “Analysis Services Configuration”, In ”Account Provisioning” Tab, Add “SQL_Admins_Grp” security group to have administration permissions for Analysis services
     
23. In “Analysis Services Configuration”, In “Data Directories” Tab, make sure the directories path is not a system drive
     
24. Click “Next” button to move for next page.
    
25. In “Reporting Services Configuration” page, select third option, to install reporting service, but you will configure it later when needed. Click on “Next” button to move for next page.
     
26. In “Error Reporting” page, select the check box to send Windows and SQL Error Reports to Microsoft to participate in enhancements and fixing errors for next SQL server updates or next releases. Click “Next” button to move for next page.
     
27. In “Installation Configuration Rules” page, make sure all check list are passed or skipped (green check), click “Next” button to continue
     
28. In “Ready to Install” page, check your selected configuration, then click on “Install” button to start SQL installation process.
     
29. Installation Progress page will open and show you the installation progress
     
30. After successful installation, the “Complete” page will open to confirm your installation. Click on “Close” button to close installation wizard
    

Note: run Windows Updates to get latest updates after successful installation of SQL server 2008 R2.

---

Post-Installation Steps and Checklist

The following is post installation and tuning steps for SQL server

1. Set max degree of parallelism (MAXDOP) to 1 for instances of SQL (SP will do this when SP is installed). Number of processes for each SQL statement.
   
   
2. Memory: You can set the max memory each SQL instance can use. If the machine is dedicate to only provide SQL for SharePoint, the max setting is total memory minus 4GB for the OS.
   
3. Maximum size for a DB recommended by MS is 200GB, however it can support up to 16TB
   
4. Never use Simple recovery mode for production SharePoint SQL databases, always use Full recovery mode
   
5. The SharePoint Config database is what comprises the actual SharePoint farm configuration information like IIS Settings, Farm solutions, Topology info, etc. This database is read intensive and can range between 0-5GB size
   
6. Tempdb best practices:
   
   1. Number of tempdb files should be 1/4th or 1/2 the number of processor cores on SQL server – This is a practice that needs real data from your existing system to see if tempdb is creating a bottleneck and whether or not increasing the number of db files will increase performance.
      
   2. Tempdb size should be roughly 10% of total content DBs
      
   3. Autogrowth setting should be in MB and not a percentage
      
      
   4. Tempdb should use Simple Recovery Model
      
      
   5. Tempdb should be placed on different drive than content databases file
      
   6. Tempdb: The default is 1, you need more than this depending on how many CPU cores are on your database server. 1 option is set the number of TempDB’s to the same as the number of CPUI cores (1-to-1). Some folks recommend the number of tempDB’s should be 1 less than the number of cpu cores, other folks go for 1 TempDB per 2 CPU cores.
7. Model DB best practices:
   1. Work with SharePoint Admin to identify default initialize size for model db and its log file
      
   2. Don’t modify DB Collation after install
      
   3. Set the autogrowth in MB and not in percentage (Note: Databases created by SharePoint does not copy autogrowth settings from Model DB)
      
   4. Increase initial size and autogrowth settings – fix growth sizes. I would start with 100MB for the mdf and 50MB for the ldf for initial sizes.
      
   5. Use Full recovery model on the Model system database – Simple prevents large log files
       

 

I hope this is Useful for You