locked
SQL Server (primary and site servers) RRS feed

  • Question

  • Hello,

    I am a bit confused on the wording, or maybe not, of the SQL requirements for SCCM 2012.

    Looking here :

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

    Seems like there is an instance of SQL for the Primary Server, then there is also SQL for the site servers? I remember setting this up on our test domain and was able to bring it up with 1 SQL server, 1 Primary, 1 Secondary and only had SQL installed on the dedicated SQL server. Could someone please clarify this for me.

    I also have a question about SQL in general. Supervisor of our DataCenter team suggested creating a new instance of the database on an existing SQL server, one that has many other databases on it. Is this recommended? is this what would be considered a "named" instance instead of the default SQLServer$, or whatever? I know that realistically it wouldn't be recommended because of CPU/Memory/Space but the servers that house these databases are large and can be expanded to support it.

    Thanks to those who provide useful answers, I really appreciate it.

    Thursday, June 27, 2013 7:15 PM

Answers

  • Don't confuse site server with secondary site server. A secondary site server is a type of site server that is specific to a secondary site. I know that sounds obvious, but it seems you are getting hung up on "site server". The requirement is a SQL instance for each site (not necessarily site server). Primary sites require a full SQL instance and secondary sites require SQL Express which will be installed and configured during their installation (although a full SQL install can be used if desired although this is not really needed and is sometimes problematic).

    For the SQL collation, as Gerry pointed out, it's simply a configuration of the SQL instance (and DB for that matter) that dictates default sort order of the database. Note that on US English versions, the supported collation is definitely default though.

    As for where to place SQL, it is not correct that SQL is only provided at no cost if you co-locate it on your primary site server. SQL Server is prov ided at no cost for exclusive use with any System Center product regardless of whether it is local or remote from the site server.

    Where you should put it a different story altogether. ConfigMgr and SQL Server require a lot of IOPS -- even for 5,000 managed systems. Not only will ConfigMgr be impacted by the performance of other applications using the same SQL Server, but those other applications will also be impacted by ConfigMgr.

    Do not treat ConfigMgr as just another application in your datacenter -- it is not! Unless you have another application with 5,000 simultaneous users. ConfigMgr does way more than most business apps ever do so you must be prepared for this load. Using a SQL instance in the primary site server is typically preferred because you can dedicate disks to it without ever having to worry about sharing them or traversing a network to get to them. It's a bit more complicated than that, but it usually does boil down to this.


    Jason | http://blog.configmgrftw.com

    • Marked as answer by RCCMG Friday, August 2, 2013 3:58 PM
    Thursday, June 27, 2013 9:16 PM

All replies

  • You need a SQL instance for the Primary Site server - this can be local or remote. A secondary site leverages SQL Express so no worries there.

    Typically exisiting instances of SQL are not suitable as the Config Mgr database requires a specific collation.



    Gerry Hampson | Blog: www.gerryhampsoncm.blogspot.ie | LinkedIn: Gerry Hampson | Twitter: @gerryhampson

    Thursday, June 27, 2013 7:22 PM
  • Thanks for the info on the sql - I am guessing the site server installs will manage setting the sqlexpress up.

    Can you carify this some more?

    "Typically exisiting instances of SQL are not suitable as the Config Mgr database requires a specific collation."

    What do you mean by specific collation? I know how to get data out of SQL, but am not 100% on SQL terms. Is it just better practice to either have a dedicated SQL server or SQL loaded on the primary, this is either or statement. Our client count is pretty small compared to some others that I have seen on this site, we only have 4000 at the moment, plus around 700 servers.

    Thanks again.

    Thursday, June 27, 2013 7:28 PM
  • This is the required database collation for System Center 2012 products (sceenshot below). This is not the default, you must choose this on installation. This is why most exisiting SQL instances are not suitable.

    You can have a local or remote SQL instance - both are supported (as long as the collation is OK). It is preferred to have a instance dedicated to Config Mgr.

    MS seem to be promoting locally installed SQL systems as they offer SQL Standard for free if you install it locally. 



    Gerry Hampson | Blog: www.gerryhampsoncm.blogspot.ie | LinkedIn: Gerry Hampson | Twitter: @gerryhampson

    Thursday, June 27, 2013 8:12 PM
  • Don't confuse site server with secondary site server. A secondary site server is a type of site server that is specific to a secondary site. I know that sounds obvious, but it seems you are getting hung up on "site server". The requirement is a SQL instance for each site (not necessarily site server). Primary sites require a full SQL instance and secondary sites require SQL Express which will be installed and configured during their installation (although a full SQL install can be used if desired although this is not really needed and is sometimes problematic).

    For the SQL collation, as Gerry pointed out, it's simply a configuration of the SQL instance (and DB for that matter) that dictates default sort order of the database. Note that on US English versions, the supported collation is definitely default though.

    As for where to place SQL, it is not correct that SQL is only provided at no cost if you co-locate it on your primary site server. SQL Server is prov ided at no cost for exclusive use with any System Center product regardless of whether it is local or remote from the site server.

    Where you should put it a different story altogether. ConfigMgr and SQL Server require a lot of IOPS -- even for 5,000 managed systems. Not only will ConfigMgr be impacted by the performance of other applications using the same SQL Server, but those other applications will also be impacted by ConfigMgr.

    Do not treat ConfigMgr as just another application in your datacenter -- it is not! Unless you have another application with 5,000 simultaneous users. ConfigMgr does way more than most business apps ever do so you must be prepared for this load. Using a SQL instance in the primary site server is typically preferred because you can dedicate disks to it without ever having to worry about sharing them or traversing a network to get to them. It's a bit more complicated than that, but it usually does boil down to this.


    Jason | http://blog.configmgrftw.com

    • Marked as answer by RCCMG Friday, August 2, 2013 3:58 PM
    Thursday, June 27, 2013 9:16 PM
  • As for where to place SQL, it is not correct that SQL is only provided at no cost if you co-locate it on your primary site server. SQL Server is prov ided at no cost for exclusive use with any System Center product regardless of whether it is local or remote from the site server.

    So if I am understanding you correctly, it is licensed with SCCM already so there really isn't a need to create a new instance on a dedicated DB server?

    Thursday, June 27, 2013 9:29 PM
  • That's correct


    Gerry Hampson | Blog: www.gerryhampsoncm.blogspot.ie | LinkedIn: Gerry Hampson | Twitter: @gerryhampson

    Thursday, June 27, 2013 9:32 PM
  • From a licensing perspective, correct.

    Performance is a completely different subject though as I pointed out above.


    Jason | http://blog.configmgrftw.com

    Friday, June 28, 2013 12:31 AM
  • Jason - I know it has been a while since last post on this thread but something has come up since then about IOPS. Is there a metric, or metrics, that I can run to see how busy the DB is?
    Friday, August 2, 2013 3:53 PM
  • Here's a great post: http://www.keepitsimpleandfast.com/2009/03/how-to-measure-performance-of-your-sql.html

    Microsoft also has a tool: http://www.microsoft.com/en-us/download/details.aspx?id=20163

    If you search "measure SQL Server performance" you'll get lots of hits also.


    Jason | http://blog.configmgrftw.com

    Friday, August 2, 2013 4:01 PM
  • Awesome thank you. I was searching how to measure SQL IOPS but was getting stuff, just wasn't sure if it was relevant or not.

    Thanks for your help.

    Friday, August 2, 2013 4:03 PM