This article is aimed mainly at small to medium business owners/managers and IT decision makers who may find themselves in a position where they need to make strategic choices about their business software and need to understand the differences between Microsoft SQL Server Standard and SQL Server Express, without getting too heavy on technical detail.
What is Microsoft SQL Server?
Microsoft SQL Server is what’s technically called a “Relational Database Management System”, basically a database engine. It’s a piece of Microsoft software designed to run on Windows Operating Systems, used by Independent Software Vendors to manage databases for their applications rather than writing database engines from scratch.
There are several versions of Microsoft SQL Server, from the SQL Server Enterprise edition designed to hold massive amounts of data for large corporations, SQL Server Web to manage databases for web sites with lots of anonymous users, SQL Server Standard edition for medium and large sized databases, SQL Server Business Intelligence designed for analysis and reporting, and SQL Server Express which is aimed at smaller databases for SMEs as well as developers.
Microsoft release new versions of SQL Server every few years that work best with current Windows Operating Systems. At the time of writing the current version is 2022. Current supported versions include SQL Server 2019, SQL Server 2016, SQL Server 2016, and SQL Server 2014 (although that goes end of life in July 2024. Businesses using earlier versions that are unsupported such as 2012, 2008 or 2005 are encouraged to upgrade to reduce security and stability risks.
In this article we are going to focus on the commercial and technical differences between SQL Server databases on SQL Server Standard and Express.
What does SQL Server do?
At a very high-level SQL Server manages the tables of information that a piece of software uses. For example, the software may need a table of Customers containing company names, address and phone numbers, plus a unique number (identifier) for that customer. Then a table of Customer Orders, one of the fields in the Customer Orders table will be “Customer” which will contain the unique identifier of the relevant customer from the Customers table. This is the “relationship” between these two tables. SQL Server databases store these tables and manage these relationships hence the name “Relational Database Management System”.
SQL Server also has a number of tools to help manage the database such as running backups and keeping logs of each and every transaction to help recover from issues or disasters. These are mainly managed from the separate SQL Server Management Studio.
What are the technical differences between SQL Server Standard and SQL Server Express?
In a nut shell, size and scale, or rather scalability. SQL Server Express is a full database engine, containing much of the same functions and features as SQL Server Standard. It also includes the SQL Server Management Studio for more advanced management and configuration of the database. However, it has a number of limitations that restrict its scope to smaller data sets with fewer users connecting to it. Also, some features are missing such as the SQL Server Agent, tuning advisor, reporting services and analysis services. There are also some advantages to this, businesses using software based on SQL Server Express don’t need to worry quite so much about managing the database engine, it mostly looks after itself. SQL Server Standard edition and SQL Server Enterprise edition are loaded with many features that are too expensive for SMEs to implement or manage.
What are the commercial differences between SQL Server Standard and SQL Server Express
Quite simply, SQL Server Express is free. Businesses can download, install and use SQL Server Express without any cost and they don’t need to enter into any Microsoft licence agreements.
Independent Software Vendors (ISVs) can also embed SQL Server Express with their own software so it gets installed alongside it, making it a seamless install process.
Why have Microsoft made such as sophisticated piece of software free to use? Well I would argue that once customers are using SQL Express and their businesses grow, they will eventually out grow its limitations and the only realistic route is to then move to paid for versions of Microsoft SQL Server which have a not insignificant licence cost.
What are the limitations of SQL Server Express?
OK, here’s where we get a little bit technical. I’ll explain the limitations but also try to explain why these limitation matter. These values are based on SQL Server Express 2019
Maximum Buffer Pool Memory (per instance) – 1410MB. SQL Express editions are limited to just over 1GB of RAM per instance. Worth mentioning that an Instance is an individual installation of SQL Server, and you can have several instances on one computer. You might need more than one instance if different applications need different versions of SQL Server, or different configurations.
The reason the amount of RAM the Express edition of SQL Server can use is important is that it dictates how well it can perform. The more data from your database you can load into RAM as a buffer, the faster it will perform. Once that 1.4GB RAM is full then it has to fetch data from hard disk which is slower. As a comparison the Standard edition of SQL Server can use 128GB RAM
Maximum Compute Capacity (per instance) – lesser of 1 socket or 4 cores. Modern computers can have more than one CPU/Processor. And each processor can have multiple cores (like lots of mini CPUs on one chip). SQL Server Express can only ever use one processor, and if that processor has more than 4 cores, it can only use up to 4 of them.
This again affects overall performance. The more people you have connecting to the database and the more “heavy lifting” the application is asking the SQL database engine to do then then more it will ask of your processor. If the processor is overloaded these instructions wait in a queue, so slowing performance. For smaller applications with only a few users this limitation won’t be a problem, but as usage grows it may start to become a bottleneck and bring forward a migration to SQL Server Standard which can use 4 processors and up to 24 cores.
Maximum database size – 10GB. This is the total amount of data you can store in the database. 10GB is quite a sizeable amount for applications that just store text and numbers. If you are storing .pdf files, pictures, or other media then 10GB isn’t much at all.
It’s very much a hard limit, you might be able to live with slow performance, but if you run out of space the application will cease to work. Custom developed applications may cheat the system by having multiple databases in one SQL instance. This may be a work around but if your databases total more than 10GB then you are already likely to be suffering performance issues because of RAM limitation mentioned above.
Feature limitations of SQL Server Express. The three resource limitations mentioned above are the main things that business owners need to be concerned about. These are the things that will cause users to complain if the application is hitting the buffers. There are a number of technical features that developers and database administrators will be interested in that are absent from SQL Express editions. These include:
- No Log Shipping
- No SQL Server Agent which is a tool to run scheduled jobs such as backups, monitoring, replication and other tasks
- No backup compression or encrypted backups
- No SQL Analysis Services or SQL Reporting Services
Can you use SQL Server in the Cloud?
One of the reasons we are writing this article is that many businesses are looking for ways to run Windows desktop applications that require SQL Server, in the cloud, to enable remote working and reduce reliance on local IT infrastructure.
Using a hosted remote desktop service such as the one provider by Your Office Anywhere you can easily run SQL based applications. SQL Express is free, but requires a bit more RAM to perform well. SQL Standard has a licence costs but Your Office Anywhere are able to significantly reduce the licence burden for customers by using data centre licences and running multiple secure instances of SQL on multi-tenanted servers.
If you are looking for was to run SQL based applications in the cloud then please get in touch by either calling 01282 500318 or completing the contact form below.
Why Not Get A Free Trial Demo?