DB-유

Installing SQL Server 2019 on Windows Server 2019

· mssql, sql server 2019, installation, windows server

SQL Server 2019 went GA at the start of the month, and I rebuilt my lab on Windows Server 2019 the same evening to see what changed in setup. Not much, which is good, but there are a few new pages, a Java dependency if you pick certain features, and a new SSMS.

My box is a fully patched Windows Server 2019 with 6 GB or more free on the system drive and separate volumes for data, log, tempdb and backups. Setup needs .NET Framework 4.7.2, which Server 2019 already has. I use Developer edition (free, identical to Enterprise for non-production); for production you enter a Standard or Enterprise key. Only bother with a domain service account if you need network share backups or Availability Groups; virtual accounts are otherwise fine.

Start setup and choose the edition

Mount the ISO and run setup.exe. On the Installation page choose New SQL Server stand-alone installation or add features to an existing installation. On Product Key pick Developer from the free edition list or enter your key. Accept the licence terms and let the Install Rules run; the Windows Firewall warning is expected.

If you plan to repeat the install, the unattended equivalent for the settings below is:

D:\setup.exe /Q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER `
  /SQLSVCACCOUNT="NT Service\MSSQLSERVER" /AGTSVCACCOUNT="NT Service\SQLSERVERAGENT" /AGTSVCSTARTUPTYPE=Automatic `
  /SQLSYSADMINACCOUNTS="CONTOSO\DBA-Team" /SQLSVCINSTANTFILEINIT=True /TCPENABLED=1 `
  /SQLUSERDBDIR="D:\SQLData" /SQLUSERDBLOGDIR="L:\SQLLogs" /SQLBACKUPDIR="B:\SQLBackup" `
  /SQLTEMPDBDIR="T:\TempDB" /SQLTEMPDBFILECOUNT=4 /SQLTEMPDBFILESIZE=1024 /SQLTEMPDBFILEGROWTH=512 `
  /SQLMAXMEMORY=24576 /SQLMINMEMORY=0 /SQLMAXDOP=4 /IACCEPTSQLSERVERLICENSETERMS

/SQLMAXMEMORY, /SQLMINMEMORY and /SQLMAXDOP are new parameters in 2019 and correspond to the new setup pages described below.

Feature selection

Tick Database Engine Services. Leave the rest unticked unless you need it:

  • Machine Learning Services now includes Java alongside R and Python, and PolyBase Query Service for External Data is where the new data virtualisation connectors live. Both pull in additional runtimes.
  • Java – if you choose PolyBase or the Java language extension, setup offers to install the bundled Azul Zulu OpenJDK 11 or to point at an existing JRE. Selecting the bundled one is the least trouble.
  • Full-Text and Semantic Extractions for Search is still optional and still worth having if any application uses CONTAINS.

SSMS and SSDT are not on the media; SSMS 18.4 is a separate download and is the version that recognises the 2019 features.

Instance and server configuration

Keep the default instance unless you already have one on the box. On Server Configuration:

  • Set SQL Server Agent startup type to Automatic.
  • Tick Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service for instant file initialisation.
  • On the Collation tab, SQL_Latin1_General_CP1_CI_AS remains the default; if you want proper UTF-8 support for varchar columns, 2019 adds _UTF8 collations such as Latin1_General_100_CI_AS_SC_UTF8. I still set that per database rather than at the instance level.

Database Engine configuration

This page has grown a lot. The tabs are:

  • Server Configuration – authentication mode and sysadmin accounts. Add your DBA group.
  • Data Directories – put data, log and backup on their own volumes.
  • TempDB – number of files (defaults to the core count up to 8), size and growth. I set 4 to 8 files of 1024 MB each with 512 MB growth on the tempdb volume.
  • MaxDOP – new in 2019. Setup proposes a value based on the core count and NUMA layout following Microsoft's guidance (cores per NUMA node, capped at 8). I accept it. It beats the old default of 0 by a mile.
  • Memory – new in 2019. Choose Recommended and setup calculates max server memory leaving room for the OS. On a 32 GB box it suggests around 26 GB for the engine. Tick the checkbox to accept the recommended values, otherwise the default of 2147483647 MB stays.
  • FILESTREAM – leave off unless needed.

Click through to Ready to Install, note the ConfigurationFile.ini path, and install. A restart is not usually required on a fresh server.

SSMS 18.4 and checking the build

Run SSMS-Setup-ENU.exe. SSMS 18 is a smaller install than 17 and no longer supports SQL Server 2000/2005 connections, which is irrelevant on a new server. After installing, check the build:

SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion') AS version, SERVERPROPERTY('ProductUpdateLevel') AS cu, SERVERPROPERTY('Edition') AS edition;

RTM is build 15.0.2000.5. Cumulative updates for 2019 will arrive on the usual cadence; apply them as they come, as there is no Service Pack model any more since 2017.

Did the new pages take effect?

SELECT name, value_in_use FROM sys.configurations WHERE name IN ('max server memory (MB)', 'max degree of parallelism', 'cost threshold for parallelism');
SELECT name, physical_name, size * 8 / 1024 AS size_mb FROM tempdb.sys.database_files;
SELECT instant_file_initialization_enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%';

Max memory and MAXDOP should reflect the setup pages, tempdb should show the file layout you chose, and instant file initialisation should be Y. Cost threshold for parallelism is still 5 by default; raise it to somewhere between 25 and 50 for most OLTP systems.

Setup problems

  • "There was an error generating the XML document" or setup hanging right after the Feature Selection page on a freshly deployed server: Windows Update is mid-installation. Reboot and run setup again.
  • "The SQL Server 2019 Java Runtime Environment requires... Java 11" (rule failure) appears if you deselected the bundled JRE for PolyBase but the JRE you pointed at is version 8. Either use the bundled Zulu runtime or install Java 11 and set JRE_HOME.
  • "Wait on the Database Engine recovery handle failed" during the final phase. Check C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\ and the ERRORLOG in the instance directory; it is typically a permission problem on a data directory or a TempDB path that does not exist.
  • Clients on older drivers cannot connect and SSMS 17 shows a warning about the version. Upgrade to SSMS 18.4 and to ODBC Driver 17 / OLE DB Driver 18 on application servers; SQL Native Client 11 still works for basic connectivity but does not know the new features.

This is the first setup where I don't need a post-install script for memory, MAXDOP and tempdb. Read the new pages instead of mashing Next.