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.
Click OK once the Setup support rules are run and verified.
Check the box for I accept the license terms and click on Next.
After the Setup Support Rules are completed click on Next.
Select the Setup Role as SQL Server Feature Installation and click Next.
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.
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.
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.
Click Add Current User, this will add the current user to SQL server administrators. Choose the Authentication Mode as Windows authentication mode. Click Next.
Choose Reporting Services Native Mode and click on Install and Configure. Click on Next.
Click on Install to start the installation.
We have installed SQL server 2012 with SP1 on Windows Server 2012 R2 Datacenter Edition. Click on Close.
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.
In SCCM, logs review is very important for troubleshoot. If you have any tutorials which have details log analysis on scenario based troubleshooting in SCCM modules, This is will help us a lot.
If you have any material related to this pls send me on my Mail Id. Thank You.
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 or password is not valid. Use SQL Server Configuration Manager to update the service account.
[Details]
Microsoft.SqlServer.Configuration.SqlEngine.ValidationException: The SQL Server service account login or password is not valid. Use SQL Server Configuration Manager to update the service account.
================================================================================
[Error Message]
The credentials you provided for the Analysis Services service are invalid. To continue, provide a valid account and password for the Analysis Services service.
[Details]
System.ApplicationException: The credentials you provided for the Analysis Services service are invalid. To continue, provide a valid account and password for the Analysis Services service.
================================================================================
[Error Message]
The credentials you provided for the Reporting Services service are invalid. To continue, provide a valid account and password for the Reporting Services service.
================================================================================
It is clearly mentioned in the above error that the account name or password you have entered is invalid. Check the credentials. Hope you have the domain server created and the credentials you are entering is in the AD domain.
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.
Prajwalsccmadmin
Prajwal – Domain name & sccmadmin – domain admin account
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 install SQL server with admin or regular account, the setup wizard just disappears after 4-5 steps and never shows.
Appreciate your assistance on this.
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.
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
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?
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?
Could you post the question in community forums ?. I may need some more on this to troubleshoot further.
There’s a step missing somewhere. SQL needs some static TCP port and a service broker. Not sure how to set them yet.
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
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.
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
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.
It’s not the SQL express, its the SQL 2012 with service pack 1. If you are unable to download the specific version of SQL, you could look for some another version of SQL that is compatible with SCCM version. What version of SCCM are you using ?.
Hi Prajwall,
I downloaded System Center 2012 R2 Configuration Manager and Endpoint Protection SP1 that was linked by you in your guide on part 6 “installing System Center 2012 R2 Configuration Manager”.
Since I have a deadline of this thursday, I downloaded and installed SQL-server 2014 Express instead of the SQL-Server 2012 SP1 that was mentioned in your guide at part 3 “Installing SQL Server for System Center 2012 R2 Configuration Manager”.
I’ll tell you what I did so far:
– make System Management container
– Give full permissions
– Extend schema
– Install the features/roles mentioned in part 2 “Installing Prerequisites for Configuration Manager 2012 R2”
– Install SQL-Server 2014 Express
– Install WSUS mentioned in part 4 “Installing WSUS for Configuration Manager 2012 R2”
I’m not going to do part 5 since I’m in a test environment where my firewall is disabled.
And I’m about to begin at part 6, on the installationg of SCCM 2012 R2.
Cool. Let me know how it goes 🙂
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 scenario can I install SQL server 2014 stranded edition ?
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 – Could you create a support thread and post the same question ?. Please add screenshots.
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?
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.
@Melon – I had updated the post with the collation info long back. it’s not updated in the document that I uploaded to technet.
Yeah and that’s what I noticed, that it was not in the pdf.
Are you going to update the PDFs on technet?
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
Hi Paddy, post the question in forums along with screenshots.
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 🙂
the collation should revised
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.
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
yes, I am running SCCM 1511 with SQL 2014 SP1.
Brilliant thanks Prajwal, I am just deploying SCCM from scratch in a lab to play with prior to installing onsite, so I can go straight with SQL 2014 SP1 with my new install then.
What version of SCCM are you installing ?
I have downloaded, SC2012_R2_SP1_Configmgr & SC2012_SP2_Configmgr_SCEP, so plan on installing 2012 SP2 then upgrading to R2 SP1 will this be okay with a fresh install of SQL 2014 SP1?
Yes.
Thanks Prajwal
Oh man I didn’t pay attention to the Secondary site part on the link you provided. So SQL-Server 2014 Express will not work. I have now downloaded a SQL-server 2014 standard SP1. I’ll have to re-do a part of my log file now
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?
On which server have you installed WSUS ?. The SUSDB will be created after WSUS is installed.
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
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.
Great guides, many thanks. What about the SPN ?
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?
John, you can still set the SQL min memory to 8 GB. I would recommend you to increase the memory.
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
It’s pretty much the same as you do for normal DP.
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.
I would suggest to increase the memory on server. I know you have restricted the SQL memory usage to 3 GB but i would recommend atleast 8 GB in your case.
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.
Hi Suraj, can you post the screenshot in the comment ?
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.
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.
Thank you fabrice.. I will update the information in my post..
Thanks for posting this I just started learning System Center and this is great information.
thx Prajwal, does it need local admin rights on the SQL Server?
Yes
Hi Prawal! Very good tutorials!
Does the SQL Service running account need local admin rights?
thx
Thank you Zhorka, SQL Server service can be run by using a domain local account or the local system account of the computer that is running SQL Server.
For my lab purpose I use the domain admin account..
Your articles are so good. They are step by step and clear explanations. Thanks a lot for sharing your experiences with others.
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.
Thanks for this bit Blake.
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.
Thanks Prajwal.. Also can I choose named instance while setting up SQL server ?
Yes you choose to have a named instance, read the note given in the post..
Hi, can I install all the features of SQL ?
Yes you can install all the SQL server features if you want to..
Marvellous topic
You need to change SQL database collation (?)
setup /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS /SAPWD=########
Thanks you