SQL Server 2019 Installation to Setup a New Instance
This setup guide introduces SQL Server 2019 installation steps for SQL developer and database administrators. With the annoucement of SQL Server 2019 by Microsoft during Ignite 2018 event, the setup media or installation files are released available for public use at the same time. So all SQL Server Data Platform professionals have the chance to download and install SQL Server 2019 to try and evaluate new features developed and shipped with new version of SQL Server.
SQL Server Installation Center
To install SQL Server 2019, database developer and DBAs use SQL Server Installation Center which is a centralized tool for data professionals which contains resources and launches tools for specific tasks like installing a stand-alone SQL Server instance, installing SQL Server Management Studio (SSMS), setting up SQL Server Data Tools (SSDT), stand-alone Machine Learning Server installation, failover cluster installation and configuration, etc.
When I compare Installation Center for SQL Server 2019 vs SQL Server 2017, there is not a difference in this setup tool as far as I could see.
Let's have a look at what database professionals can do using SQL Server Installation Center.
Launch SQL Server Installation Center.
In Planning tab, before you install or migrate your data from a different data platform, you will find the tools to check hardware and software requirements plan configurations as well as documentation that will be useful to complete these tasks.
Installation tab provides the setup programs to install a new stand-alone SQL Server 2019 instance or updating or adding new features to an existing SQL Server instance. Again using tools from this tab, it is possible to install SQL Server Reporting Services, SQL Server Management Studio, SQL Server Data Tools, setup a failover cluster, installing a new stand-alone Machine Learning Server, etc.
Maintenance tab has the tools to upgrade SQL Server editions, repair a corrupted SQL Server instance, etc.
In Tools tab, database administrators will find System Configuration Checker software, execute a report which will discover all installed SQL Server products and features installed on each SQL Server instance. Additionally, MAP or Microsoft Assessment and Planning Toolkit for SQL Server can be launched from this section.
Resources tab mainly covers documentation for SQL Server 2019, provides links to online communities and SQL Server 2019 samples and downloads.
In Advanced tab, there is a tool enabling installers to complete a SQL Server 2019 instance setup based on a configuration file, advanced cluster options, etc.
Options tab, you can specify the SQL Server setup media file location and architecture based on processor type; x64 or x86
SQL Server Installation Step by Step using Wizard
When you launch SQL Server Installation Center switch to Installation tab.
The SQL Server 2019 installation option that I will demonstrate in this setup guilde will be a new SQL Server 2019 stand-alone instance installation.
SQL Server 2019 installation steps will follow these steps as seen on the left side of the setup guide:
Product Key, Licence Terms, Global Rules, Microsoft Update, Product Updates, Install Setup Files, Install Rules, Feature Selection, Feature Rules, Feature Configuration Rules
Then we will start and track SQL Server 2019 setup process status in following steps: Ready to Install, Installation Progress, Complete
In the first step, SQL Server data platform professional is required to provide a product key.
You can select SQL Server 2019 Developer Edition as free edition as follows:
It is possible to select SQL Server 2019 Evaluation Edition too.
The last free edition option that can be selected from dropdown list is SQL Server 2019 Express Edition
To continue with SQL Server 2019 installation, accept licence terms displayed on screen by marking the checkbox.
In Global Rules, setup wizard will check if any configuration prevents SQL Server 2019 installation.
This is a general check of operating system related tools and software that might affect SQL Server setup process
If there is no conflict, the setup wizard will contine with next step.
Here is the list of Global Rules.
GlobalRules: SQL Server vNext CTP2.0 Setup configuration checks for rules group 'GlobalRules'
NoRebootPackageDownLevel: This rule determines whether the computer has the required update package for .NET Framework 2.0 or .NET Framework 3.5 SP1 that is needed for a successful installation of Visual Studio components that are included in SQL Server.
ServerCore64BitCheck: Checks if this version of SQL Server is 64bit.
ServerCorePlatformCheck: Checks if this version of SQL is supported on the currently running Windows Server Core OS.
AclPermissionsFacet: Checks if the SQL Server registry keys are consistent.
FacetWOW64PlatformCheck: Determines whether SQL Server Setup is supported on this operating system platform.
HasSecurityBackupAndDebugPrivilegesCheck: Checks whether the account that is running SQL Server Setup has the right to back up files and directories, the right to manage auditing and the security log and the right to debug programs.
MediaPathLength: Checks whether the SQL Server installation media is too long.
NoRebootPackage: This rule determines whether the computer has the required update package for .NET Framework 2.0 or .NET Framework 3.5 SP1 that is needed for a successful installation of Visual Studio components that are included in SQL Server.
RebootRequiredCheck: Checks if a pending computer restart is required. A pending restart can cause Setup to fail.
SetupCompatibilityCheck: Checks whether the current version of SQL Server is compatible with a later installed version.
ThreadHasAdminPrivilegeCheck: Checks whether the account running SQL Server Setup has administrator rights on the computer.
WmiServiceStateCheck: Checks whether the WMI service is started and running on the computer.
A general documentation about Rules is provided by Microsoft at https://go.microsoft.com/fwlink/?LinkID=398124
It is better to check for updates from Microsoft Update before you continue with SQL Server 2019 installation.
You can simply mark the checkbox to check for updates automatically at this step or leave it unmarked to continue to install SQL Server.
Since it might take hours I will not install any updates right now so I leave the checkbox clear.
In Install Setup Files step, the installation wizard checks if there is any update to setup files.
If not, it will redirect you to next step.
In Install Rules step, the setup program checks if there is a problem that prevents SQL Server installation related with SQL Server Data Platform tools.
Here is the list of Install Rules.
InstallGlobalRules: SQL Server vNext CTP2.0 Setup configuration checks for rules group 'InstallGlobalRules'
ServerCoreBlockUnsupportedSxSCheck: Checks if an earlier version of SQL Server is currently installed.
AclPermissionsFacet: Checks if the SQL Server registry keys are consistent.
FacetDomainControllerCheck: Checks whether the computer is a domain controller. Installing SQL Server vNext CTP2.0 on a domain controller is not recommended.
IsFirewallEnabled: Checks whether the Windows Firewall is enabled.
Feature Selection screen is the step where I love most :)
As a database developer or a database administrator, you can select and choose among different SQL Server 2019 features introduced to data professional in this tab.
If you are not installing a new instance but if you want to add additional features to your existing SQL Server instance, this screen is where you will manage the abilities of your database instance.
If as a database administrator want to see what is new in a stand-alone SQL Server 2019 instance, you can compare these features list with most recent SQL Server 2017 features.
When I compare two features list from SQL Server 2019 vs SQL Server 2017, I can see that Java Connector for HDFS Data Sources is introduced for developers to query HDFS data using T-SQL commands easily.
Instance Features: The configuration and operation of each instance feature of a SQL Server instance is isolated from other SQL Server instances. SQL Server instances can operate side-by-side on the same computer.
Database Engine Services: Includes the Database Engine, the core service for storing, processing and securing data. The Database Engine provides controlled access and rapid transaction processing and also provides rich support for sustaining high availability. The Database Engine also provides support for the utility control point in the SQL Server Utility. Only Database Engine Services and Analysis Services can be clustered.
SQL Server Replication: Includes a set of technologies for copying and distributing data and database objects from one database to another and synchronizing between the databases for consistency. You can use replication to distribute data to different locations, and to remote and mobile users over local and wide area networks, dial-up connections, wireless connections and the Internet.
Machine Learning Services (In-Database): Includes extensions that enable integration with R and Python languages using standard T-SQL statements.
R: Includes extensions that enable integration with R language using standard T-SQL statements.
Python: Includes extensions that enable integration with Python language using standard T-SQL statements.
Full-text and Semantic Extractions for Search: Includes the Search engine that supports Full-Text Extraction for fast text search as well as Semantic Extraction for key phrases (likely tags) and similarity search on content stored in SQL Server.
Data Quality Services: Includes Data quality database objects.
PolyBase Query Service for External Data: Includes PolyBase technology that enables truly integrated querying across Oracle, Teradata, SQL Server and other relational and non-relational data using standard T-SQL statements.
Java Connector for HDFS Data Sources: Includes PolyBase Java Connector that enables truly integrated querying across HDFS data using standard T-SQL statements.
Analysis Services: Includes Analysis Services and tools used to support online analytical processing (OLAP) and data mining. Only Database Engine Services and Analysis Services can be clustered.
Shared Features: Each shared feature is installed once within a defined scope and operates within that scope. The defined scope can span all SQL Server versions on a computer (e.g., SQL Server Browser), can be isolated to one major version of SQL Server, or can be isolated to one or more minor versions.
Machine Learning Services (Standalone): Includes Microsoft Machine Learning Server for Windows that provides parallelized big data analysis, transformation, modeling and operationalization capabilities.
R:
Python:
Data Quality Client: Includes Data quality client objects.
Client Tools Connectivity: Includes components for communication between clients and servers.
Integration Services: Includes the designer, runtime, and utilities that enable Integration Services to move, integrate, and transform data between data stores.
Scale Out Master: Includes Scale Out Master for Integration Services Scale Out.
Scale Out Worker: Includes Scale Out Worker for Integration Services Scale Out.
Client Tools Backwards Compatibility: Client Tools Backwards Compatibility
Client Tools SDK: Includes the software development kit containing resources for programmers.
Distributed Replay Controller: Includes the Distributed Replay Controller which orchestrates the actions of the distributed replay clients.
Distributed Replay Client: Includes the Distributed Replay Client. Multiple Distributed Replay Clients work together to simulate a workload against an instance of SQL Server.
SQL Client Connectivity SDK: Includes SQL Server Native Client (ODBC / OLE DB) SDK for database application development.
Master Data Services: Includes Master Data Services, the platform for integrating data from disparate systems across an organization into a single source of master data for accuracy and auditing purposes. Installs the Master Data Services Configuration Manager, assemblies, PowerShell snap-in, and folders and files for Web applications and services.
Rredistributable Features: SQL Server redistributable and shared features are installed when needed: Error and Usage Reporting, SQL Server Native Client, MSXML version 6.0, Sync Services for ADO.NET, and SQL Server Browser.
Though the prerequisites for each selected feature changes, in general if you have following items installed before you will not have any issue during setup:
Microsoft Visual C++ 2015 Redistributable
Microsoft Visual C++ 2012 Redistributable
Windows PowerShell 3.0 or higher
Universal C Runtime in Windows
Oracle SE Java Runtime Environment Version 7 Update 51 or higher (64-bit)
Microsoft .NET Framework 4.6
Mark the feature that you want to have on your SQL Server 2019 instance and on your SQL Server machine.
While you mark the desired features, in Disk Space Requirements section, you will see the total size required on disk drives and available disk size on that drive.
Also you can configure the root directory each category of features using folder selection options.
After you selected all the features that you want on your database server, click Next.
In Feature Rules, according to the selected features following rules are validated on your server.
Standalone Rules: These rules do not belong to any rule group
KB2919355: Check if KB2919355 needs to be manually installed for Windows 8.1 or Windows Server 2012 R2
InstallFeatureSpecificRules: SQL Server vNext CTP2.0 Setup configuration checks for rules group 'InstallFeatureSpecificRules'
AS_IsDotNet45Installed: This rule determines if the .NET Framework 4.5.1 is already installed
ASIsValidEditionForDeploymentModeCheck: Checks whether the SQL Server edition supports the currently selected Analysis Services server mode.
InternetConnectionToNETFX4DownloadSite: SQL Server media for Express packages does not include Microsoft .NET Framework 4.0 package. If Microsoft .NET Framework 4.0 is not already installed on the computer, SQL Server setup for the Express packages requires connection to Microsoft .NET Framework 4.0 dowload center to download and install .NET 4.0 Framework through Web setup. Ensure internet connection to Microsoft .NET Framework 4.0 download center on this computer. If you do not want to open up the internet connection, as a workaround pre-install Microsoft .NET Framework 4.0 on this computer before running SQL Server setup.
Polybase_IsCrtVersion11Installed: Checks that Microsoft Visual C++ Redistributable 2012 is installed
Polybase_IsDomainController: PolyBase cannot be installed on an Active Directory Domain Controller
Polybase_IsPolybaseInstalled: PolyBase can be installed on only one SQL Server instance per computer
Polybase_IsSqlEngineRunning: Adding PolyBase feature to a SQL Server instance requires connection to SQL Server Database Engine on the instance
PolybaseJava_IsMinJavaVersionInstalled: Checks that Oracle JRE 7 Update 51 (64-bit) or higher is installed
PowerShellCheck: Checks whether Windows PowerShell 3.0 (or higher) is already installed on system. Windows PowerShell is needed by some SQL Server features.
ServerCoreBlockUnsupportedFeaturesCheck: Checks if all selected features are supported on Windows Server Core.
ServerCoreNetFxCheck: Checks if The Microsoft .NET Framework 4 Server Core is already installed on this computer or is available for installation.
SlipstreamMediaInfoCheck: Checks whether the language of the original media is the same language as the specified service pack.
On Instance Configuration installation step, give a name to your SQL Server instance or choose to use default instance (MSSQLSERVER) as name.
I prefer to use named instances. For example, for this version of SQL Server 2019, I used SQLServer2019CTP as instance name.
As you can see in above screenshot, in Installed Instances window there are SQL Server installations with installed features from previous setups with version and edition detail.
On Server Configuration step, you can configure service accounts and passwords, preferred startup types, etc.
An other configuration option is related with Database File Initialization where you activate by marking the checkbox "Grant Perform Volume Maintenance Task priviledge to SQL Server Database Engine Service"
On the same Server Configuration screen on Collation tab, database administrator can define a default collation for the database engine. There are numerous collations defined for SQL Server. You can choose one by using Customize... button.
On Database Engine Configuration step, DBA can define authentication mode, password for sa account, SQL Server administrators on Server Configuration tab.
On Data Directories tab, it is possible to place Data root directory to another folder.
You can also define data and log directories as well as default backup directory on this screen.
I keep them unchanged since this is my development server.
TempDB configuration is an other tab where a DBA can control TempDB and its log file properties like initial size and number of TempDB files, Autogrowth options, log file directory, etc.
Feature Configuration Rules is again a step where setup program checks if any thing prevents configuration of selected options.
InstallFeatureSpecificConfigRules: SQL Server vNext CTP2.0 Setup configuration checks for rules group 'InstallFeatureSpecificConfigRules'
ASIsValidEditionForDeploymentModeCheck: Checks whether the SQL Server edition supports the currently selected Analysis Services server mode
InstanceClashRule: Checks whether the specified instance name is already used by an existing SQL Server instance.
BlockCrossLanguageInstall: Checks whether the Setup language is the same as the language of existing SQL Server features
BlockMixedArchitectureInstall: Checks whether the installing feature(s) are the same CPU architecture as the specified instance
FAT32FileSystemCheck: Checks whether the specified drive is FAT32 file system volume. Installing on a FAT32 file system is supported but not recommended as it is less secure than the NTFS file system
StandaloneInstall_HasClusteredOrPreparedInstanceCheck: Checks if the selected instance name is already used by an existing cluster-prepared or clustered instance on any cluster node
We are now very close to start SQL Server 2019 installation.
In this screen, DBAs can make their last check on SQL Server 2019 installation configuration.
If you are ready to start, press Install button.
Here you see SQL Server 2019 setup progress is displayed
SQL Server 2019 installation is completed successfully.
With the end of the install process you will be informed that "Computer restart required" by a message displayed as follows:
"One or more affected files have operations pending. You must restart your computer after the setup process is completed."
Restart your server and start working on your new Data Platform, SQL Server 2019.
Now SQL programmers and SQL Server database administrators can use either SQL Server Management Studio 17 or Azure Data Studio to connect SQL Server 2019 instance and work with most recent data platform tool from Microsoft.