How to download and install SQL Server 2016 express edition
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:
- Design and test the database schema by an organization.
- 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
- 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
- Before SQL Server 2017, Microsoft did not provide a developer edition, so students used it for their project works
-
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:
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.
- 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.
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:
- Basic: When we select the Basic installation type, the installer will download and install the database engine and features with the default settings
- 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
- 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.
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.
The download process begins. The time taken to download the files depends on the internet speed.
Once files are downloaded, the SQL Server installation center starts. On the installation screen, you can
- Perform new SQL Server stand-alone installation or add new features in the existing instance
- Install SQL Server management studio
- Install SQL Server Data Tools
- 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.
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.
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.
On the feature selection screen, you can select the feature that you want to install. Select all the instance features and click Next.
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.
On the Server configuration screen, provide the credentials of the SQL Server service accounts and database collation. I have used the default settings.
On the Database engine configuration screen, you can configure the following:
- Authentication Mode and the SQL Server administrator
- Data directories: Specify the location of the data files and log files
- TempDB: Specify the following TempDB settings
- Number of TempDB files
- Initial and maximum size of TempDB data files
- Autogrowth size
- Location of the TempDB data files and log files
- Initial and maximum size of the TempDB log files
- Autogrowth of the TempDB log files
- 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:
Data directories:
TempDB:
FILESTREAM:
On Reporting Services configuration screen, you can install and configure the SQL Server reporting services. You can choose any of the following options
- Install and configure: This option will install the reporting service, and when the SQL Server installation completes, it configures the reporting services
- Install only: This option will install the reporting services
We want to install the reporting services; therefore, select the Install Only option.
On the consent to install Microsoft R Open screen, click on Accept.
The installation process begins.
The SQL Server 2016 express download and installed on the workstation.
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.
Once connected to SQL Server instance, run the following query to view the version of the SQL Server.
1
2
3
use
master
go
select
@
@
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.