Part 10 - How to install SQL 2012 before installing SCCM 2012 R2

In this guide we will be installing and configuring SQL before installing SCCM 2012 R2.

SQL Installation,

Execute Setup.exe from the SQL installation media, select "New Installation".

Review and click Next.

Accept the License terms. Next.

Review and click Next.

Select "SQL Server Feature Installation" Next.

Select following:

• Database Engine Services
• Reporting Services - Native
• Management Tools -Basic
• Management Tools - Complete

Click Next.

Review and click Next.

Select "Default instance" and Next.

Review and click Next.

Set all services to run as the SQL domain account. Set the services to startup type: Automatic

• SCCM-SQLService
• SCCM-SQLReporting

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

Set the authentication mode to "Windows Authentication" and in the SQL Server Administrator add your SCCM-Admins group.

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

Letter	Content	Size	Allocation unit size
C:\	Windows	120 GB	Default (4K)
S:\	SCCM	200 GB	Default (4K)
D:\	SQL Database	40 GB	64K
T:\	SQL TempDB	40 GB	64K
L:\	SQL Transaction Logs SQL TempDB Logs	40 GB	64K
W:\	WSUS	40 GB	Default (4K)

In "Reporting Service Configuration", Select Install only

Uncheck "Error Reporting", Next.

Review and Next.

Click Install.

Install done, Review, 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 not in use, 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 that resides in the domain of the SQL Server. 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 CU12

At the time of this writing, the latest SQL Cumulative Update is CU12. We will install it in order to have a updated SQL Installation.

Download and execute SQL 2012 SP2 cumulative update 12

https://sqlserverbuilds.blogspot.dk/ - CU15

Accept the licence terms and click Next

Leave default values, click Next.

Wait for Check File in Use and click Next.

Click Update to begin update.

When the process is completed, click Close.

SQL Communications

To ensure proper SQL communication, verify that settings are set accordingly in 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
• IP2 (which should have your Server IP)
• Active : YES
• Enabled : YES
• All other IP and IP ALL
• Active : YES
• Enabled : NO
• TCP Dynamic Ports : Blank value
• TCP Port : 1433

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

SQL Configuration

SCCM setup verifies that SQL Server reserves a minimum of 8 GB of memory for the primary site. 

• Open SQL Server Management Studio
• Right click the top SQL Server instance node
• Select Properties
• In the Memory tab define a limit for the minium and maxium server memory. A finger rule is normal to give 80% of available RAM to the SQL. The rest is for the System.

My laptop only have 8 GB ram så i adjust til down a little to 4096 MB. It will trigger a warning under the SCCM installation.

Database Sizing

We follow the guide made by MVP, Kent Agerlund to estimate the DB sizing need. Visit his blog post and download the provided Excel file. Input your values in the blue cells and keep it for the next part. We’ll create the DB using those values using a script in the next section

For this tiny setup i use this simple script to create my DB for the SCCM.

Recommend to watch the The Top Ten Lessons Learned in Managing SQL

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 one which is really simple:

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**

Make sure you have thise folder created before you run the script in SQL Server Management Studio

• D:\SCCMDB
• L:\SCCMLogs
• L:\TempDBLogs
• T:\TempDB

Simple SQL script:

USE master
CREATE DATABASE CM_DEV
ON
( NAME = CM_PRI_1,FILENAME = 'D:\SCCMDB\CM_DEV_1.mdf',SIZE = 5620, MAXSIZE = Unlimited, FILEGROWTH = 1855)
LOG ON
( NAME = PRI_log, FILENAME = 'L:\SCCMLogs\CM_DEV.ldf', SIZE = 1855, MAXSIZE = 1855, FILEGROWTH = 512)
ALTER DATABASE CM_DEV
ADD FILE ( NAME = CM_PRI_2, FILENAME = 'D:\SCCMDB\CM_DEV_2.mdf', SIZE = 5620, MAXSIZE = Unlimited, FILEGROWTH = 1855)

USE master
go
alter database tempdb modify file (name='tempdev', filename='T:\TempDB\tempDB.MDF', SIZE= 4536, MAXSIZE = Unlimited, FILEGROWTH = 512)
go
alter database tempdb modify file (name='templog', filename='L:\TempDBLogs\templog.LDF', SIZE= 2268, MAXSIZE = Unlimited, FILEGROWTH = 512)
go

Review the TempDB properties

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

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 has 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 which 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

The server is now ready for SCCM installation.

Back to index