Installing SQL Server 2012 for Configuration Manager 2012 R2

In this post we will be looking at the steps for Installing SQL Server 2012 for Configuration Manager 2012 R2. In this deployment series of Configuration Manager 2012 R2 we started with system center 2012 R2 Configuration Manager system requirements which tells more about the new features of SCCM 2012 R2 and system requirements for Configuration Manager 2012 R2, in the next post we saw the steps for installing prerequisites for SCCM 2012 R2. After installing prerequisites the next step is to install SQL server. Most of the users have a question on where should the SQL server be installed, should it be installed locally on the server where SCCM is installed or on a remote server ?. The answer is you can install it on local server or host the database on remote server. I would prefer to install SQL locally because this it requires less administrative overhead, the license for SQL is included with System Center so no additional license needs to be purchased and with local SQL you can achieve better performance than remote. In this post we will be installing SQL server 2012 with SP1 on windows server 2012 R2, if you have only SQL server 2012 iso copy, you can download the SQL server 2012 service pack 1 and install it.

For System Center 2012 R2 Configuration Manager you can install the SQL server with combinations :-

1) Install SQL Server 2012 + Min Cumulative Update 2

2) Install SQL Server 2012 + Service Pack 1 [no cumulative update required]

3) Install SQL Server 2012 with Service Pack 1 [no cumulative update required]

If you are planning to use SQL server other than 2012 then click here to see the SQL Server Requirements for Configuration Manager 2012 R2.

Here is a video tutorial that shows the steps to install SQL server for SCCM.

Installing SQL Server 2012 for Configuration Manager 2012 R2

So lets look at the steps to install SQL Server 2012 with SP1 (x64 Bit). I have mounted the DVD on to the Windows Server 2012 R2, open the SQL server folder, run the setup as administrator. Click on Installation and click on New SQL server standalone installation.

Installing SQL Server 2012 for Configuration Manager 2012 R2

Click OK once the Setup support rules are run and verified.

Installing SQL Server 2012 for Configuration Manager 2012 R2

Check the box for I accept the license terms and click on Next.

Installing SQL Server 2012 for Configuration Manager 2012 R2

After the Setup Support Rules are completed click on Next.

Installing SQL Server 2012 for Configuration Manager 2012 R2

Select the Setup Role as SQL Server Feature Installation and click Next.

Installing SQL Server 2012 for Configuration Manager 2012 R2

On the Feature Selection window, select Database Engine Services, Reporting Services-Native and Management Tools – Complete.

 

Note – The features that we have selected are the ones which are required for deploying Configuration Manager 2012 R2. However you can select all features and install them if you want to.

 

Installing SQL Server 2012 for Configuration Manager 2012 R2

For Instance Configuration click on Default Instance, the instance ID MSSQLSERVER would be created.

Note – If you are creating a Named Instance, then during the SCCM 2012 R2 setup you might come across this error “The specified SQL Server instance is not configured to use dynamic ports” unless you have not configured dynamic ports for SQL server instance. To fix this error launch the SQL Server Configuration Manager, expand SQL Server Network Configuration. Click on Protocols for Instance (Instance is your Named Instance). On the right side pane, right click on the TCP/IP and click on Properties. Click on IP Addresses tab and scroll down to the bottom and under the IPALL, set TCP Port value to 1433. Click on Apply and after this change you must restart SQL Server Service.

 

Installing SQL Server 2012 for Configuration Manager 2012 R2

For Service Accounts, Microsoft recommends you to use domain service accounts and not the local system accounts. We will be using the account named sccmadmin which is a member of domain admins group. This account will be also used for installing and managing Configuration Manager 2012 R2. You can also create a separate user accounts for each of the SQL server services.
Specify the account name and password and click on Next.

Important – During the SQL install, the collation should be SQL_Latin1_General_CP1_CI_AS.

Installing SQL Server 2012 for Configuration Manager 2012 R2

Click Add Current User, this will add the current user to SQL server administrators. Choose the Authentication Mode as Windows authentication mode. Click Next.

Installing SQL Server 2012 for Configuration Manager 2012 R2

Choose Reporting Services Native Mode and click on Install and Configure. Click on Next.

Installing SQL Server 2012 for Configuration Manager 2012 R2

Click on Install to start the installation.

Installing SQL Server 2012 for Configuration Manager 2012 R2

We have installed SQL server 2012 with SP1 on Windows Server 2012 R2 Datacenter Edition. Click on Close.

Installing SQL Server 2012 for Configuration Manager 2012 R2

During the installation of Configuration Manager 2012 R2, the configuration manager checks if the SQL server memory limit is limited. If the memory is not limited, then a warning is displayed. To avoid that lets limit our server with a memory limit. Launch the SQL Server Management Studio. Login to the server and right click the Server and click on Properties. Click on Memory, set Minimum server memory value to 8192 MB (The minimum value can be less that 8192 MB) and set Maximum server memory as 8192 MB. Click on OK and close the SQL management studio.

Installing SQL Server 2012 for Configuration Manager 2012 R2

Related Posts

68
Leave a Reply

avatar
35 Comment threads
33 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
newest oldest most voted
Suvankar
Guest
Suvankar

I am keep getting this error, though i am logged in as sccmadmin; in hom com domain in my vmware environment. where as i can see sccmadmin & the current password i have logged in in the sccm server as well. please help. I am learning via your blogs. crTITLE: SQL Server 2012 Setup —————————— There are validation errors on this page. Click OK to close this dialog box. Review errors at the bottom of the setup page, then provide valid parameters or click Help for more information. —————————— BUTTONS: OK —————————— [Error Message] The SQL Server service account login… Read more »

Amit
Guest
Amit

Hello Prajwal,
I am learning SCCM to reading from this site.
i am confused that as you mentioned Prajawalsccmadmin so kindly let me know here what is Prajwal. Is it ad server or SCCM server as sccmadmin is domain account. I am new in SCCM.

Malavan
Guest
Malavan

Prajwalsccmadmin
Prajwal – Domain name & sccmadmin – domain admin account

somvir
Guest
somvir

Hi Prajwal, I am trying to set up a lab environment on VMware workstation and facing some issue with the service account, created for the configuration installation. Here are the scenarios: First I created the service account in AD and then assigned the domain admin permission to it and then login with this account on my SCCM server, the account works fine a for a while and then after sometimes when I try to login back it says that username or password incorrect, I have recreated another account and getting this issue with every account. And if I try to… Read more »

sateshb
Guest
sateshb

Thank you for the tutorial, not sure if I missed a setup but was running into an issue connecting to the SQL database. After some troubleshooting and research i found the solution, referencing an article with the missing step.

Enable TCP/IP in SQL Server Configuration
https://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

suhas f
Guest
suhas f

i have installed sql server 14 on server 2012 r2 stand edition
installation was fine with no error, but when i try to connect to DB engine it is giving me error
microsoft sql server error 18456
but same credentials works for reporting services

suhas f
Guest
suhas f

got it now, i have already logged in with domain admin account but it was not working.
then i had tried to open the SQL studio by making it open with another account and used domain\admin account with the password .
now it has gone though.
just a query, should i enable SA account (sql ) ? as a backup so that i can login if admin fails?

Harjeet Matharu
Guest
Harjeet Matharu

Hi Parjwal, Thanks for sharing this useful guide for SCCM learning. I am following along to install SQL Server on my VM running Windows Server 2008 R2. I am installing SQL Server 2008 but I do not see ‘Reporting Services’ under features installation. Also I do not see alll the Service Accounts name which appear here. I doubt If I am installing the correct version of SQL Server. Could you please help?

Gaz Jay
Guest
Gaz Jay

There’s a step missing somewhere. SQL needs some static TCP port and a service broker. Not sure how to set them yet.

witri
Guest
witri

Hello Prajwal.
i’m new in sccm 2012. i want to ask you, what is the first step i will do, to install SCCM 2012.
Is there, i install SQL server first??
And then, how about Windows Assessment and Deployment kit for Windows 8.1?? I used win server 2012 and the client used window 7. how about this??

Thank you

Nick
Guest
Nick

Hi Prajwal Desai,

Again I have a problem and I don’t know what do to do fix it. Can you help me with this?
EDIT: Maybe I need to search before I post a message here because I found the problem, sorry for the trouble.

Thank you very much.

Emanuel
Guest
Emanuel

Im sure you have tried to install SQL before, so there are old files from last installation that has to be deleted. go to C:ProgramFiles and C:ProgramFiles(x86), and then delete old files from the folder Microsoft SQL Server

Nick
Guest
Nick

Good day Prajwal,

I have a question about the first link “Download Microsoft SQL server 2012 with SP1”.
Is this supposed to be SQL 2012 Express? because I can’t find any SQL server 2012 downloads when I go to that link.

EDIT: I have another question, the user sccmadmin was made without showing in the guides? Or did I look over it?
PS: I’m inexperienced with all of this.

shakkeer c
Guest
shakkeer c

Hi Prajwal , Excellent article , one question … I am going to install SCCM_2012 along with SCEP. I was reading some article and it says that ” Each license SCCM 2012 R2 license comes with a SQL standard 2014 edition server license included . It’s classified as SQL Technology in the universal license terms and may be used only to support SCCM . If they already own the license for SCCM they just download SQL Standard 2014 edition from the VLSC in the downloads section. However If they require Enterprise Edition they would license per core separately” ….In this… Read more »

faisal
Guest
faisal

HI ,I am stuck end of the SCCM install . its stuck to create the database. I have been trying for last one week. i have two server. my active directory Domain name is :fslmetal. username sccm, another server name is :sccmmachine. do i need to add any other user login in sql server?

faisal
Guest
faisal

Hi Everybody,

I’m learning to work with SCCM 2012, But I can’t even install it correctly. And the end of the install I get the error : “Setup has encountered fatal errors during database initialization. Check the log files for details.” my log file i upload in here. plz can anyone give me soln?

http://www.filedropper.com/configmgrsetup1_1

Melon Le Immorale
Guest
Melon Le Immorale

Thanks for the guide, it has been helping me alot.

I’m downloading your PDFs for future use and I can see that you have forgotten to add the “Important – During the SQL install, the collation should be SQL_Latin1_General_CP1_CI_AS.” to the one on installing SQL.

Paddy
Guest
Paddy

Hi, Love the articles, they’ve been really helpful so far. I have a problem in that I am trying to install my SQL on to a separate server, and when I try to instal Config Manager I get the following error at the Database Information stage: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA), but I have checked the RPC services on both the Manager server and the database server and they are running. Any advice on this issue would be a huge help.

Thanks in advance.

Paddy

James Vincent
Member
James Vincent

Hi Paddy, post the question here along with screenshots – http://prajwaldesai.com/community/forums/system-center-configuration-manager.4/

Zach Albrecht
Guest
Zach Albrecht

Stating SCCM and SQL will perform better on one shared windows installation is truly circumstantial to the size of the environment being managed and the connectivity between hosts.

This does change with SC 2016 Suite with AlwaysOn Support being integrated but that is another topic 🙂

Robin Pei
Guest
Robin Pei

the collation should revised

James Vincent
Member
James Vincent

SQL Server 2014 is supported as a site database, it must be an upgrade, not a new install. To use SQL Server 2014 as a site database provider, the site had to have been already running, and upgraded from SQL Server 2012, SQL Server 2008, or SQL Server 2008 R2. New ConfigMgr 2012 installations using SQL Server 2014 as the base is not supported.

Steve
Guest
Steve

Hi Prajwal Desai,
I was just wondering if this is still the case now? Is SQL 2014 still not supported on new builds?

Many Thanks

Fernando Taboada
Guest
Fernando Taboada

Hi, I would like to know if SQL 2014 is compatible with SCCM 2012 R2 making an upgrade from SQL 2008 to SQL 2014
There’s any step that I must pay atenttion?

James Vincent
Member
James Vincent

On which server have you installed WSUS ?. The SUSDB will be created after WSUS is installed.

Eng.hassan Almanasrah
Guest
Eng.hassan Almanasrah

Hi Prajwal
Strange thins appeared for two times I tried to install SCCM.
After installing WSUS and make the SQL database for it. I cant find the database for WSUS in SQL database for SCCM. I gues it should be susdb!!!!
Leading to errors in SUP roles in SCCM. I tried many times to troublshoot, but still the same.
If you can help me and thanks for the nice installation procedures. You made it easy. 😉
Thanks

Binarylab
Guest
Binarylab

Exactly. A service principal name (SPN) needs to be configured for the domain user account if you’re using it for SQL services (best practice). Otherwise it will not work.

Roy
Guest
Roy
John
Guest
John

What should I set for the SQL Minimum server memory if the entire server itself I’m installing it on only has 6GB of memory dedicated to it?

James Vincent
Member
James Vincent

John, you can still set the SQL min memory to 8 GB. I would recommend you to increase the memory.

John
Guest
John

Hey cool, thanks.

Also, any suggestion or reference on setting up another server that will be used as a Distribution Point?
I know how to do it local, but not how to do it from the Site server against a remote target 2012 server that I want to designate as a Distribution Point.

Thanks much

James Vincent
Member
James Vincent

It’s pretty much the same as you do for normal DP.

suhas
Guest
suhas

Thank you for such a useful article.
i have a quick question, i have installed server 2012 r2 standard edition & my base system and having 4gb of RAM.
installed sql server on it and assigned 3gb RAM, will it runs ok as i have installed it only for learning hence i am assuming there will not much be load on it. I mean not a huge DB. Any inputs please.

Suraj Mishra
Guest
Suraj Mishra

Hello Prajwal,

I have a strange issue. I have already installed SQL 2012 with SP1 however when i am trying to install SCCM 2012 R2, its asking me to use SQL 2012 with CU2 or higher. I even tried installing SP2 however the error is same. The base OS for both the servers is Server 2012 Datacenter edition.
I have checked the ports. That seems to be ok. However this error dosent seem to disappear.

Hope you can suggest a solution to this issue.

James Vincent
Member
James Vincent

Hi Suraj, can you post the screenshot in the comment ?

Suraj Mishra
Guest
Suraj Mishra

Hello Prajwal, so sorry to bother you…after posting the question to you i continued the troubleshooting and upgraded SQL from SP1 to SP2. Doing that fixed the issue. Surprisingly Sp2 didnt work when i tried to keep SCCM and SQL on the same server.
When i made another server and installed SQL on that one, SP2 worked.

ROTH Fabrice
Guest
ROTH Fabrice

Thank you very much Prajwal! Very cool posting.
I thing to mention, where I banged my head on the table for more than a week now. Be certain to install the 64-Bit Version of SQL-Server. With the 32-Bit Version of SQL 2012 (or 2012R2) you will get an “incorrect SQL Version” at the pretest of SCCM.

Mark
Guest
Mark

Thanks for posting this I just started learning System Center and this is great information.

Zhorka
Guest
Zhorka

thx Prajwal, does it need local admin rights on the SQL Server?

Zhorka
Guest
Zhorka

Hi Prawal! Very good tutorials!

Does the SQL Service running account need local admin rights?

thx

selim atmaca
Guest
selim atmaca

Your articles are so good. They are step by step and clear explanations. Thanks a lot for sharing your experiences with others.

Blake
Guest
Blake

One of the better tutorials I’ve seen on the web. Love the web design, makes it all so easy to read and follow. (I am a web designer as well as Sys Admin).
There is one step missing though.
In SERVER CONFIGURATION when specifying the accounts, you need to click on the COLLATION tab and set “SQL_Latin1_General_CP1_CI_AS ”
The first comment mentions how to do this after the installation but it would probably be best to include this configuration straight into the install procedure.

Isaac Bowley
Guest
Isaac Bowley

Thanks for this bit Blake.

MSCCMUser
Guest
MSCCMUser

I came across your page searching for support on SQL2012 Always ON database support. I am not able to install on Always ON listner IP/FQDN., I keep getting Network Path not found when I give the SQL FQDN name of sql server. I can ping the FQDN from the Server and get the correct IP. ALso, made sure the Dynamic ports are not configured., TCPIP/1433 is selected on SQL Config.

Any help is appreciated.

Rakesh K
Guest
Rakesh K

Thanks Prajwal.. Also can I choose named instance while setting up SQL server ?

Rakesh K
Guest
Rakesh K

Hi, can I install all the features of SQL ?

Tristan
Guest
Tristan

Marvellous topic

You need to change SQL database collation (?)
setup /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS /SAPWD=########

Thanks you

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More