Jump to content


anyweb

How can I install System Center Configuration Manager (Current Branch) version 1902 on Windows Server 2019 with SQL Server 2017 - Part 4

Recommended Posts

Introduction

This multi-part guide will show you how to install the latest baseline version of Configuration Manager from Microsoft. The latest available baseline version is System Center Configuration Manager (Current Branch) version 1902 as of April the 10th 2019. I blogged how to upgrade to 1902 here. This guide is aimed a new installations of SCCM. Baseline media is used to install new ConfigMgr sites or to upgrade from supported versions, for more information about baseline media please see my blog post here.

Note: The SCCM 1902 Current Branch media is not yet available on MSDN or VLSC. When the new baseline media is released I'll update this note.

This series is broken down into the following parts:-

You can use this multi-part guide to get a hierarchy up and running on Windows Server 2019 using SQL Server 2017. The concept behind this is to guide you through all the steps necessary to get a working Configuration Manager Primary site installed (for lab use) using manual methods or automated using PowerShell. This gives you the power to automate the bits that you want to automate, while allowing you to manually do other tasks when needed. You decide which path to take. PowerShell knowledge is desired and dare I say required if you are in any way serious about Configuration Manager.

I will show you how to do most steps via two methods shown below, it's up to you to choose which method suits you best but I highly recommend automating everything that you can, using PowerShell.

  •         Method #1 - Do it manually
  •         Method #2 - Automate it with PowerShell

Downloads

The scripts used in this part of the guide are available for download here. Unzip to C:\Scripts. The scripts are placed in the corresponding folder (Part 1, Part 2 etc) and sorted into which server you should run the script on (DC01 or CM01).

 

Scripts.zip

 

Step 1. Install SQL Server 2017

Note: Perform the following on the Configuration Manager server (CM01) as a Local Administrator, I'd suggest you logon as the username matching your name.

  • Method #1 - Do it manually

In this section you will install SQL Server 2017 CU14 which is the latest supported version of SQL Server that is compatible with SCCM 1902 Current Branch as of 2019/4/16. For details about which versions of SQL Server are supported with different site systems in ConfigMgr, please see this page.

Before starting, please configure the firewall as described in https://go.microsoft.com/fwlink/?linkid=94001 to allow access to SQL Server through the firewall. You can do this by executing the following command as local administrator on the CM01 (ConfigMgr) server.

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

 

SQL Firewall.png

After configuring the firewall, browse to the drive where the SQL Server 2017 media is, and run setup.exe. The SQL Server Installation Center wizard will appear.

sql server setup wizard.png

Click on Installation and then choose New SQL Server standalone installation or add features to an existing installation.

Installation new sql server.png

Enter the Product Key or use the evaluation version if that's what you want to use.

Note: The product key will be automatically filled in for licensed media downloaded from Microsoft Volume Licensing  Service Center.

sql product key.png

Accept the EULA

eula.png

Make your Microsoft Update choices

sql microsoft update settings.png

and review your Install rules, as long as you've opened the correct port for SQL you'll be ok and can safely ignore the Warning about the Firewall.

sql install rules.png

select the SQL server instance features you need (at least Database Engine Services) and if necessary change the drive letter where you intend to install it

sql installation folders.png

And configure the Instance Configuration or just leave it as default

instance configuration.png

Verify the Service Accounts settings

server configuration.png

and for Collation (click on the Collation tab in Server Configuration), make sure the collation is set to SQL_Latin1_General_CP1_CI_AS

sql server collation.png

For Database Engine Configuration, click on Add Current User

add current user.png

After configuring Data Directories, TempDB and Filestream settings you are ready to install

ready to install.png

Click on Install to start the installation of SQL Server 2017, and once it's completed, click Close.

sql server setup done.png

Next download and install the following:

ssms.png

 

  • Method #2 - Automate it with PowerShell

Note: Make sure your SQL Server 2017 media is in the drive specified in the script or edit the script to point to the new location of the media. The script  set's the installation path pointing at D:\MSSQL if you want to install SQL somewhere else  please change the variables as appropriate.

To install SQL Server 2017 use the Install SQL Server 2017.ps1 script. The script will create a ConfigurationFile.ini used to automate the installation of SQL Server 2017, and after it's installed the script will download the SSMS executable (Management Studio) and install it. Then it will download Reporting Services and install it. If either of the EXE's are in the download folder, it will skip the download and just install. SQL Server no longer comes with the Management Studio or Reporting Services built in, and they are offered as separate downloads, don't worry though, my PowerShell script takes care of that for you.

1. Extract the scripts to C:\Scripts on CM01 and load the Install SQL Server 2017.ps1 script located in C:\Scripts\Part 4\CM01

2. Edit the variables [lines 17-81] as desired before running.

3. Logon as the user specified in line 20.

logon as admin user defined in line 20.png

4. Start Windows PowerShell ISE as Administrator and run the script by clicking on the green triangle.

SQL script has run.png

Done !

That's it for this part, please join me in Part 5 where we Configure and Install System Center Configuration Manager 1902.

  • Like 1
  • Thanks 1

Share this post


Link to post
Share on other sites

I'm going to run through this guide again. There's a few inconsistencies. Each step has a different scripts.zip attached.  I figured I would just download the part 5 which would include everything (6 wasn't out at the time). And I noticed when you run powershell to install WSUS calls for a XML file in part 2\cm01 folder that does not exist.  The XML file is in part 4 folder.  Small little things like this.  Also install roles and features power shell is in part 4 and 5 folders.

  • Thanks 1

Share this post


Link to post
Share on other sites

ah my bad, thanks for the heads up. I mixed up some old/new scripts... i've re-uploded them for parts 4-6, should be fine now, please check.

 

  • Like 1

Share this post


Link to post
Share on other sites

For some reason I keep receiving the following error.  Default and when I reduce to 2048.  My VM has 4GB.

 

Configuring SQL memory...Exception setting "ConfigValue": "Failed to retrieve data for this request."
At C:\Scripts\Part 4\CM01\Install SQL Server 2017.ps1:257 char:1
+ $SQLMemory.Configuration.MinServerMemory.ConfigValue = $SQLMemMin
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting
 
Exception setting "ConfigValue": "Failed to retrieve data for this request."
At C:\Scripts\Part 4\CM01\Install SQL Server 2017.ps1:258 char:1
+ $SQLMemory.Configuration.MaxServerMemory.ConfigValue = $SQLMemMax
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

 

 

2019-06-06 17_11_32-Window.png

Share this post


Link to post
Share on other sites

I've seen errors configuring the SQL Server memory when you are logged on as the wrong user, make sure you are logged on as the domain\user specified in the script, look at line 20... I think the user must be a SA to do the sql server memory change, but can't remember, please verify what user you are running this as... and i don't think domain\administrator is a SA....

  • Like 1

Share this post


Link to post
Share on other sites

11 hours ago, anyweb said:

is that user a sql SA ?

I'm just following the instructions by modifying line 20 which is supposed to set the SA. I'm executing the install SQL 2017 powershell so there's no step before this where SQL would be installed and a SA created ahead of this powershell script.  Also there are some references to copying things into c:\scripts\sccm1802 instead of 1902.

Share this post


Link to post
Share on other sites

On 6/12/2019 at 7:30 AM, anyweb said:

sorry, i mean is that user in the local administrators group on that server ?

Yes. Even though it's domain admin, schema, enterprise etc I added it specifically and I'm logged in as that user to execute the SQL install powershell script.

Share this post


Link to post
Share on other sites

ok just tried it, it worked fine. also i don't have any 1802 in my script, so please double check what you've downloaded, it should be dated April 2019.

 

image.png

Share this post


Link to post
Share on other sites

On 4/16/2019 at 10:05 AM, anyweb said:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

I ran through this again in detail and it worked like a champ. I apologize. I'm not sure why that little part or error I was getting was happening but it went out without a hitch.  Such great work Niall. All the notes in the powershells etc etc plus the step by step stuff as well. My hats off to you as always!!!

 

I know it's one number typo. I didn't want you to think I was crazy.

 

<#
# Extend the Schema in AD for SCCM (Current Branch) version 1902, 2019/4/23 Niall Brady, https://www.windows-noob.com
#
# This script:            Optionally extends the schema for SCCM
# Before running:         Make sure the $SCCMPath variable points to the location of the 1802 ISO (or extracted media). Edit the variables as necessary (lines 16-45). 
# Usage:                  Run this script on the ConfigMgr Primary Server as a user with local Administrative permissions on the server
#>

 

<#
# Install SCCM (Current Branch) version 1902, 2019/4/23 Niall Brady, https://www.windows-noob.com
#
# This script:            Optionally extends the schema for SCCM, downloads pre-requisites, and then Installs SCCM (Current Branch) version 1802.
# Before running:         Make sure the $SCCMPath variable points to the location of the 1802 ISO (or extracted media). Edit the variables as necessary (lines 16-45). 
# Usage:                  Run this script on the ConfigMgr Primary Server as a user with local Administrative permissions on the server
#>

Share this post


Link to post
Share on other sites

Hi,

 

I was wondering if its possible to add one more than one user/group to the SQL admins on the script?

eg:  "TEST\Administrator" " "TEST\SQL_Admin" "TEST\Domain Admins" "BUILTIN\Administrators"

 

Thanks

Share this post


Link to post
Share on other sites

2 hours ago, Suganya said:

Will that I can go for Windows authentication instead of SQL.. How do I pass credentials into .ini file or anyother way to do this please help..

you'd need to look at my PowerShell script for installing SQL Server 2017, and configure it to produce the output you require, then test it.

Share this post


Link to post
Share on other sites

Hi,

I used your pages to install sccm 1902. (downloaded from evaluation center)

the prereq keeps failing on:

  • sql collation
  • user used for the services is not correct.
  • the user 

i used an AD user with sysadmin permissions for the services and when i check the collation it is the correct one.

installation is also with a sysadmin user with administrator permissions.
 

collation.jpg

Edited by Razor97
extra screenshot

Share this post


Link to post
Share on other sites

1) Have one query on DB server related.. If I have DB01 and APP01 for database and application related.. Do I need to install all the prerequisite (part1,2 &3)for DB01 server also, these two DB01 and APP01 are in same domain. I wanted to have DB in DB01 machine and required to point to APP01 for the application related. Please help me on this to make my distributed server setup.

2) while installing SCCM on APP01 I need to point to DB01 for database.. Where do I need to mention full server name for referring this.. Please guide me on navigating the line number in the program.

Share this post


Link to post
Share on other sites

Hi , while installing SQL server I am getting this error 

The credentials you provided for the 'SQLAgent$SCCMSQL' service is invalid.. Please help me on this ..

Virtual Account 'NT Service\SQLSERVERAGENT' provided for 'SQLAgent$SCCMSQL' is either not a valid account or cannot be used for this service.

Share this post


Link to post
Share on other sites

might be nice of you to include what you did to solve your own issue in case someone else has the same problem later on.

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...