This post will cover the installation of SQL Server 2017 for SCCM. Few things we need to keep in mind before performing the installation.
- SQL Server Reporting Services (SSRS) is no longer part of SQL Server installation setup. We need to install SSRS separately.
- The same is true for SQL Management Studio. Starting onwards SQL Server 2016, SQL Management studio is not part of the standard installation, hence we also need to install this separately.
There are more steps to cover while installing SQL Server 2017 unlike previously when standard installation was enough to configure everything.
Lets start with the Standard SQL Server 2017 setup
ISO can be downloaded from following link: https://www.microsoft.com/en-au/sql-server/sql-server-downloads
Once mounted (or extracted), launch the setup.exe
On left pane select “Installation”, on right pane click on “New SQL Server stand-alone installation or add features to an existing installation”
Select “evaluation” edition or provide product key if you have it.
Select “I accept the license terms” and click on next.
You can use Microsoft Update to look for latest updates.
This will verify the rules to make sure you are ready to run the installation. Make sure appropriate Windows Firewall ports are opened.
Various Inbound TCP / UDP ports are required to be opened, such as ports 1433, 1434, 4022, 135.
If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433.
For more information check this page.
Ports required for SQL and Powershell command to enable it:
New-NetFirewallRule -DisplayName “SQL Server” -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName “SQL Admin Connection” -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Database Management” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Service Broker” -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow
New-NetFirewallRule -DisplayName “SQL Debugger/RPC” -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow
Following features needs to be selected:
- Database Engine Services
- Full-Text and Semantic Extractions
Right Pane will show you the require prerequisites whether they are installed or going to be installed as part of installation. We can see the Microsoft .Net Framework 4.6 is showing as installed as I did it previously or else it would have shown error.
We can use Default Instance or Named Instance, recommended way is to go with Named Instance as we can host multiple instances on same SQL Box. Lets name it as SCCM2.
We need to specify the Service Accounts. Recommended way in production environment is to have service accounts created for “SQL Server Agent” & “SQL Server Database Engine”. For the sake of lab environment, I am proceeding with default settings. SQL Server configuration manager to manage the passwords of the SQL Service account.
Click on tab “Collation” and make sure “SQL_Latin1_General_CP1_CI_AS” is selected.
Under Tab “Server Configuration”, click on “Add Current User” for SQL Server administration.
Click on tab “Data Directories”, we will see default values as shown below. Best practice to have all these configured on 4 different drives.
Ideally Data root directory, database directory & database log directory should be on a different drive.
Click on “Install” for actual installation to begin.
We are done with the SQL Server installation. We will now proceed with SQL Management Studio
SQL Management Studio
This can be downloaded from following link:
The latest version at the time of writing this blog is 17.9
Once downloaded, launch the setup.exe
Once installation is completed, you can launch the “Microsoft SQL Server Management Studio” which will be displayed under “Microsoft SQL Server Tools 17”
We need to specify the Server Name. For Default Instance, we need to just specify Server name only.
But we selected Name Instance during installation. Hence we need to select “SCCM02-TP\SCCM2”
Where SCCM02-TP is Server Name and SCCM2 is Named Instance.