Nisarg Upadhyay
Connect to server

How to download and install SQL Server 2016 express edition

December 2, 2021 by

In this article, you are going to learn about SQL Server 2016 express download and installation process. The SQL Server 2016 express edition is a free version of SQL Server that offers limited features. The express edition of SQL Server can be used in any of the following scenarios:

  1. Design and test the database schema by an organization.
  2. To store a small amount of data. For example, we have developed inventory software for a shop/coffee shop, and we are designing a module to keep track of customers, appointments, and inventory. We can use the SQL Server express edition as a backend. The SQL Server Express edition is free, so customers don’t have to pay for a database license
  3. The backend of embedded software. At the beginning of my career, I worked with a firm that developed embedded software and sold it to their customer. They used to ship the software with express edition
  4. Before SQL Server 2017, Microsoft did not provide a developer edition, so students used it for their project works
  5. You can use the express edition as a witness server to enable the automatic failover process in the mirroring setup. You can read Configuring SQL Server Express edition as SQL witness server in Database Mirroring article to learn how we can use SQL Server express edition instance as a witness of a database mirroring

Following is the list of imported features and scalability limitations of the SQL server express edition.

Feature

Allowed Limit

Maximum compute capacity for SQL Server database engine.

One socket or four cores.

Maximum memory for buffer pool per instance.

1024 MB

Maximum allowed database size

10GB

Database mirroring support

Yes, as a witness server

Database snapshot

Yes

Column store index Support

Yes

In-memory OLTP

Yes

Table and index partitioning

Yes

Data compression

Yes

Partition parallelism

Yes

SQL Server profiler

No

SQL Server Agent

No

Standard performance dashboard

NO

Peer to peer transaction replication

No

SQL Server replication (Oracle publication)

No

Backup encryption

No

Transaction and merge replication

Yes, but the instance can be used as a subscriber

Snapshot replication

Yes, but the instance can be used as a subscriber

Replication to Azure

No

R integration

No

Python integration

No

Machine learning server

No

  • Note: You can read the entire list of features and scalability limitations of the SQL Server Express edition here

You can read the following articles to learn the download and installation process of SQL Server 2019, SQL Server 2017, and SQL Server 2012 express editions:

  1. How to install SQL Server 2019 Express edition.
  2. How to install SQL Server 2017 express edition.
  3. How to install SQL Server 2012 express edition.

Let us understand how we can download and install SQL Server 2016 express edition.

Download SQL Server 2016 express

The SQL Server 2016 express edition comes with Service pack 2 (SP2.) It can be downloaded from the Official page of the Microsoft download center. On the web page, select the installation language and click on Download.

SQL Server 2016 express edition download page

  • Note: Alternatively, you can directly download the installation file from here

The download process begins. Once it completes, double-click the installation file to begin the installation.

SQL Server 2016 express setup file

Now, let us understand the installation process. The installation process of SQL Server 2016 SP2.

To begin the installation, double-click on the SQLServer2016-SSEI-Expr file. On the first screen, you have three installation types:

  1. Basic: When we select the Basic installation type, the installer will download and install the database engine and features with the default settings
  2. Custom: When we select Custom installation type, you can run the step-through installation wizard, and you can choose components according to the business requirement
  3. Download media: When we select the Download media option, you can download the iso file of the SQL Server express edition

We are going to learn the process of custom installation, so click on the Custom installation type.

SQL Server 2016 express edition installation options

On the next screen, you must specify the media location. The SQL Server will download the installation files on the location specified in the media location textbox. The download size of the media files is 445MB.

SQL Server 2016 media location

The download process begins. The time taken to download the files depends on the internet speed.

Installation process of SQL Server 2016 express  edition

Once files are downloaded, the SQL Server installation center starts. On the installation screen, you can

  1. Perform new SQL Server stand-alone installation or add new features in the existing instance
  2. Install SQL Server management studio
  3. Install SQL Server Data Tools
  4. Upgrade the SQL Server version

We are installing a new instance so, click on New SQL Server stand-alone installation or add features to an existing installation.

Select New stand alone installtion

You can view the Microsoft Software License terms and Microsoft SQL Server 2016 express edition license terms on the License Term screen. Click on Accept license terms.

License terms

The installer will check the installation rules on the Install Rules screen to ensure that the setup does not encounter any errors. If any installation rule fails, the setup will not continue until the error has been rectified.

Install rules

On the feature selection screen, you can select the feature that you want to install. Select all the instance features and click Next.

Feature Selection

On the instance configuration screen, provide the instance details. If you select Default instance, then the installer will create an instance named MSSQLSERVER. If you want to create a named instance, then provide the instance name in Named Instance. We are creating a named instance, so provide SQLEXPRESSINST as an instance name.

Instance configuration

On the Server configuration screen, provide the credentials of the SQL Server service accounts and database collation. I have used the default settings.

Server configuration

On the Database engine configuration screen, you can configure the following:

  1. Authentication Mode and the SQL Server administrator
  2. Data directories: Specify the location of the data files and log files
  3. TempDB: Specify the following TempDB settings
    1. Number of TempDB files
    2. Initial and maximum size of TempDB data files
    3. Autogrowth size
    4. Location of the TempDB data files and log files
    5. Initial and maximum size of the TempDB log files
    6. Autogrowth of the TempDB log files
  4. FILESTREAM: Enable the FILESTREAM feature

In our setup, use the default settings of all parameters. Screenshot of all parameters are as the following:

Server Configuration:

Authentication mode

Data directories:

Data directories

TempDB:

TempDB settings

FILESTREAM:

FILESTREAM feature

On Reporting Services configuration screen, you can install and configure the SQL Server reporting services. You can choose any of the following options

  1. Install and configure: This option will install the reporting service, and when the SQL Server installation completes, it configures the reporting services
  2. Install only: This option will install the reporting services

We want to install the reporting services; therefore, select the Install Only option.

Reporting Services

On the consent to install Microsoft R Open screen, click on Accept.

Consent to install R Open

The installation process begins.

SQL Server 2016 express edition installation in process

The SQL Server 2016 express download and installed on the workstation.

SQL Server 2016 express edition is installed

Now, let us test the connectivity by executing some queries.

Connect to the SQL Server 2016 express edition

As the SQL Server 2016 express download and installed, we are going to use SQL Server management studio to verify the connectivity. Open the SQL Server management studio and in the connect to the server screen, select Database Engine as Server type, NISARG-PC\SQLEXPRESSINST as server name, Select Windows Authentication as the authentication method and click on Connect.

Connect to server

Once connected to SQL Server instance, run the following query to view the version of the SQL Server.

Database engine and SQL Server Version

As you can see, the SQL Server 2016 express edition was downloaded and installed correctly.

Summary

In this article, we learned about the SQL Server 2016 express edition download and installation process.

Nisarg Upadhyay
16,932 Views