By: Daniel Farina | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Express Edition
Problem
SQL Server Express edition is a free version of SQL Server that can be used in production environments. In my previous tip we looked at reasons why you would or would not want to use SQL Server Express. In this tip we will cover the different versions of SQL Server Express that are available for you to download and use.
Solution
In the past few years we have seen SQL Server grow with many new features. Just to name a few: in SQL 2014 Microsoft added In-Memory OLTP, which was further enhanced in version 2016. In SQL 2016 came Machine Learning as a feature with the possibility to use R scripts inside T-SQL code. Finally, SQL Server 2017 brought us as an unexpected surprise of a Linux edition.
The New Versions of SQL Server Express
With all of those changes we shouldn't be surprised with the fact that Microsoft has decided to create different versions of SQL Server Express in order to adapt the product according to the market needs.
Basically, in recent years Microsoft was focused on making SQL Server a product that is more than a conventional database. In other words, Microsoft wanted SQL Server to be different than something that can be seen as a data holding place by adding more features and trying to differentiate itself from the competition. Just for comparison, let’s think about SQLite, which is a lightweight embedded and ACID-compliant database that implements most of the SQL standards, but lacks remote access capabilities. In other words, if someone labels SQLite as a data holding place, that person won’t find any resistance to that argument.
SQL Server 2017 Express
By downloading and using this version, you have the core express database server that includes the database engine features. Also, this version includes SQL Server Replication features, but it’s limited to work as a subscriber for Merge, Peer to Peer and Transactional replication.
This version is ideal when you need to accept remote connections. This, like all versions of SQL Server Express is available for free. On the next image you will see a screen capture of the Feature Selection step on the SQL Server Express installer.
SQL Server 2017 Express with Advanced Services
This version gives users more features than the ones you will get with Express core edition. It includes the SQL Server database engine, SQL Server Replication (limited to work as a subscriber for Merge, Peer to Peer and Transactional replication) and full text search. Something that is worth mentioning is the inclusion of data analysis tools with Machine Learning Services (In-Database), allowing you to use both Python and R language. Also, you have the PolyBase Query Service for External Data, and for reporting, you have Reporting Services available as a downloadable add on.
In contrary to the core express version, if you want to download and use SQL Server 2017 Express with Advanced Services, you must join the Visual Studios Dev Essentials program. You can do so by following this link: https://www.visualstudio.com/es/dev-essentials/. On the following screen capture you will see the feature selection screen of the SQL Server 2017 Express with Advanced Services installer.
This version, which is the most complete, is the right choice for advanced developers which are working on applications that use Business Intelligence or Machine Learning Algorithms. Also, this is the proper version if you are developing an application that uses PolyBase to connect to a Hadoop data source or Azure blob storage.
SQL Server 2017 Express LocalDB
This version allows you to embed SQL Server Express into applications with LocalDB. It is a lightweight version of the SQL Server Express edition which is a replacement of the deprecated SQL Server Compact. This version is the best choice when you need to use a database in your application and you don’t want to get involved with the administration of the database.
You can think about LocalDB as an alternative to SQLite database. SQL Server 2017 Express LocalDB contrary to SQLite, it is a client-server database engine that allows you to create an application where multiple users or processes on the computer where LocalDB is running needs to access the database. Furthermore, with LocalDB you have the possibility to use functions, triggers and stored procedures which are not available on SQLite.
But LocalDB also has the following limitations:
- It can’t act as a subscriber in a merge replication scenario.
- It does not support FILESTREAM.
- Only allows local queues for Service Broker.
This version is your best choice if you are a developer that wants to code your application and you don’t want or don’t have time to spend on database configuration and administration.
Next Steps
- Check out the SQL Server Express Edition Tips Category for more cool tips and tricks regarding SQL Server Express Edition.
- SQL Server Express does not include shortcuts for backup handling and automation. So if you are in need then you can read the following tip: Automate SQL Server Express Backups and Deletion of Older Backup Files.
- If you are interested in using SQL Server Express LocalDB on your applications as the database server you can read the following tip: Getting Started with SQL Server 2012 Express LocalDB.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
