The PDF file is a 162 pages document that contains all informations to install and configure SCCM Current Branch. Use our products page or use the button below to download it . |
 | 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, 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, 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 Default instance and ensure that 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, 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 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 CU4
At the time of this writing, the latest SQL Cumulative Update is CU4. We will install it in order to have a 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. To avoid, the warning, we’ll set the SQL Server memory limits to 8GB-12GB (80% of available RAM).
- 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 available RAM. In my case I have 16GB available.
- Minimum 8192
- Maximum 12288
Database Sizing
We always recommend to create the SCCM database before the setup. This is not mandatory, SCCM will create the database for you during setup but will not create it the optimal way. We strongly recommend to watch the The Top Ten Lessons Learned in Managing SQL session from MMS2013 which cover it all.
We follow the guide made by MVP, Kent Agerlund to estimate my 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 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 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**
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
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 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
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
- IP1 (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.
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
47 Comments on “Complete SCCM 2012 SQL Install Guide”
Pingback: Complete SCCM Installation Guide and Configuration
This guy literally copy and pasted your whole article and made it his.
http://damcuvelier.over-blog.net/2018/02/sccm-complete-sccm-2012-sql-install-guide.html
Somehow I have four tempdb files.. tempdev, temp2, temp3, and temp4.. how did that happen? The additional tempdbs have a file size of 8.
Hi,
We have defined SCCM architecture, we need to modify the SQL Server with Default instance to named instance. Can we change the sql server name with named instance. If possible please provide me the steps how to modify the sql server name in SCCM Configuration
Hello,
awesome information, thank you for sharing.
Anyway, the script to generate the database is not more available … 🙁
KR,
Pablo
From your guide: “Set all services to run as the SQL domain account that you created previously and set the services start up type to Automatic”.
The screenshot doesn’t show which account (that has been created before) is used for which service. What is the “SQL domain account”? Following the guide, would this be correct?
SQL Server Agent = SCCM-SQLService
SQL Server Database Engine = SCCM-SQLService
SQL Server Reporting Services = SCCM-SQLReporting
SQL Server Browser = Can’t be changed anyway
Be great if you could update this for SQL 2016 and give a full breakdown on the drives that should be configured for best practices with SQL.
Thanks for the guide. One thing, the drives here for the SQL install Data Directories don’t match the drivers you stated in part 1:
C:\ Windows 100GB
D:\ SCCM 200GB
E:\ SQL Database (64K) 40GB
F:\ SQL TempDB (64K) 40GB
G:\ SQL Transaction Logs (64K) / SQL TempDB Logs 40GB
What is the point of the second MDF? ie E:\SCCMDB\CM_XXX_2.mdf
In SQL 2016 if you use your instructions on : “On the Right Pane, right-click TCP/IP and select Properties
In the Protocol tab
Enable: YES
Listen All : NO ”
This does not work if you set to NO.. It keeps bringing an error message of “Setup is unable to connect to SQL Server with the connection information provided” You also don’t mention that we need to enable “Named Pipes” in the protocol. Anyway, thanks for the write up.
Exactly! Thank you Gerry.
Hi Benoit,
I’m having trouble applying the correct MAXSIZE values in DB creation:
Msg 5103, Level 16, State 1, Line 1
MAXSIZE cannot be less than SIZE for file ‘[name]_log’.
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 911, Level 16, State 1, Line 4
Database ‘CM_[name]’ does not exist. Make sure that the name is entered correctly.
# of clients is 5000, # processors/Cores is 4/4, memory is 16383. I manually created subfolders in the database and log drives (\SCCMDB and \SCCMLogs). Can you give me any advice on what to use for the MAXSIZE values?
Great stuff, I’v been looking for this kind of details step by step for a while – I purchased the pdf for permanent record. One thing i am having an issue with is that when running the second tempdb query in sql – it errors not like the first character ‘<'. not being a guru on any of this is there something I need to change in the script? cheers
Benoit – can you please clarify the disk drives?
In article 1 you set D for SCCM, E for SQL database, F tempdb and G for logs – fine
When installing SQL you say D:\SQL for the Data Root directory – that is the SCCM drive – is that correct ?Presumably you want ONLY the SCCM/SQL database on the E drive
Did you ever get an answer on this one? I’m wondering the same thing….
He had to have meant E:\ drive since E:\ is listed nowhere on the installation document.
Whatever, cant harm. going to do it anyway on E:\
Gosh this is a great article. Thank you so much. I was using CBT Nuggets course on SCCM and wondered why my report builder and SQL wasn’t set up properly; this is really thorough and still applies to SQL 2014 and SCCM 2012R2SP2.
Thanks Kate.
I am trying to set up a new SCCM 1511 server – from scratch. I’ve followed your guide (excellent!), but I installed SQL 2014 and now I’m stuck at the script to do the tempDB sizing. I keep getting a syntax error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘<'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '<'.
I copied the script exactly and changed the paths to be correct for my database. But this script will not run. Does something need to be different for this to work in SQL 2014?
Never mind, I figured it out – had to take all of the “junk” (<span class=, etc) out of the lines.
Thanks!
I followed your guide step by step, I keep getting SQL port errors no matter what I do… I have thoroughly followed your instructions on setting up SQL however I get an SQL Network Errors as if I haven’t opened the SQL ports. I have tried everything and I am getting pretty frustrated with uninstalling SQL and reinstalling. Please any help would be much appreciated.
Hello,
It’s sad to admit, but I’m stuck on;
“Set all services to run as the SQL domain account that you created previously and set the services start up type to Automatic”
What is the “SQL domain account?” I created the accounts mentioned in the “Requirements and recommendations before installing SCCM 2012 R2 / Primary Site server prerequisites.”
Thank you.
I like them, especially BFF and Free Love <3 but also Get Wet and Undercover Vixen lol. Man Chaser is kinda shimmery for my taste
Avand in vedere ca la mine ziua mea de nastere , Craciunul si ziua de nume sunt foarte apropiate , cadourile sunt sunt date cam toate odata :)Pai de la prieteni am primit cateva carti grozave, de la parinti am primit bani 🙂 iar de la sora mea, cumnatul meu si sora cumnatului meu ( stiu, suntem o familie mare 😀 ) am primit haine si dulciuri 🙂
Yes thanks for the guide but I have question regarding running the scripts. Where do I run these scripts from? Do these scripts need to be run each time the SQL server reboots? I am referring to the script when you are creating your db… I am kind of a newb to SQL and SCCM and maybe Systems Administration lol.. deep networking background… your help is much appreciated. I have been banging my head against a wall since yesterday trying to get a stand-alone install of sql server 2008 to run in conjunction with SCCM… this guide has been the most helpful thus far!!!
You run the script in SQL Management Studio. Connect to your instance, create a new query and paste the code. You need to run this script just once to create your DB.
Ok cool, thank you sir. Hey, so I bought the guide. This guide is very much well worth it. It makes for a comprehensive yet simple read. I work at LG Electronics as Datacenter Sys Admin and this really helped me streamline my Install & Config process. I do have one more question, I assume the DP needs to be on its own server? When I was working at eBay as Desktop Support, I do not remember a separate server in our lab to support SCCM we just cabled the machines to a Dell Switch that connected to the SCCM server for PXE boot. Is there any way that Dell Switch was the DP? I guess I am little confused and trying to get a similar deployment to the one we had at eBay. I am a complete newb when it comes to SCCM so I apologize in advance for asking silly questions. And thank you for taking the time to help me!!! Much appreciated.
Depending on the size of your environment you can run the DP on the same box as your sccm and db. In my environment for example, I have 1 vm serving as the sccm, db, and dp. I also have about 2300 devices supported. I am also dealing with 6 buildings with fiber links.
In a larger environment or branch office topology you might consider adding more dp’s and possibly putting up a separate db server. I can’t see a switch serving as the deployment point.
Thanks for your quick response Brian!!! Ok, so since we are a subsidiary that was bought out by LG we actually only have about 500 users (probably 1000 devices tops) roughly so I could host the DP on the same box as SCCM, but lets say the SCCM server and the DP that is hosted on it is in the datacenter (downstairs) I don’t want to have to go image 10-20 laptops/dekstops down in the datacenter, if i want to image the machines at my desk, should I install and create the DP there? Or do I just set up a switch thats on the same subnet as the sccm server? Of course making sure that the site server is has PXE and Multicast enabled.
My preference, have pix available anywhere on my network. I would configure the sccm server in your datacenter/vm environment, and then configure ip helper addresses on your core switch(s). If you have a look at your current switch configs I would be you have something in place to provide dhcp for each vlan. Personally I would add the sccm sever as an ip helper address at that level.
For example, in my environment I have a core switch per building which has vlans defined for the device subnets. So subnet 10.4.x.x would have ip helper addresses defined for each of my dhcp servers and any pxe server such as sccm, zenworks…
With that in place it should be a piece of cake. I strongly suggest putting a password on the pxe tab of your dp so you don’t have people imaging themselves by mistake.
To test your pxe setup out you would press f12 on a pc at boot time and if all goes well you will receive a dhcp address and then see the f12 prompt to continue.
A little extra advise:
In my environment I have unknown computer support enabled on this tab. I do not deploy task sequences to my collections out of fear of the unexpected. I simply delete the computer object from sccm and then it is treated like an unknown machine. My collections are then configured to query AD and place computers where they should go. Everyone has their own way of dealing with this…
Ok that makes sense… awesome!!! thank you Brian and Benoit for your help!!! Your expertise is much appreciated on this end!!!
Why not install SQL Server 2014 for 1511?
Yes you can, it’s supported. The SQL blog post has been around for a while and was not updated. Our paid installation guide has been updated with SQL 2014
Can you fix the TempDB sizing SQL script …
I just test it and it ran fine. Make sure that you change the drive letter to fit your environement. Also make sure that the ‘ and ” are well formatted. Workpress can convert those when doing cut/paste.
So, to confirm you intend for the database creation script to include HTML elements? This is what I am seeing.
use master
go
alter database tempdb modify file (name=’tempdev’, filename=’F:\SCCMTempDB\tempDB.MDF’, SIZE= 4536, MAXSIZE = Unlimited, FILEGROWTH = 512)
go
alter database tempdb modify file (name=’templog’, filename=’G:\SCCMLogs\templog.LDF’, SIZE= 2268, MAXSIZE = Unlimited, FILEGROWTH = 512)
go
Hah, the comment parsed the tags for me. I guess that works.
Lo ha dicho Selma antes que yo. ¿Por qué no cambiar el dinero que se despilfarra en luces durante las fiestas de Navidad o en la Feria de Sevilla, a un objetivo más de justicia? Por lo que nos dice Manuel Ruda, la existencia del poblado es una auténtica vergüenza para Sevilla, su alcalde y sus ciudadanos.¡Felices Reyes Rafa!
Eagerly waiting for the book. And not the bootlegged one. Want the real issue autographed by the both of you so when the book gets an Oscar I can auction it in London for zillions of dollars!Good luck!
Hello,
I have two questions:
1. In the “Instance configuration section” you wrote “Select Default instance and ensure that your instance is created on the SQL Volume”.
What is the SQL Volumen? C:\ or E:\?
2. According to the previous part, section “Disk”, drive D:\ is dedicated for SCCM.
So is it correct, that in the “Database Engine Configuration”, tab “Data Directories”, the “Data Root Directory” also “d:\SQL” is?
An addition note: drive letter H:\ is not noted in the “Disk” section in the previous part.
Daniel
What is
This is a wonderful guide for the setup and config of SCCM for an intermediate Systems Administrator that has no previous experience with SCCM. I don’t say ‘beginner’ only because if you are looking just to blindly follow steps you see on the screen, you can’t. You need to pay attention and READ what he says and fill in the necessary info where needed and make decisions/changes based on your environment or server setup.
My only comment so far is that I got to this page through the SCCM 1511 setup guide/blog. But your next part and overview links are for the SCCM 2012 R2 guide. I know a small thing but you may want to ‘fork’ this page for each guide or update the link to point to your most recent install guide.
https://systemcenterdudes.com/step-by-step-sccm-1511-installation
Pingback: SCCM Deploy – Post 1. Setting up VMware AD and SCCM 1511
Pingback: SCCM Deploy – Post 4. Setup SCCM 2012 1511
I’m working on a side-by-side migration from SCCM 2012 sp1 to 1511. My original db was a flat single partition configuration. What happens if I setup my new db per your suggestions splitting the files out? Should my data still migrate over or should I maintain the original configuration?
That’s a good question. I’m not an SQL guru, I can’t answer your question unfortunately.
I’m pretty sure you can change the configuration of the SQL server once your migration is completed. In SQL Management studio, you can change the path of the files but I haven’t test that.
I’m pretty sure you can have your answer on any good SQL forum out there.
http://thomaslarock.com/2015/02/changing-default-database-file-locations-in-sql-server/
I love this deployment guide. I think this is the first guide you can use to deploy ConfigMgr in the production environment.
Thanks, your comment is appreciated !