Install SQL Server 2019 (Express) on Windows Server Core and activate remote management

Microsoft recommends Windows Server Core for most infrastructure services and for applications such as Exchange 2019. You can also run SQL Server on Server Core. However, the installation requires a few steps from the command line.

Even if Server Core does not support all SQL Server services, such as reporting, master data, or data quality services, such a setup should still be sufficient for most tasks. This is especially true for the typical applications of the Express Edition.

Minimal installer for the GUI ^

The installation has not changed since SQL Server 2017, so the following instructions apply equally to both versions. It starts by fetching an installer smaller than 6 MB from the SQL Server 2019 download page, which in turn will request the required files from the internet.

The tool also starts with a graphical interface under Server Core and offers three options: Basic, Custom, and Download Media.

Installation options in the graphical setup for SQL Server 2019

Installation options in the graphical setup for SQL Server 2019

The first variant is straightforward: after you confirm the end-user license agreement (EULA) and select the target directory, the installer sets up SQL Server with the default values. This setup is limited to the database engine only.

After selecting the installation directory, the standard setup will start

After selecting the installation directory, the standard setup will start

If you choose the user-defined installation, the program downloads the installation files and then opens the actual SQL Server setup. Here you can select the various options and components with the help of a wizard.

GUI wizard does not work ^

The download of the complete installation media will be the preferred option if the database server does not have access to the internet. You can download the files to a workstation and then transfer them to the target system.

Download the installation media for SQL Server

Download the installation media for SQL Server

Executing the .exe file then unpacks the archive into a subdirectory from where you can run setup.exe.

The command below opens the same GUI you get during the user-defined installation.

The wizard led installation of SQL Server does not work under Server Core

The wizard led installation of SQL Server does not work under Server Core

In both cases, this proves to be unusable under Server Core. In both Server 2016 and Server 2019 with Core App Compatibility installed, clicks on various options yielded no effect in my lab.

Installation from the command line ^

So if you don't want to use the standard installation, the only other option is to set up SQL Server from the command line. The setup provides a silent mode for this, which you can activate with the /Q switch.

Installing the SQL engine requires the Action parameter (with the possible values install, uninstall, or upgrade) and IAcceptSQLServerLicenseTerms. Also, you must pass values for Features to the setup. Possible values are SQLENGINE, FullText, Replication, AS, IS, and Conn. If you specify several of them, you need to separate them with a comma.

Running setup.exe to install SQL Server 2019 Express

Running setup.exe to install SQL Server 2019 Express

Other mandatory parameters include Instanceid and Instancename (both have the default value MSSQLSERVER) as well as Sqlsvcaccount and Sqlsvcpassword, with which you specify the account or its password under which SQL Server will run.

You can install the Express Edition based on these parameters, but the full version also requires that you assign the sysadmin role to certain users with Sqlsysadminaccounts. Complete documentation of all setup parameters is here on Microsoft Docs.

Completing the installation ^

If you plan to manage SQL Server remotely, you should add this parameter when installing on Server Core:

Otherwise you may have to grapple with Windows Management Instrumentation (WMI) and the PowerShell module sqlserver to activate this protocol afterward.

After the installation is finished, you can run this PowerShell command to verify whether the setup was successful:

Subsequent verification of SQL Server installation with PowerShell

Subsequent verification of SQL Server installation with PowerShell

If you need the browser service, set its startup type to automatic using PowerShell, and start the service:

Allow remote administration for SQL Server ^

In the next step, you open SQL Server for remote management using the command-line tool sqlcmd.exe installed alongside the database. If you chose the default installation at the beginning of the GUI installer, you can click the Connect button to start the program instead of launching it manually. Then enter the following commands:

Configuring SQL Server for remote access with sqlcmd.exe

Configuring SQL Server for remote access with sqlcmd.exe

Configuring the firewall ^

Finally, it is necessary to open the firewall for managing SQL Server remotely. You can do this via PowerShell:

If you have installed other services in addition to the SQL engine, you can configure the firewall for them using this script by Ryan Mangan.

Opening a firewall port for SQL Server management

Opening a firewall port for SQL Server management

Now the path should be clear for the remote management of SQL Server. With SQL Management Studio, Microsoft offers a powerful tool for this purpose. It is currently available in version 18.4 and can also manage SQL Server 2019.

Logging on to SQL Server using SQL Management Studio

Logging on to SQL Server using SQL Management Studio

Log in with the account and the authentication method you previously specified during setup.

Want to write for 4sysops? We are looking for new authors.

Read 4sysops without ads and for free by becoming a member!

2+
Share
2 Comments
  1. Thiago Andrade 6 months ago

    Hi!

    Thanks for the article. Very good!

    I found a wrong command. The following command is invalid:

    Set-Service -Name SQLBrowser -StartupType AutomaticStart-Service -Name SQLBrowser

    Must be:

    Set-Service -Name SQLBrowser -StartupType Automatic

    Tanks!

    0

Leave a reply to Michael Pietroforte (Rank: Level 4)
Click here to cancel the reply

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

*

© 4sysops 2006 - 2020

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account