How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1

Let’s try to learn How to Install SQL Server as part of SCCM Migration SQL Server – Installation | Part 1. I recently received a lot of inquiries from admins regarding the upgrade of their ConfigMgr (SCCM|MEMCM) hierarchy. SCCM Migration SQL Server step-by-step guide.

Most of their organizations want to upgrade/migrate their hierarchy to ConfigMgr Current Branch’s latest version. Since most of the existing ConfigMgr 2012 environment runs on Windows Server 2008 R2, the upgrade needs to be planned properly.

Related Post New ConfigMgr Primary Server Installation Step by Step Guide | SCCM

Introduction – How to Install SQL Server as part of SCCM Migration SQL Server Installation

As of writing this post, SQL 2017 is the latest version, ConfigMgr Current Branch 1702 is the baseline version, and Windows Server 2008 R2 is not supported for site servers or most site system roles but does remain supported for the distribution point site system role (including pull-distribution points, and for PXE and multicast).

Patch My PC

You can check more on the depreciated OS for site system roles and the Current Branch support lifecycle.

In the next few weeks, I will be covering the SCCM role migration from one server to another in my next series of posts. Topics that will be covered are as below.

  1. The Complete Guide for SCCM Server Migration Part 1 – SQL 2017  -SCCM SQL Server 2017 Installation and best practices
  2. The Complete Guide for SCCM Server Migration Part 2 – Database Migration
  3. The Complete Guide for SCCM Server Migration Part 3 – WSUS Server Migration
  4. The Complete Guide for SCCM Server Migration Part 4 – Primary Server MigrationSCCM Site Restore without SCCM Backup

Following are the topics covered in the first part of this series. My years of experience with different customers helped me derive the best approach from the SCCM world.

Tips on SCCM SQL Server Best Practices
How to Install SQL Server 2017 for SCCM
How to Install Reporting Service for SCCM
How to Configure Report Server for SCCM

Tips on SCCM SQL Server Best Practices

Below are some of the best practices followed

64K file allocation unit size

By default, the NTFS file format uses 4K file allocation units. Since the SQL stores data in pages and extents, which is of size 8K and 64K, respectively.

It’s recommended to use a 64K file allocation size to format the drives, which will hold the SQL Databases, including tempdb.

How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 1

ConfigMgr Database Sizing

Below is a rough estimate for configuring the ConfigMgr database size based on the initial sizing and number of clients. Make sure you forecast the asset count increase for the next 5 years and include it in the total number of clients

  • 250 MB (initial DB size) + (x * 6MB) – Where x is the number of clients the site server manages

For Example, if an Organization has 10K workstations, including the growth for the next 5 years, the ConfigMgr DB size will be as below:

  • Data File = 250 + 10,000*6 = 60250 MB
  • Log File = 30% of Data File =  18075 MB

Its always recommended to pre-create the DB file

TempDB best practice

Tempdb files should be placed on a dedicated drive. Suppose your ConfigMgr hierarchy is huge with Central Administration Site and multiple primaries with SQL replication playing an important role. In that case, it’s recommended to place the tempdb on a drive with high IOPS.

Tempdb should be sized at 25-30% of ConfigMgr DB.

The latest version of SQL Setup adds multiple equally sized tempdb data files during a new instance installation. By default, setup adds as many tempdb data files as the logical processor count or eight, whichever is lower.

Pag File Sizing

Pagefiles and server RAM are used as virtual memory, and it’s recommended to set a MIN and MAX page file size.

  • MIN = 1 X RAM on Server
  • MAX = 3 X RAM on Server

In my lab server with 2 GB RAM, below is the configuration.

How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 2

Memory Configuration

By default, SQL will consume 100% of the memory available on the server. If ConfigMgr DB resides on the Primary site, then restrict max memory to 75% of total RAM and if ConfigMgr DB is on a remote SQL server, then restrict max memory to 80-90% of total RAM.

AV Exclusions

AV exclusions play an important role in SQL performance. Its recommended excluding the SQL process and DB files from Real-time monitoring for better performance. However, if these files get infected, they won’t be identified by your antivirus. Below are the exclusions for SQL Server 2017.

%ProgramFiles%\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSRS14.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe
SQL Server data files (.mdf, .ndf, .ldf files)
SQL Server backup files (.bak, .trn files)
Trace files (.trc files )

SQL Service Account

As a SQL Security best practice, using a service account for SQL and Reporting Service is recommended. Use SQL Server configuration manager to manage the passwords of the SQL Service account.

How to Install SQL Server 2017 for SCCM

Beginning with SQL Server 2017, SSRS installation moved out of the standard SQL server installation to a lightweight installer. The major advantage I see here is that now Reporting services can be upgraded without impacting the SQL Server database engine.

Below are the steps involved in installing a stand-alone SQL 2017 instance.

  1. As soon as you start the SQL 2017 setup,
  2. Just click on the installation tab on the SQL Server Installation Center
  3. And then on New SQL Server stand-alone Installation.
How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 3
  • 2. Enter the product key,
  • accept the agreement,
  • install the setup files,
  • and run the pre-requisite rules.
  • Proceed to the next window once you get all rules passed
How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 4

3. In Feature Selection, you need to select:

  • Database Engine Services.
  • Client Tools Connectivity.

NOTE! – From SQL 2017 onwards, Reporting Services moved out of the SQL Setup; you need to download from Microsoft Download Center. More details on Reporting Service are found

How to Install SQL Server as part SCCM Migration SQL Server - Installation | Part 1 1
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 5
How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 6

4. Select Default Instance as a part of Instance Configuration.

I won’t prefer installing ConfigMgr on SQL with multiple instances as it’s really hard to troubleshoot the performance bottlenecks. ConfigMgr license comes with standard SQL included, so there is nothing to worry about the SQL licensing.

5. Provide the SQL Service account details(recommended), or else leave it on the default SYSTEM Account.

How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 7

6. Provide the Collation SQL_Latin1_General_CP1_CI_AS

How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 8
How to Install SQL Server as part SCCM Migration SQL Server - Installation | Part 1 2
How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1 9

7. Select Windows Authentication and provide SQL Service account and SQL DBA security group Administrator access.

How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 10

8. Select the Data directories based on your drive configurations. Recommended placing on different drives.

9. Place tempdb on a separate drive with high IOPS, and the setup will detect the number of files based on the logical processor on the server or 8, whichever is lower.

Since my test server has one logical processor, it has taken the number of files as 1. Also, make sure you do the tempdb sizing as I mentioned earlier for better performance.

How to Install SQL Server as part SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 11
The complete guide for sccm server migration
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 12

10. Once the setup is complete, you will get a successful installation window.

11. Reboot the server and Install SQL Server Management Studio separately by downloading

How to Install Reporting Service for SCCM

Download the SQL Server 2017 Reporting services installer

  1. Run the SQL Server Reporting Services set up to land on the welcome page.
The complete guide for sccm server migration
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 13
The complete guide for sccm server migration
How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1 14
The complete guide for sccm server migration
How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1 15
  • 3. Select Install Reporting Services only and complete the setup.
How to Install SQL Server as part SCCM Migration SQL Server - Installation | Part 1 3
How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1 16

You may need to reboot the server after configuring the Report Server.

How to Configure Report Server for SCCM

Let’s learn How to Install SQL Server as part of SCCM Migration SQL Server – Installation. In this part, we will learn about the SCCM report server.

  1. Open the SSRS Configuration page and connect to the Report Server Instance.
The complete guide for sccm server migration
How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1 17
The complete guide for sccm server migration
How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1 18
  • 2. Select the Service Account tab and click on use another account if you use a service account for SSRS. Provide the Service account and password and click on apply.
  • 3. Select the Web Service tab and verify the default provided values. If you wish to make any changes to the Report Server URL, modify it according to your requirements and click Apply to save the settings.
How to Install SQL Server as part of SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 19
  • 4. Select the Database tab on the left and then click on Change Database on the right to create a new Report Server Database in the SSRS configuration window.

a. Click on the test connection tab to check the connectivity to the SQL database engine and proceed to the next step on the success

How to Install SQL Server as part of SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 20

b. Provide the Report Server database name if you want custom naming. Else, leave it default, and the name will be ReportServer 

The complete guide for sccm server migration
How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1 21
How to Install SQL Server as part of SCCM Migration SQL Server - Installation
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 22

c. Service Credential will show up on the credentials page since I have used the service account. You don’t need to provide the password again if you have already configured the service account initially.

How to Install SQL Server as part SCCM Migration SQL Server - Installation | Part 1 4
How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1 23

d. Finally, you will see the summary page where you can verify the details provided earlier before the SSRS database is created.

e. On proceeding further, the SSRS DB is created, and appropriate permissions are set.

The complete guide for sccm server migration
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 24
The complete guide for sccm server migration
How to Install SQL Server as part of SCCM Migration SQL Server – Installation 25

5. Back up the SSRS encryption key (.snk file and password) in a safe location to restore the SSRS in case of any failure.

Author

Rajul is a Technical Architect with more than 12 years of experience as an Endpoint Manager Configuration Manager and has hands-on experience in SCOM, SCVMM, SCEP, SQL, Azure, Intune, etc. His main area of interest is the design and implementation of ConfigMgr and OpsManager Infrastructure. He has a vast knowledge of ConfigMgr infrastructure & Client-side troubleshooting.

22 thoughts on “How to Install SQL Server as part SCCM Migration SQL Server – Installation | Part 1”

  1. When are you going to do that next post on moving the DB from an older version to SQL 2017? Interested to see what you put together.

    Reply
  2. So what you mean is install a new instance of SQL 2017 and then move the DB to SQL 2017?

    Any possibility to directly upgrade SQL 2016 SP1 to SQL 2017? I am aware RS is separate in SQL 2017.

    Thanks

    Ram

    Reply
    • I have not tested the in-place upgrade of SQL 2016 SP1 to SQL 2017 with ConfigMgr DB. Logically it should work and the only difference would be to upgrade the SSRS separately from DB engine upgrade.

      Reply
  3. Hello, Rajul!
    Is it to possible to the in-place upgrade OS Windows 2012 R2 -> Windows 2016 on Site Server?
    Or needed to make a new site and migration?

    Reply
    • It is possible, just make sure you do a full site backup from within SCCM, then do a complete site stop.

      Once the OS is upgraded, I had to run ConfigMgr setup and run “Perform Maintenance or reset this site” so that all the components were re-initialised – not doing this causes many headaches..

      I had some issues afterwards where several windows services failed to autostart but this could be my environment

      Reply
      • Yes, it is possible. But from my past experience, I would say to restore the site on a fresh machine with fresh OS installation. By doing this, you are not carrying the rot from the old server. Obviously, you will have to make some extra effort but it will be worth and will have a problem-free environment.

  4. hi,
    I have primary server with remote sql server with Sccm cb 1710,now I want to migrate the sql server from physical to virtual.what are the best practices for p2v?what are the precautions need to be taken?

    Reply
    • P2V is ok. I used P2V in the environment where I work. In new world P2V is so normal and you don’t need to worry about that at all.

      If you would have asked this question 6-7 years before then, the answer could be different

      Reply
  5. You say “ConfigMgr license comes with standard SQL included, so nothing to worry about the SQL licensing.” This article details the installation of SQL 2017 Std, yet in VLSC the only SQL option is 2016 Std. Do you have any further detail to shed on this?

    Reply
  6. Hi Anoop. You mention “ConfigMgr license comes with standard SQL included, so nothing to worry about the SQL licensing.”

    In this article you are installing SQL 2017, yet in VLSC only SQL 2016 is available with ConfigMgr. Can I assume you have purchased SQL 2017 for other means and have this available to you? Otherwise you could only be running it in eval.

    Reply
  7. Hi
    i have different drives setup as suggested earlier on site server:
    C : OS = 150
    E: SCCM = 200 GB
    F: SQL Database =100 GB
    G: SQL TempDB = 50 GB
    H: SQL Logs = 50 GB
    How can i setup
    -root and shared feature directories on “Features Selection “Tab,
    -data directories and temp db directories on “Database Engine Configuration” Tab
    Could you please guide me through this?

    Reply

Leave a Comment

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