MS SQL Server Express

Microsoft SQL Server Express is a version of Microsoft SQL Server relational database management system, which is free for download, distribution and use. It contains a database specifically designed for embedded and small-scale applications.

The product has its roots in the Microsoft Database Engine (MSDE) product that comes with SQL Server 2000. The Express logo has been in use since the release of SQL Server 2005.

SQL Server Express provides many features of paid, full versions of Microsoft SQL Server database management system. However, it has technical limitations that make it unsuitable for some large-scale deployments.

Unlike MSDE, Express does not include a parallel workload controller to limit performance if the Database Engine component gets more work than usual for a small number of users.

SQL Server Express includes several graphical tools for managing databases. These include:

• SQL Server Management Studio
• SQL Server Configuration Manager
• SQL Server Surface Area Configuration tool
• SQL Server Business Intelligence Development Studio

Features available in the SQL Server “Standard” and “better” releases, but not in SQL Server Express:

• Notification Service Integration Analysis Service

Comparison with Microsoft SQL Server

Major differences:

• Maximum size of 10 GB per database in SQL Server 2016, SQL Server 2014, SQL Server 2012 and 2008 R2 Express (4 GB for SQL Server 2008 Express and earlier versions; compared to 2 GB in the previous MSDE).
• The restriction applies to each database (excluding log files), but in some scenarios, users can access more data using multiple interrelated databases.
• No SQL Server Agent service.
• Restrictions on using physical hardware:
  – One physical processor, but allowed to use multi-core.
  – 1 GB RAM (works on a system with large amounts of RAM, but uses no more than 1 GB per SQL Server Database Engine instance). Express with additional services has a 4 GB limit per instance (not available in other Express versions). Analysis Services are not available for Express options.

Unlike MSDE, Express does not include a parallel workload controller to limit performance if the Database Engine component gets more work than usual for a small number of users.

Microsoft SQL Server Express installation options

Microsoft SQL Server Express is presented in options:

SQL Server Express with Service Pack

Basic express release of the database server, which includes a functional database module that is suitable for accepting remote connections or remote administration. Available free of charge.

SQL Server Express with additional services

Additional features available in express output, including database module, reporting services and full-text search. Available free of charge when you join the Visual Studios Dev Essentials program.

SQL Server Express LocalDB

Embedded in SQL Server Express applications using LocalDB, a simplified version of the express release. Available free of charge as an option when downloading SQL Server 2017 Express with Service Pack.

The free version of Express, 2005, offered the following standard approach to installation options. As a rule, SQL 2005 Express installers come with the following agreed naming convention:

SQLEXPR.EXE
Has installers for 32-bit and 64-bit processors, but with basic settings.
SQLEXPR32.EXE
Only has an installer for 32-bit processors (still basic settings).
SQLEXPRWT.EXE
It has installers for 32-bit and 64-bit processors and SQL Server Management Studio Express (SSMSE) (2008 R2).
SQLEXPR_ADV.EXE
Has the basics and SQL Server Management Studio Express (SSMSE) + reports and full-text queries.
SQLEXPR_TOOLKIT.EXE
Has the basics of SSMSE and Business Intelligence Development Studio (BIDS)

Architecture

External access is provided through the SQL Server protocol layer. All operations in SQL Server can be performed by accessing through a specific format called Tabular Data Stream (TDS). TDS is the application layer protocol used to transfer data between the server and the client. TDS can be wrapped around other physical and transport protocols, including TCP/IP. Consequently, access to SQL Server is also available outside of it. The architecture can be seen in Figure 1.

In addition, there is native support for Common Language Runtime (CLR), which allows you to use Microsoft development tools effectively.

Technology

DB engine
This is a basic service for data storage, processing and protection. The Database Engine components provide controlled access and fast transaction processing to meet the requirements of the most demanding data-consuming applications in the enterprise. The Database Engine also provides enhanced support to ensure high availability.

Replicate
Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to ensure consistency. By using replication, data can be distributed to multiple locations, including mobile users via LANs, WANs, remote access connections, wireless connections and the Internet.

Integration Services
Integration Services is a platform for creating high-performance data integration solutions, including packages that provide extract, transform, and download (ETL) processing for data warehouses.

Analysis Services
Analysis Services is a data analytics platform and set of tools for personal, team and corporate business analytics. Server and customer developers support traditional OLAP solutions, new table modeling solutions, and self-service analytics and collaboration using Power Pivot, Excel, and SharePoint Server environments. Analysis Services also includes intelligent data analytics to reveal hidden patterns in large volumes of data.

Microsoft SQL Server Express Versions

Installing and configuring MS SQL Server Express

This instruction describes how to install MS SQL Server 2017 Express on Windows Server 2012 R2/2016/2019. Following it you will get a ready-made tool to get acquainted with the SQL programming language, to test your own developments or run a small resource that does not require large amounts of storage of databases.

SQL Server 2017 Express Release Restrictions

This issue is free and suitable for commercial use, but has some limitations:

• Maximum number of processor cores: 4;
• Maximum database size: 10 GB;
• Maximum buffer pool size per database instance: 1410 MB.

If you need to go beyond these limitations, we recommend purchasing a Microsoft SQL Server Standard license.

Installation Procedure

To install SQL Server 2017 Express, follow the link, select the installation language and click Download:

Once the downloaded file is started, the installer will open a special window offering several installation options to choose from. To control the installation options, select Custom:

Then select the place where the installation files will be downloaded, if necessary change it to the desired one and press Install. Wait until the download of the installation files is complete.

Then select the first option to install New SQL Server stand-alone installation:

Read the terms of the license agreement and click Next. If you want to receive updates from Windows Update, tick the box and click Next.

Read the warning about possible problems, and if there are no problems, press Next:

Go to the Function Select screen, where you can select or remove additional features for the database server if necessary, leave it unchanged and press Next:

Select a server name and identifier. The server identifier will be included in the installation path. Leave it as default and click Next.

On the next screen you can specify service accounts other than the standard ones and grant the volume service tasks to the DBMS SQL Server kernel service, which will increase the file initialization speed, but the DBMS may access the remote content. On the Collation tab one can change the sorting parameters of the database engine. In this example, we will give privileges, leave the sorting parameters by default and click Next:

The next step of the installation is to configure the database core configuration. To do this, the following tabs are provided:

• Server Configuration – specify the method of authorization to the database: by Windows or mixed mode, which includes Windows authorization and your own SQL Server authorization. When choosing the second option, you should specify the SQL Server administrator password;
• Data Directories – specify the location of SQL Server executable files and data;
• TempDB – parameters of TempDB used by internal SQL Server resources, temporary user objects and version storage;
• User instances – allows to give rights to users that do not have administrator rights to run individual database instances;
• FILESTREAM – enables you to use Memory Optimized tables if necessary.

Since Microsoft R (Machine Learning Services) and Python were selected as the default installation, you should agree to the terms of use for this and the next step by clicking Accept and Next in succession:

The installation process will start and the SQL Server 2017 installer window will appear. Press Close. This concludes the installation of SQL Server 2017 Express.

How to install Microsoft SQL Server Express 2017