- How to use delivery optimization with WSUS - Mon, Jul 13 2020
- ESXi 7.0 Free: New features, limitations, upgrade - Mon, Jul 6 2020
- Update baseline: Microsoft's recommended GPO settings for Windows updates - Wed, Jul 1 2020
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.
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.
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.
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.
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.
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:
Get-CimInstance Win32_product | ? Name -Like *SQL* | select Name, Caption
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'
RECONFIGURE WITH OVERRIDE
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.
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.
Log in with the account and the authentication method you previously specified during setup.