Complete SCCM 2012 SQL Install Guide

This blog post has been updated. Please refer to the new SCCM Current Branch Installation Guide.

In the first part of this SCCM 2012 and SCCM 1511 blog series, we planned our hierarchy and prepared our Server and Active Directory.

In part 2, we will install and configure SQL before installing SCCM 2012 R2 or SCCM 1511.

Click the following link to see all supported SQL versions. For our post, we will install SQL 2012 SP2 locally on the same server where the Primary Site will be installed.

SCCM 2012 SQL Install Guide

• Execute Setup.exe from the SQL installation media and select New Installation.

• Review and Click Next.

• Select Enter Product Key and skip the proposed updates. We will install CU4, later.

• Select SQL Server Feature Installation

• Select the Database Engine, Reporting Services and Management Tools features and specify the SQL installation directory. This is the directory for the program files and shared features.

• Select the Default instance and ensure your instance is created on the SQL Volume.

• Review and click Next.

• Set all services to run as the SQL domain account that you created previously and set the services start-up type to Automatic.

• On the Collation tab, set the Database Engine to use SQL_Latin1_General_CP1_CI_AS

• In the Server Configuration tab, set the authentication mode to Windows Authentication and in the SQL Server Administrators, add your SCCM Admins group.

• In the Data Directories tab, set your drive letters correctly for your SQL databases, Logs, TempDB, and backup.

• In Reporting Service Configuration, Select Install only.

• In Error Reporting, uncheck the box and click Next.

• Click Next

• Click Install

• Complete the installation by clicking Close.

SPN Creation

When you configure SQL Server to use the local system account, a Service Principal Name (SPN) for the account is automatically created in Active Directory Domain Services. When the local system account is unused, you must manually register the SPN for the SQL Server service account.

Since we are using a domain account, we must run the Setspn tool on a computer in the SQL Server domain. It must use Domain Administrator credentials to run.

Run both commands to create the SPN, Change the server name and account name in each commands.

• setspn -A MSSQLSvc/yourservername:1433 yourdomain\SQLSA
• setspn -A MSSQLSvc/yourserver.fullfqdn.com:1433 yourdomain\SQLSA

To verify the domain user SPN is correctly registered, use the Setspn -L command

• setspn –L yourdomain\SQLSA

Apply SQL 2012 STD SP2 CU4

The latest SQL Cumulative Update is CU4, which is the current version. We will install it to have an updated SQL Installation.

• Download and execute SQL 2012 SP2 cumulative update 4
• Accept the licence terms and click Next

• Leave default values, click Next.

• Wait for Check File in Use and click Next.

• Click Close when the process is completed.

SQL Configuration

SCCM setup verifies that SQL Server reserves a minimum of 8 GB of memory for the primary site. We’ll set the SQL Server memory limits to 8GB-12GB (80% of available RAM) to avoid the warning.

• Open SQL Server Management Studio
• Right-click the top SQL Server instance node
• Select Properties
• In the Memory tab, define a limit for the minimum and maximum server memory. Configure and limit the memory to 80% of your server’s available RAM. In my case, I have 16GB available.
  • Minimum 8192
  • Maximum 12288

Database Sizing

We always recommend creating the SCCM database before the setup. Although this is not mandatory, SCCM will create the database for you during setup but not optimally. We strongly recommend watching the Top Ten Lessons Learned in Managing SQL session from MMS2013, which covers it all.

We followed MVP Kent Agerlund’s guide to estimate my DB sizing needs. Visit his blog post and download the provided Excel file. Input your values in the blue cells and keep them for the next part. In the next section, we’ll create the DB using those values in a script.

For this blog post, We’ve created a Database for 2000 clients, 2 processors, 2 cores and 16GB RAM.

Create Database

To create the database, you can use Kent’s script and input your values (as returned previously in the Excel file) OR  use the following simple one:

The Name value will become your Site Code during the SCCM installation. Be sure to select a unique Site Code.

• **Replace all XXX value with your 3 character Site Code**
• **Change the values of  the Filename, Size, MaxSize and FileGrowth. Change the location of the file to your SQL and Logs drives**

[pastacode lang=”sql” manual=”USE%20master%0ACREATE%20DATABASE%20CM_XXX%0AON%0A(%20NAME%20%3D%20CM_XXX_1%2CFILENAME%20%3D%20’E%3A%5CSCCMDB%5CCM_XXX_1.mdf’%2CSIZE%20%3D%207560%2C%20MAXSIZE%20%3D%20Unlimited%2C%20FILEGROWTH%20%3D%202495)%0ALOG%20ON%0A(%20NAME%20%3D%20XXX_log%2C%20FILENAME%20%3D%20’G%3A%5CSCCMLogs%5CCM_XXX.ldf’%2C%20SIZE%20%3D%204990%2C%20MAXSIZE%20%3D%204990%2C%20FILEGROWTH%20%3D%20512)%0AALTER%20DATABASE%20CM_XXX%0AADD%20FILE%20(%20NAME%20%3D%20CM_XXX_2%2C%20FILENAME%20%3D%20’E%3A%5CSCCMDB%5CCM_XXX_2.mdf’%2C%20SIZE%20%3D%207560%2C%20MAXSIZE%20%3D%20Unlimited%2C%20FILEGROWTH%20%3D%202495)” message=”” highlight=”” provider=”manual”/]

Review the Site Database properties

• Open SQL Management Studio
• Right-click your DB, Select Properties
• In the General tab, verify that the SQL collation name is SQL_Latin1_General_CP1_CI_AS

• In the File tab, verify that your database files have been created with the script value.
• Verify that the file is located on your SQL Volume
• Change the database owner to SA. By default, the owner will be the account that created the database.

If you find out that you made an error, you can safely delete the Database using SQL Management Studio and rerun the script.

• Open SQL Management Studio
• Right-click your DB, Select Delete

TempDB sizing

Run the following scripts to size the TempDB. (using the value returned by the Excel file)

**Change the values of Filename, Size, MaxSize and FileGrowth. Change the location of the file to your TempDB drives**

[pastacode lang=”markup” manual=”use%20master%0Ago%0Aalter%20database%20tempdb%20modify%20file%20(name%3D’tempdev’%2C%20filename%3D’F%3A%5CSCCMTempDB%5CtempDB.MDF’%2C%20SIZE%3D%204536%2C%20MAXSIZE%20%3D%20Unlimited%2C%20FILEGROWTH%20%3D%20512)%0Ago%0Aalter%20database%20tempdb%20modify%20file%20(name%3D’templog’%2C%20filename%3D’G%3A%5CSCCMLogs%5Ctemplog.LDF’%2C%20SIZE%3D%202268%2C%20MAXSIZE%20%3D%20Unlimited%2C%20FILEGROWTH%20%3D%20512)%0Ago” message=”” highlight=”” provider=”manual”/]

Review the TempDB Properties

• Open  SQL Management Studio
• In the System Database, Right-click the TempDB, select Properties
• In the File Tab, verify that your database files have been created with the script value
• Ensure that the TempDB and log are on the TempDB volume

SQL Communications

To ensure proper SQL communication, verify that settings are set accordingly in the SQL Network configuration.

• Open SQL Server Configuration Manager
• Go to SQL Server Network Configuration / Protocols for MSSQLServer
• On the Right Pane, right-click TCP/IP and select Properties
• In the Protocol tab
  • Enable: YES
  • Listen All: NO

• In the IP Addresses tab
• IP1 (which should have your Server IP)
  • Active: YES
  • Enabled: YES
• All other IPs and IP ALL
  • Active: YES
  • Enabled: NO
  • TCP Dynamic Ports: Blank value
  • TCP Port: 1433

Once the modification has been made, restart the SQL Server Service.

The server is now ready for SCCM installation.

Overview | SCCM 2012 R2 Step-by-Step Installation Guide

Next Part | How to install SCCM 2012 R2

sccm 2012 sql install guide