Installing SQL Server 2016 on Server Core

One of Microsoft’s best practices for deploying SQL Server is to install it on Windows Server Core. Windows Server Core provides a more streamlined, efficient and secure platform than the full Windows Server implementation with the GUI. Even so few businesses have chosen to implement SQL Server on Server Core and there are a few good reasons for this. First, the installation process is different. Since Server core doesn’t support a full GUI interface you can’t use the standard SQL Server Installation Center to install SQL Server on Server Core. Next, the configuration and management are different. You can’t install SQL Server Management Studio (SSMS) on Server so you have to manage the SQL Server instance remotely oy by using PowerShell which most DBAs are not really all that familiar with. In this article you’ll see how you can install SQL Server 2016 on Windows Server Core.

Benefits of Running SQL Server on Server Core

There are several important benefits for running SQL Server on Server Core. The four biggest benefits are:

• More secure – Using Server Core reduces the overall attack surface significantly by eliminating the UI, the browser, many applications and services.
• Less Patching – Less system code also reduces patching significantly. Some Microsoft estimates show that Server Core requires 35-45% fewer patches than a full installation.
• Less Overhead – Service Core runs with less than 40 services running compared to the 60 or 70 or even more services running in a full installation. Plus, Server Core only requires about 2 to 3 GB of HD space.
• Less management — Because there are far fewer applications and services installed there is less for IT to manage.

With Windows Server 2012 you can easily switch back and forth between Server Core and a full Windows Server installation. As of Windows Server 2016 TP5 that’s nots not the case. With Windows Server 2016 TP5 (and probably the final release) you need to choose between Server Core and a full installation when the operating system is initially installed.

Limitations

Like you might expect not all of the SQL Server 2106 subsystems and features are supported on Windows Server Core. The following is the list of SQL Server 2016 features that are not supported:

• Reporting Services
• SQL Server Data Tools (SSDT)
• Distributed Replay Controller
• Master Data Services
• Data Quality Services
• Client Tools Backward Compatibility
• Client Tools SDK
• SQL Server Books Online
• Management Tools – Remote Only

Requirements

You can install SQL Server 2016 on a Server Core installation of Windows Server 2008 R2 SP1, Windows Server 2012 or Windows Server 2016 (as of the time of this writing is currently at TP5 but scheduled to be generally available in September 2016). The following software components are also required.

• .NET Framework 2.0 SP2 –Included in the Server Core installation of Windows Server 2008 R2 SP1 and Windows Server 2012. If it is not enabled, setup enables it by default.
• .NET Framework 3.5 SP1 Full Profile — Included in Server Core installation of Windows Server 2008 R2 SP1. If it is not enabled, setup enables it by default.
• .NET Framework 4 Server Core Profile — For all editions of SQL Server 2016 except SQL Server Express, setup installs the .NET Framework 4 Server Core Profile.
• Windows Installer 4.5 — Shipped with the Server Core installation of Windows Server 2008 R2 SP1 and Windows Server 2012.
• Windows PowerShell — Shipped with Server Core installation of Windows Server 2008 R2 SP1 and Windows Server 2012.

Installation

To install SQL Server 2016 on Windows Server Core you either need to use a command-line installation or you need to use a configuration file.  Using the command-line is the most straightforward method and that’s what I will illustrate in this article. Using a configuration file is a good choice when you want to perform standardized installations across multiple systems.

To install SQL Server 2016 on Server Cover first mount the installation media and then you can run the SQL Server 2016 setup.exe program from the command line. The following sample command illustrates performing a command line installation for SQL Server 2016.

D:\>  Setup.exe /QS /ACTION=Install /FEATURES=SQL,Replication,IS /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT=”NT Authority\System” /SQLSYSADMINACCOUNTS=”WS2016ServerCor\Administrator” /AGTSVCACCOUNT=”NT AUTHORITY\Network Service” /SECURITYMODE=SQL /SAPWD=”MYSt0n6Pw9″ /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS=1

The /QS switch indicates that the setup will shows progress through the UI but will otherwise be unattended. The /ACTION switch is required and the value Install indicates SQL Server will be installed. The /FEATURES switch governs which features will be installed. The value of SQL, Replication and IS indicate that the SQL Server relational engine, replication and Integration Services will be installed. Here, the /INSTANCENAME parameter is used to specify the default instance of MSSQLSERVER. The /SQLSVCACCOUNT specifies the account that the SQL Server service will run under. The /SQLSYSADMINACCOUNTS provision logins to be members of the sysadmin role. The /AGTSAVCACCOUNT specifies the account for the SQL Agent to run under.  The optional /SECURITYMODE parameter uses the value of SQL to indicate the both Windows and SQL Server authentication will be supported. The /SAPWD parameter provides a password for the SA account. The /TCPENABLED=1 parameter enables the TCP/IP protocol. Finally, the /IACCEPTSQLSERVERLICENSETERMS=1 value is required to accept the license and perform an unattended installation.

Bear in mind that this is just an example. Naturally, you can change these values to fit your own organization’s needs. For more complete information about how to install SQL Server 2016 from the command-line you can refer to Install SQL Server 2016 from the Command Prompt.

Following the installation, you can run the following PowerShell command on Server Core to verify the installation.

Get-ItemProperty HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\* | Select-Object DisplayName, DisplayVersion,Publisher, InstallDate | Format-Table –AutoSize

You can run sconfig.exe from Server Core to get the system name and configuration values. Then you can then connect to your new SQL Server 2016 Server Core instance using SQL Server Management Studio (SSMS) from a networked system.