Install SCCM Current Branch – Part 9 – Prerequisites (SQL Server)

In the two previous posts, I installed the Windows Features and the Windows ADK as part of the prerequisites to install SCCM. In this article I will install SQL Server 2016 SP1, configure its memory and open the necessary firewall ports for intra-site and inter-site replication.

NOTE: This should be done at the site server (SCCM Server) using the SCCMAdmin account, created in the step two of the installation guide.

If you’re thinking on getting your SCCM Current Branch Certification, consider reading:

Lets’s Start!

Downloading

Follow this link and download the setup file for the evaluation version of SQL Server 2016 SP1.

Installing SQL Server 2016

Before beginning remember that this operation should be done at the SCCM Site Server using the SCCM Admin account.

Browse to the location where you downloaded SQL Server setup executable and run it.

At the initial screen choose “Custom”.

SCCM - Install SQL Server 2016

 

Specify where you want to download the installation media to. I chose to download to my data drive, which is drive F. Press “Install“.

SCCM - Install SQL Server 2016

 

Wait while the SQL Server setup downloads the installation files.

SCCM - Install SQL Server 2016

 

Choose “Installation” and then “New SQL Server stand-alone installation or add features to an existing installation”.

SCCM - Install SQL Server 2016

 

Choose “Evaluation” and press “Next“.

SCCM - Install SQL Server 2016

 

Read and accept the License Terms and press “Next“.

SCCM - Install SQL Server 2016

 

Choose whether or not to check for updates and press “Next“.

SCCM - Install SQL Server 2016

 

Most probably you’ll get a warning about “Windows Firewall” at the “Install Rules” screen. If you click “Warning”, you’ll see a message about the correct ports for SQL Server being opened in Windows Firewall. We will take care of that later. You can press “Next”.

SCCM - Install SQL Server 2016

 

From the “Feature Selection” window, we will only need the “Database Engine Services” and the “Reporting Services – Native“. Select them and press “Next“, leaving all other options as default.

SCCM - Install SQL Server 2016

 

Leave the “Default Instance” selected and press “Next“.

SCCM - Install SQL Server 2016

 

Set all SQL services to run with the SCCM Admin account and set them to start automatically. Move on to the “Collation” tab.

SCCM - Install SQL Server 2016

 

On the “Collation” tab, set the Database Engine collation to SQL_Latin1_General_CP1_CI_AS. Press “Next“.

SCCM - Install SQL Server 2016

 

On the “Database Engine Configuration” screen, leave “Windows authentication mode” selected and press the “Add Current User” button, to add the SCCM Admin user to the list o SQL Administrators. Leave all the other tabs untouched. Press “Next“.

SCCM - Install SQL Server 2016

 

Choose “Install only” and press “Next“.

Install SQL Server 2016

 

Finally, press “Install“.

Install SQL Server 2016

 

Wait for the installation to finish.

Install SQL Server 2016

 

Press “Close” and the end of the installation.

Install SQL Server 2016

 

Installing SQL Server Management Studio

The next step is to install the SQL Server Management Studio, as SQL Server 2016 setup does not automatically install it.

Download it from: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

 

SQL Server Memory Configuration

The SCCM setup will check if the SQL allocated memory is set to at least 8GB and if it is set to less, it will warn us. Not only to avoid this warning but because, according to Microsoft, the recommended allocated memory for SQL Server is 80% of the server’s available physical memory, I’m going to set the SQL memory setting.

To do this, open SQL Server Management Studio and connect to your database server.

Right-click the server node and choose “Properties“.

Install SQL Server 2016

 

Select the “Memory” node and set the “Minimum server memory” to 8192 MB and the “Maximum server memory” to 80% of the server’s physical memory. In my case I set it up to 8192 MB because I only have 8 GB of RAM in my virtual machine. Press “OK” and it’s done.

Install SQL Server 2016

 

Opening Windows Firewall ports for SQL Server

Press the “Start” button and type WF on the search box. Select “Windows Firewall with Advanced Security“.

Install SQL Server 2016

 

Right-click “Inbound Rules” and select “New Rule…“.

Install SQL Server 2016

 

Select “Port” and press “Next“.

Install SQL Server 2016

 

Type 1433 at the “Specific local ports” field and press “Next“.

Install SQL Server 2016

 

Press “Next” at the “Action” screen.

Install SQL Server 2016

 

Leave only the “Domain” checkbox selected and press “Next“.

Install SQL Server 2016

 

Name the rule and press “Finish“.

Install SQL Server 2016

 

Repeat the above steps for port 4022.

You could also achieve this by using GPO. I might write an article on it.

 

And this concludes the installation of SQL Server at our SCCM Site Server machine. In the next post I will install WSUS.

 

Recommended reading:

 

Further reading:

https://docs.microsoft.com/en-us/sccm/core/plan-design/configs/support-for-sql-server-versions

https://technet.microsoft.com/library/ms191144(SQL.105).aspx

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-windows-firewall-for-database-engine-access

https://docs.microsoft.com/en-us/sccm/core/plan-design/configs/recommended-hardware

 

As always, if you found this article useful, share it with your friends.

If you have any questions or suggestions, leave your comment.

Thank you for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

By continuing to use the site, you agree to the use of cookies. More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" then you are consenting to this. To know more please read here our Privacy Policy

Close