Rajendra Gupta
SQL Server 2019 Important Features

SQL Server 2019 overview and installation

October 15, 2018 by

On September 24th, 2018, Microsoft launched SQL Server 2019 preview version (SQL Server vNext 2.0) in the ignite 2018 event. As you know, SQL Server 2017 is still being adopted by the organizations, we are now ready with this preview version.

As we know, SQL Server 2017works on the cross-platform operating system for both Windows and Linux operating systems. As per SQL Server blog,

” SQL Server 2019 creates a unified data platform with Apache SparkTM and Hadoop Distributed File System (HDFS) packaged together with SQL Server as a single, integrated solution.”

Microsoft SQL Server 2019 is built on the concept to move the artificial intelligence a further step in with improvement over database services, security, integration to big data clusters etc.

The graphic below shows highlighted features

SQL Server 2019 Important Features

Image source: https://www.microsoft.com/en-us/sql-server/sql-server-2019

Important features

Some of the important features of SQL Server 2019 are:

  • Big Data clusters with SQL Server
  • Resumable online index create
  • Row mode memory grant feedback
  • Online Clustered ColumnStore Indexes rebuild
  • New DMV sys.dm_db_page_info
  • Up to 5 synchronous replicas in Always On Availability Groups
  • Enhanced Polybase connectors for SQL Server, Oracle, Teradata, and MongoDB
  • Data Virtualization
  • Extended support for persistent memory devices.
  • Database-scoped default setting for online and resumable DDL operations
  • Certificate Management in Configuration Manager
  • Data Classification and Auditing
  • Graph DB enhancements and support to UTF-8
  • Always Encrypted with secure enclaves

We will review each enhancement in detail over the next few articles. In this article, we will see how to install SQL Server 2019 in windows.

SQL Server 2019 installation on Windows

We can install SQL Server 2019 on Windows, Linux, Docker containers and Big data analytics container images with Kubermetes. Go to SQL Server 2019 and we get below download options

Install 
SQL Server 2019 options on differnt environments

In this article, we are going to install SQL Server 2019 on windows so click on windows and it now opens a screen with only windows operation system set up.

Install SQL Server 2019 on WIndows system

Click on ‘Preview SQL Server 2019 for Windows’ and it lands on the product family page. We need to provide basic details like Name, Company name, email address, contact number, and country name.

Provide basic details to download set up of SQL Server 2019

Once you provide details, click on ‘Continue’ and it starts the download. Download SQL Server VNext 2.0 Set up

Once the download is complete, click on SQLServerVNext-SSEI-Eval.exe file and you get below options for the installation of SQL 2019.

It gives three options to choose:

  1. Basic: To install the database engine with default settings.
  2. Custom: in this option, we will go through the installation steps and configure SQL Server as per our requirement.
  3. Download Media: If we just want to download the set up, choose this option. We can choose ISO\CAB format to download.

SQL Server vNext CTP2.0 installation type

Let us choose the Custom mode for the installation. Once you click on the custom mode, it asks for the SQL Server media download target location. This path should have sufficient free space to download media.

SQL Server vNext CTP2.0 installation media location

Click on Install to move further. It downloads the media and starts the installation.

Download progress for SQL Server vNext CTP2.0 installation media

While the download is in progress, we can a few messages to give us some important information.

  • SQL SERVER 2019 CTP 2.0 is also available for Linux. To obtain the Linux 2.0 images, including containers, please see here.

SQL Server vNext CTP2.0 installation media download and messages

Once SQL Server CTP 2.0 media download is complete, it extracts the setup files to start the installation.

SQL Server vNext CTP2.0 installation media download and messages

We need to wait while SQL Server vNext CTP 2.0 set up process the current operation.

SQL Server vNext CTP installation progress message

It launches the SQL Server installer page that contains links for below pages:

  • Planning: for SQL Server documentation, release notes, upgrade documentation etc.
  • Installation: Install SQL Server services on stand-alone and cluster instance; install SQL Server Reporting service, Management tools, data tools etc.
  • Maintenance: Edition upgrade, repair, remove a node from clusters.
  • Tools: System configuration checker, SQL Server feature discovery report, Microsoft Assessment and Planning Toolkit
  • Resources: SQL Server resources, books online, license agreement, privacy statement.
  • Advanced: install SQL Server on the configuration file, advanced cluster preparation, image preparation and completion of the standalone instance
  • Option: Specify architecture (x64 or x86) and installation media root directory.

SQL Server Installation Center and its options

Click on Installation page and then on ‘New SQL Server stand-alone installation or add features to an existing installation’

SQL Server Installation Center - option to install SQL Server on Standalone system

Since we are installation evaluation edition of SQL Server vNext CTP2.0, we will move will default option ‘Evaluation’ and click Next

Specify edition in SQL Server Installation Center

Accept the license terms and conditions. Please note the message written here that SQL Server

Transmits information about installation experience in performance data to Microsoft to help improve the product.

SQL Server Installation Center accept license terms

Click on Next. In the next page, it checks for the Global rules and give status information about successful, failed rules. If there are any failed rules, we need to fix those before proceeding with the installation.

Global Rules in SQL Server vNext CTP2.0 Setup

In the next step, it downloads the setup files. If SQL Server installation is already there on the server, it might skip the steps.

Install Set up files in SQL Server vNext CTP2.0

In the next screen, it checks for setting up rules that might cause issues in the SQL Server installation. We can see the warning message against the Windows Firewall but it can be ignored. Click next to move further.

Install Rules SQL Server vNext CTP2.0

Now we need to select the features, we need to install with this installation. We can a new option ‘Java connector for HDFS data sources’ as compared with SQL Server 2017 installation.

Feature selection in SQL Server vNext CTP 2.0

You can choose to install a default or named instance name. If a default instance is already present on the machine, we can only install named instance. Provide an appropriate name for the named instance. For example, I specified SQL 2019.

Instance Configuration to select default or named instance

In the next page, Server configuration, you can specify service account to use for SQL Server services along with the Collation. In this article, let us move with default service account and collation. We can change service accounts later from SQL Server configuration manager Specifu service account for SQL Services

If we want to change the collation, click on customize and choose the required collation. We need to be cautious while changing Collation.

specify and customize SQL Server Collation

Specify Authentication modes (Windows or Mixed mode) and add users to have SQL Server administrators.

Select Authentication modes and administrators

Click on Data Directories and we can specify the data, log files, backup directories here.

Specify Data Directories for database files, log files and backups

Click on TempDB to configure the TempDB configurations. We will move with the default configuration suggested by the installer.

Specify Data Directories for TempDB database files, log files

If we want to enable Filestream, enable this option else to leave and click on Next.

Enable FileStream in SQL Server vNext CTP 2.0

We get an overview of the SQL Server configurations from the Ready to install page. Review the information and click on next to start the installation process.

Verify the Configuration  before starting setup

Once the installation is completed, launch SSMS 18.0 preview to connect with SQL Server.

Download and install SSMS Release 18 Preview 4

We need to install SSMS 18.0 preview 4 to connect to SQL Server 2019 and explore full features. To install SSMS 18.0 preview. Download from link.

Download SSMS 18.0 Preview for connecting to SQL Server

Download the SSMS release 18.0 preview 4 and start the installation. It just completes without any user input.

Install SSMS 18.0 Preview 4

It gives below message to restart the system to complete setup.

Restart Server after installation of SSMS 18.0 release 18.0 preview 4

Now launch SSMS and connect to SQL Server to check the version and edition details.

Launch SSMS to connect with SQL Server 2019

Connect to the instance and right click ->properties

View Database instance properties

We can check the edition, product version and ProductLevel from query as well as shown below.

Database instance properties from t-SQL

Conclusion

SQL Server 2019 looks promising to deliver the next generation of SQL Server with many new features, enhancements, integration with big data clusters, AI enabled relational database engine. Keep exploring new features to get pace with Microsoft new release.


Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
2,900 Views