Installing MS SQL Server

MS SQL is a component required by the ConfigMgr server. This prerequisite must be met regardless regardless of the server type. ConfigMgr requirements for MS SQL are not that demanding. Even when planning to support a few thousands of clients, installing MS SQL Server with default settings should be fair enough. There is no need for SQL to be placed on a separate disk array. There is no need for splitting the roles of ConfigMgr and SQL on two separate machines.

ConfigMgr 1706 can use the SQL installed, starting from version 2014.

The following steps will show you how to install SQL in version 2016:

1. Run the MS SQL Server installer and choose the Installation option.
2. After choosing the Installation option, an additional window appears with installation modes.
3. Choose New SQL stand-alone installation or add features to an existing installation. The ConfigMgr server supports the SQL installed in the failover cluster; however, this installation type is out of the scope of this book:

1. At the beginning of the installation, we need to provide the MS SQL product key.
2. The next step is to accept the ConfigMgr license terms.

1. After accepting the license terms, the installer checks whether there are any updates for the installer itself. When found, it downloads and installs them. In the next steps, it checks whether there are any problems that might prevent MS SQL Server from being installed:

1. If there are no impediments, the installer will go further. In the next step, we decide what MS SQL components should be installed and in which folders; ConfigMgr requires the following components to be installed: 
   • Database Engine Services
   • Reporting Services - Native

1. It is not possible to install MS SQL Server Management Studio along with MS SQL Server. This needs to be downloaded and installed separately:

1. After deciding and choosing the required SQL Server components, we need to decide about the SQL instance name:

1. In the next step, we define an account that will be used by MS SQL Server. We define it on the Service Accounts tab:

This is the moment when we need to remember about the Collation tab:

The ConfigMgr server supports only the SQL_Latin1_general_CP1_CI_AS collation. If we install SQL Server using a different collation, ConfigMgr will not be able to go through the installation process.

1. After choosing the Service Accounts and Collation on the Server Configuration tab, we need to choose the way of authentication that will be used for the SQL Server as well as the accounts we will use for administration:

1. On the Data Directories tab, choose the folders in which SQL Server will create the database and log files:

1. Choose the Reporting Services to be installed. During the installation, we have a choice to either install it or install and configure it automatically during the installation process. Choose Install and configure:

1. The last step is a summary of our configuration of SQL Server. After checkouts, if all the settings are as intended to be, we may start the installation process.

1. The installation of SQL Server shouldn't take long. It is important to check whether Reporting Services works properly, as it often happens that it is somehow not configured correctly. To do the checkout, simply enter the URL http://cm16/reports and check whether the website looks similar to the following one:

1. After installing server roles and all features, running Windows Update and installing all the pending updates is highly recommended.