Sunday, 10 February 2013

ConfigMgr 2012 / SCCM 2012 SP1 Step by Step Guide Part 2: SQL 2012

Back to main menu

Part 2 describes the installation of SQL 2012 (do not use SP1 - no support for WSUS)

 (Edit 21st November 2013: SQL 2012 SP1 fully supported for SCCM 2012 SP1 or R2)

This installation was carried out on a small site (approx 300 users) so the SQL deployment did not have to be as carefully designed. For larger Config Mgr deployments it is essential to design SQL and carefully locate database, log files and tempdb on seperate drives.

 My colleague, System Centre MVP Paul Keely, has prepared a good guide for SQL design in relation to System Centre deployments.

 http://gallery.technet.microsoft.com/SQL-Server-guide-for-8584c403

 1. SQL Pre-Requisite

 You must add the .Net Framework 3.5 feature. However, in Windows Server 2012, this cannot be done through the GUI

 First mount the Windows Sever 2012 media and open PowerShell as Administrator

 Run the following PowerShell command

 Install-WindowsFeature Net-Framework-Core -Source G:\Sources\sxs (G: is my DVD drive)


Restart the server

Shutdown -r

2. Install SQL 2012

Start Setup
 Select Installation

Select New SQL Server stand-alone installation



Accept the License terms

Select SQL Server Feature installation so that you can choose which components to install


We require the following components for an SCCM deployment

Database Engine Services
Reporting Services - Native
Management Tools - Complete





Choose Default Instance and SQL Program Files location



Choose Service Accounts - I have chosen Local System Accounts but it is generally best practice to use domain service accounts (must have rights to create SPN in Active Directory)


Choose SQL collation as follows. This is very important. If you do not choose this collation you will not be able to install Config Mgr 2012


Add accounts to be SysAdmin on SQL

 


Choose database and log file locations (see note above for best practice)


Choose to install and configure Reporting Services




Install





3. Install SQL Cumulative Update 3 (CU2 minimum required for Config Mgr 2012 SP1)







4. Configure SQL maximum memory. I have 12GB so I will use 8GB for SQL.

(Also if you configure 8GB minimum memory you will not have a SQL memory warning in Config Mgr Prerequisite checker)

 

at

46 comments:

  1. Joe Conaghan26 August 2013 at 05:43

    Gerry, it doesn't squawk about 8 gigs for the maximum. It wants that as the minimum.

    ReplyDelete
  2. Anonymous14 January 2014 at 09:15

    Hi Garry,

    Which SQL Cumulative Update to choose for the following SCCM Scenario.

    Windows server 2012
    SC2012_R2_SCCM_SCEP
    SQLServer2012SP1-FullSlipstream-ENU-x64

    ReplyDelete
  3. Gerry Hampson14 January 2014 at 12:12

    No Cumulative Update is required in this configuration. You can see the supported configurations here

    http://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLSrvReq

    ReplyDelete
  4. Anonymous30 January 2014 at 17:26

    Hi Gerry,

    Which SQL version should I use for SCCM lab setup?.

    ReplyDelete
  5. Unknown13 February 2014 at 09:29

    Hi Gerry,
    Your step by step guide looks very useful - many thanks.
    I currently have an install of SCCM 2003 running on a separate DB on SQL 2008 R2 and is used mainly for image deployment, MS FEP and a small amount of software deployment. I have approx 500 desktops and 500 laptops.
    I am planning to install SCCM 2012 on another server and duplicate the current DB as all DP's and software\image shares can be accessed independently.
    Is this possible? If so am I best to duplicate the current SCCM database so I can migrate off the 2003 install or use the original - can you see any major drawbacks with either?
    Your help wil be much appreciated.
    Cameron, Newcastle

    ReplyDelete
    Replies
    1. Gerry Hampson13 February 2014 at 13:26

      Hi Cameron,

      You're talking about SMS 2003. You won't be able to use the SMS database. There is no migration path from SMS 2003 to SCCM 2012.

      You have two options:

      1. SMS 2003 --> SCCM 2007 SP2 ---> SCCM 2012. These are side-by-side migrations (not in-place) and this is a lot of work. You can still be left with a very messy environment.

      2. You start from scratch with SCCM 2012R2. This would be my recommendation. You will not be able to migrate anything from SMS 2003. You will be able to copy your source data, of course, and import to SCCM.

      Delete
  6. Anonymous20 February 2014 at 17:45

    Hi Gerry,

    Your Step by step guide in putting together a ConfigMgr 2012 environment from scratch is great. The only thing I should add is that with organizations who may be doing an XP to 7 migration will run into some problems with 2012 R2. It is recommended to not go above SP1 until they have migrated to 7. There is a workaround that is currently not supported by MS.

    http://social.technet.microsoft.com/Forums/en-US/6e934990-999a-4367-860a-3ce4e5eda956/sccm-2012-r2-error-0x800700c1?forum=configmanagerosd

    ReplyDelete
  7. saththiyan21 February 2014 at 04:26

    Hi, I installed SQL server on a different server and tried but i got failed to connect to SQL , it through an error " path not found"

    ReplyDelete
  8. Unknown20 March 2014 at 13:43

    Hello Garry,

    While you mentioned to install ADK 8.1, it has option to install SQL server 2012 as well and i already installed SQL 2012 during ADK 8.1 installation.

    I have also downloaded SQL SERVER 2012.iso from microsoft website as well.

    So now i have two questions:

    1 Should i install manually SQL server 2012 again as you mentioned in this blog or i don'e need to?
    2 I started installing it again, i run the SQL Server Installation Center,
    its asking me,
    "perform a new installation or add features to an existing instance"
    and here i can see my previously installed SQL serve instance.

    However i will try installing the way you mentioned in this blog, which is ,"Perform a new installation" instead of option "add feartures to an existing instance"

    ReplyDelete
    Replies
    1. Gerry Hampson20 March 2014 at 17:39

      It sounds like you're in a bit of a mess Ramneesh. This is a complex product and can be difficult to deploy especially if it gets messy at the start. I would start again.

      Note that you do not need to install all the ADK options. You do not need SQL Express. You need three components only:

      Deployment Tools
      Windows PE
      USMT

      Delete
  9. Unknown20 March 2014 at 18:58

    Hi Gerry,
    Thanks for reply. I will uninstall SQL server installed with ADK 8.1 and install it again .

    Thanks in advance !
    I might ask you more questions

    ReplyDelete
    Replies
    1. Gerry Hampson20 March 2014 at 19:09

      I would actually reinstall the OS Ramneesh.

      Delete
    2. Unknown22 March 2014 at 18:57

      Hi Gerry,

      I instaled OS again as you said, installed SQL 2012 SP1 successfully,
      followed all the steps and now installing config manager,

      all the prerequiste files are downloaded successfully, and prerequiste check is also good,

      but during final installation i get error,

      "Setup could not install SQL RMO, ConfigMgr installation cannot be completed"

      Thanks

      Delete
    3. Gerry Hampson24 March 2014 at 07:45

      There are two possible ways to deal with this Ramneesh,

      1. Go to your download prerequisites folder. Uninstall the SQL Management Object by executing SharedManagementObjects.msi. Run the ConfigMgr setup again.
      or 2. Go to your download prerequisites folder. Install sqlncli.msi and SQLSysClrTypes.msi manually. Run the ConfigMgr setup again.

      Delete
    4. Unknown24 March 2014 at 13:00

      Thanks Gerry, it's working.
      I am now configuring SCCM,
      With roles.

      In few days I will make it work with your help.

      Delete
    5. Gerry Hampson24 March 2014 at 15:54

      That's good.

      Delete
  10. Eduardo Perez1 April 2014 at 17:32

    Hello Gerry,

    I have a question about SQL, we are about to start the upgrade (from scratch) from SCCM 2007 to SCCM 2012. When we installed SCCM 2007 a few years ago, we had SQL on a separate server and we purchased a per-processor license. This time our Microsoft reseller is telling me that I don't need to purchase that version of SQL (I assume he recommended to use SQL express). Our installation is not that big, about 3500 devices and 800 AD users. I just wanted to make sure of which way to go for SQL: use SQL express or to get the per-core license on SQL standard 2012?

    Thanks
    Eduardo

    ReplyDelete
    Replies
    1. Gerry Hampson1 April 2014 at 17:38

      You cannot use SQL Express for ConfigMgr. You require SQL Standard edition. However it is included in the System Center license costs so it does not cost you anything (as long as you just use it for System Center products).

      You can have local or remote SQL and there is no cost (I always choose local).

      Delete
  11. Eduardo Perez1 April 2014 at 18:10

    wow... Thanks for the clarification Gerry.

    ReplyDelete
  12. Unknown1 April 2014 at 23:26

    Hi,
    I just follow your instruction and when im trying to install the SSCM i get the error :
    "Configuration Manager sites require a supported SQL Server version with required hotfixes for site database operations to succeed. Before Setup can continue, you must install a supported version of SQL Server on the specified site database server"
    Any solution ?thanks

    ReplyDelete
    Replies
    1. Gerry Hampson2 April 2014 at 08:59

      Exactly what version of SQL did you install (incl. SP & CU)? Is it local or remote?

      Delete
  13. Unknown4 April 2014 at 22:59

    I had the same issue. Downloading the SQL Server 2012 SP1 fixed the problem. To verify you have the minimum version check the help about on SQL Server Mgmt Studio. The minimum you need is version 11.00.3000. See here for more info: http://sqlserverbuilds.blogspot.com/

    ReplyDelete
  14. Anonymous26 September 2014 at 18:16

    Hi Gerry

    Is SQL 2012 free with SCCM 2012? I mean does the SCCM 2012 license include SQL?

    ReplyDelete
  15. Anonymous18 November 2014 at 19:04

    Gerry, when you installed the SQL databases to another volume (D: in your case).....did you setup the folder structure on that volume first?

    I.e. Make it mimic the folder structure on the C: drive

    ReplyDelete
    Replies
    1. Gerry Hampson18 November 2014 at 21:05

      No. I just changed C: to D: in the folder location and the SQL installer did the rest.

      Delete
  16. Anonymous4 February 2015 at 06:36

    Hi Gerry,
    I have few questions re SCCM 2012 R2 SQL requirements please;

    1. For SCCM 2012 R2 Central admin site can we have SQL sever remotely similar to primary sites? We have multiple primary sites globally therefore we would like to have a CAS in the hierarchy.

    2.Is SQL 2014 still not supported for SCCM 2012 R2?

    3. If not, can we use SCCM 2012 R2 in database incompatibility mode for 2008/2012?

    4. Do you have any other alternative solution to use SCCM 2012 R2 with SQL 2014? Is there a way to implement the database on SQL 2008 and then migrate the database to 2014?

    Currently we have both SQL 2008 and 2014 environments, we are slowly moving away from 2008 due to old license maintenance costs. So we would like to implement this new SCCM solution on 2014.

    Thanks,
    Ash

    ReplyDelete
    Replies
    1. Gerry Hampson4 February 2015 at 16:47

      1: Yes, you can use remote SQL to deploy a CAS. Why do you need multiple primary sites?

      2,3,4: You can use SQL 2014 for your ConfigMgr solution. You just can't install a new site on this database. You have to use a supported SQL version and then upgrade to SQL 2014.

      See here for supported configurations
      https://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLDBconfig

      Delete
    2. Anonymous5 February 2015 at 00:50

      Thanks Gerry
      We have multiple business locations globally with small remote office locations, business is also expanding rapidly. Eg New York office and New Jersey remote office, So NY will be a primary site and NJ will be a DP or a secondary site (still deciding between DP or SS). We also have separate IT staff in main office locations (except remote sites) to manage local IT requirements. So in our head office in Melbourne Australia we will have a CAS as well as a primary site for our Melbourne IT requirements. CAS will be used to connect all primary locations to our head office. We have reporting and auditing requirements in HO to oversee global IT assets. So is multiple primary sites with a CAS is the best way to do this?

      Thanks,
      Ashley

      Delete
    3. Gerry Hampson5 February 2015 at 16:35

      You really only need a CAS if you are managing 100K devices. Otherwise you can use a Primary Site and separate the environments using RBA if you need to

      http://blogs.technet.com/b/gary_simmons_mcs/archive/2013/09/19/sccm-2012-rba-example.aspx

      If you are unsure about using a CAS Ashley, you should post a question on the TechNet forum. Everyone will persuade you against using one.

      https://social.technet.microsoft.com/Forums/en-US/home?category=systemcenter2012configurationmanager

      Delete
  17. Anonymous30 April 2015 at 18:47

    Hi Gerry, Installing SCCM 2012 R2 on Windows 2012 R2.
    - On the SQL install, is SP2 supported? All the documentation I see uses SP1.
    - I am not a Domain Admin. I do have admin rights to my OU where we will be deploying SCCM. Obviously I can affect schema. Do I do the same prep work to my OU that I would to the domain when deploying SCCM?

    ReplyDelete
    Replies
    1. Gerry Hampson6 May 2015 at 16:54

      I'm not 100% sure what you are asking here.

      1. Are you talking about SQL Server 2012 SP2? Yes, this is fully supported.
      2. You should extend the schema. This will effect the entire domain, not just your OU. You need to be a Schema Admin to do this.

      Delete
  18. Anonymous20 April 2016 at 10:26

    Hi Gerry, I have installed SQL Server 2012 Service Pack 1 for SCCM 2012 R2 SP1. Is this right, I did get an warning about the fire wall when I installed SQL.

    I am new to this and your guide looks to be helping very well.

    Thank you

    ReplyDelete
    Replies
    1. Gerry Hampson20 April 2016 at 13:38

      Yes, this is supported. You can find the supported configurations in the TechNet library.

      https://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLSrvReq

      However, I'm a big fan of always installing the latest SP and CU.

      Delete
  19. Anonymous21 April 2016 at 13:58

    Hi Gerry, I got an error when installing SCCM. Required SQL Server Collation - FAILED - does this mean I would need to reinstall SQL server?

    Thanks

    ReplyDelete
    Replies
    1. Gerry Hampson21 April 2016 at 14:20

      Yes, you'll have to re-install and use the required collation

      SQL_Latin1_General_CP1_CI_AS

      Delete
  20. Anonymous13 September 2016 at 19:26

    Hi Gerry,

    Do you have the SQL 2012 installation file you used as i dont seem to have the same options in the "feature selection" phase of the installation.

    Great article by the way.

    Thanks
    Greg

    ReplyDelete
    Replies
    1. Gerry Hampson28 September 2016 at 15:47

      Thanks Greg. I didn't use a SQL configuration file. It was a manual installation.

      Delete
  21. Jaison Lucas13 August 2018 at 11:53

    Hi Gerry,

    I have a IBM Server- quadcore with 16Gb RAM and 1/2 TB disk space.
    Is it fine if i install DC, SCCM, SQL and WSUS all on this server?
    Or
    Is it advisable to split it on 2 servers.

    Many Thanks.

    ReplyDelete
    Replies
    1. Gerry Hampson17 August 2018 at 17:54

      I would NEVER install ConfigMgr on a domain controller.

      Delete
  22. Unknown9 December 2018 at 12:27

    Dear Geery i glad to contact you again please advise me
    i am installing SCCM 2016 on remote SQL 2012SP4
    i have tow failed prerequisite check although i am set Collection as required
    (SQL_Latin1_General_CP1_CI_AS)) and check it in SQL management stdio

    tow failed are
    1- Configuration Manager requires that you configure your SQL Server instance and Configuration Manager site database (if already present) to use the SQL_Latin1_General_CP1_CI_AS collation

    2- The SQL Server installation must use a case-insensitive collation. For example, SQL_Latin1_General_CP1_CI_AS.

    ReplyDelete
    Replies
    1. Gerry Hampson9 December 2018 at 16:26

      Check again, did you use Latin1_General_CI_AS collation? This won't work. You need the SQL_ prefix.

      Delete
  23. shoaib4 March 2020 at 08:30

    Hi Gerry I m getting this error " ERROR: SQL Server Database has user defined objects, cannot configure database".

    ReplyDelete
    Replies
    1. Gerry Hampson6 March 2020 at 18:34

      That normally means that the ConfigMgr database already exists. Did a previous attempt fail? You should delete the database and start again.

      Delete