- Windows 11 taskbar: remove chat icon, customize search field with Group Policy - Tue, Oct 3 2023
- Allow non-admins to access Remote Desktop - Thu, Sep 28 2023
- Which WSUS products to select for Windows 11? - Tue, Sep 26 2023
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
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
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
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.
.\setup.exe /UIMODE=EnableUIOnServerCore
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
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:
TCPEnabled=1
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:
Get-CimInstance Win32_product | ? Name -Like *SQL* | select Name, Caption
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:
Set-Service -Name SQLBrowser -StartupType Automatic Start-Service -Name SQLBrowser
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:
EXEC sys.sp_configure N'remote access', N'1' GO RECONFIGURE WITH OVERRIDE GO
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:
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action allow
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
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.
Subscribe to 4sysops newsletter!
Logging on to SQL Server using SQL Management Studio
Log in with the account and the authentication method you previously specified during setup.
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!
Thiago, thanks! That was my bad. I missed a line break. I fixed the command now.