If you use SQL, several options are open to you, from the Enterprise editions down to SQL Server Express, a free version of Microsoft’s main RDBMS (Relational Database Management System), SQL Server. SQL Server is used to store information and access other information from multiple other databases. Server Express Edition is packed with features, such as reporting tools, business intelligence, advanced analytics, and so on.
SQL Server Express 2019 is the basic version of SQL Server, a database engine that can be deployed to a server, or you can embed it into an application. It is free and ideal for building desktops and small server applications driven by data. It is ideal for independent software developers, vendors, and those building smaller client apps.
The Benefits
SQL Server Express offers plenty of benefits, including:
- Automated Patching – allows you to schedule windows to install important updates, to SQL Server and Windows automatically
- Automated Backup – take regular backups of your database
- Connectivity Restrictions – when you install Express on an Image Gallery-created Server VM installation, there are three options to restrict connectivity – Local (in the VM), Private (in a Virtual Network), and Public (via the Internet)
- Server-Side Encryption/Disk Encryption – Server-side encryption is encryption-at-rest, and disk encryption encrypts data disks and the OS using Azure Key Vault
- RBAC Built-In Roles – Role-Based Access Control roles work with your own custom rules and can be used to control Azure resource access.
The Limitations
However, SQL Express also has its limitations:
- The database engine can only use a maximum of 1 GB of memory
- The database size is limited to 10 GB
- A maximum of 1 MB buffer cache
- The CPU is limited to four cores or one socket, whichever is the least. However, there are no limits to SQL connections.
Getting Around the Limitations
Although your maximum database size is limited to 10 GB (Log Files are not included in this), you are not limited to how many databases you can have in an instance. In that way, a developer could get around that limit by having several interconnected databases. However, you are still limited to 1 GB of memory, so using the benefit of having several databases to get around the limitation could be wiped out by slow-running applications.
You could have up to 50 instances on a server, though, and each one has a limit of 1 GB memory, but the application’s development cost could end up being far more than purchasing a standard SQL license.
So, in a nutshell, while there are ways around the limits, they don’t always pay off.
SQL Server Express Versions
SQL Server Express comes in several versions:
- SQL Server Express With Tools – this version has the SQL Server Database, and all the tools need for managing SQL instances, such as SQL Azure, LocalDB, and SQL Server Express
- SQL Server Management Studio – this version contains the tools needed for managing SQL Server Instances, such as SQL Azure, SQL Express, and Local DB, but it doesn’t have SQL Server
- SQL Server Express LocalDB – if you need SQL Server Express embedded into an application, this version is the one for you. It is a lite Express version with all the Express features, but it runs in User Mode and installs fast with zero-configuration
- SQL Server Express With Advanced Series – this version offers the full SQL Server Express experience. It offers the database engine, the management tools, Full-Text Search, Reporting Services, Express tools, and everything else that SQL Server Express has.
What SQL Server Express 2019 is Used For and Who Uses it
Typically, SQL Server Express is used for development purposes and to build small-scale applications. It suits the development of mobile web and desktop applications and, while there are some limitations, it offers the same databases as the paid versions, and it has many of the same features.
MSDE was the first SQL Server Data Engine from Microsoft, which was called Microsoft Desktop Engine. SQL Server Express grew when Microsoft wanted to build a Microsoft Access alternative to provide software vendors and developers with a path to the premium versions of SQL Server Enterprise and Standard.
It is typically used to develop small business applications – web apps, desktop apps, or mobile apps. It doesn’t have all the features the premium versions have. Still, most small businesses don’t have the luxury of using a DBA (SQL Server database administrator), and they often don’t have access to developers who use DBAs either.
Lots of independent developers embed Server Express into the software, given that distribution is free. Microsoft has even gone down the road of creating SQL Server Express LocalDB. This lite version offers independent software vendors and developers an easier way of running the Server in-process in the applications and not separately. SQL Server Express is also considered a great starting point for those looking to learn about SQL Server.
Downloading SQL Server Express Edition 2019
SQL Server Express Edition 2019 is pretty easy to download, and you get it from the official Microsoft Website.
Once you have downloaded it onto your computer, follow the steps below to install it and set it up:
Step One
- Right-click on the installation file, SQL2019-SSEI-Expr.exe.
- Click on Open to get the installation process started – ensure that the user who is logged on has the rights needed to install software on the system. If not, there will be issues during the installation and setup.
Step Two
- Now you need to choose which type of installation you need. There are three:
- Basic – installs the database engine using the default configuration setup
- Custom – this takes you through the installation wizard and lets you decide which parts to install. This is a detailed installation and takes longer than the basic installation
- Download Media – this option allows you to download the Server files and install them when you want on whatever computer you want.
- Choose the Custom installation – while the Basic is the easiest one, takes less time, and you don’t need to worry about the configuration as it is all done for you, the custom version allows you to configure everything how you want it.
Step Three
- Now you have a choice of three package installation types:
- Express Core – at 248 MB, this only installs the SQL Server Engine
- Express Advanced – at 789 MB, this installs the SQL Server Engine, Full-Text Service, and the Reporting Services features
- LocalDB – at 53 MB, this is the smallest package and is a lite version of the full Express Edition, offering all the features but running in user mode.
Step Four
- Click on Download and choose the path to install Server Express to – C:\SQL2019
- Click on Install and leave Server Express to install – you will see a time indicator on your screen, and how long it takes will depend on your system and internet speed.
Step Five
- Once the installation is complete, you will see the SQL Server Installation Center screen. This screen offers a few choices:
- New SQL Server Stand-Alone Installation or Add Features to Existing Installation
- Install SQL Server Reporting Services
- Install SQL Server Management Tools
- Install SQL Server Data Tools
- Upgrade From a Previous Version of SQL Server
- We will choose the first option – click on it and accept the License Terms
Step Six
- Click on Next, and you will see the Global Rules Screen, where the setup is checked against your system configuration
- Click on Next, and the Product Updates screen appears. This screen looks for updates to the setup. Also, if you have no internet connection, you can disable the option to Include SQL Server Product Updates
- Click on Next, and the Install Rules screen appears. This screen will check for any issues that might have happened during the installation. Click on Next
Step Seven
- Click on Next, and the Feature Selection screen appears
- Here, we choose which features are to be installed. As you will see, all options are enabled, so disable these:
- Machine Learning Services and Language Extensions
- Full-Text and Semantic Extractions for Search
- PolyBase Query Service for External Data
- LocalDB
- Near the bottom of the page, you will see the Instance Root Directory option. Set the path as C:\Program Files\Microsoft SQL Server\
Step Eight
- Click Next, and you will see the Server Configuration screen
- Here, we will set the Server Database Engine startup type – in this case, leave the default options as they are
- Click on the Collation tab to customize the SQL Server collation option
- Click Database Engine Configuration to specify the Server authentication mode – there are two options:
- Windows Authentication Mode – Windows will control the SQL logins – this is the best practice mode
- Mixed Mode – Windows and SQL Server authentication can access the SQL Server.
- Click on Mixed Mode, and the SQL Server login password can be set, along with a Windows login. Click on the Add Current User button to add the current user
Step Nine
- Click on the Data Directories tab and set the following;
- Data Root Directory – C:\Program Files\Microsoft SQL Server\
- User Database Directory – C:\Program fees\Microsoft SQL Server\MSSQL.15.SQLEXPRESS\MSSQL\Data
- User Database Log Directory – C:\Program fees\Microsoft SQL Server\MSSQL.15.SQLEXPRESS\MSSQL\Data
- Backup Directory – C:\Program fees\Microsoft SQL Server\MSSQL.15.SQLEXPRESS\MSSQL\Backup
Step Ten
- Click the TempDB tab and set the size and number of tempdb files – keep the default settings and click Next
- Now you will see the Installation Progress screen where you can monitor the installation
- When done, you will see the Complete Screen, telling you the installation was successful.
Frequently Asked Questions
Microsoft SQL Server Express Edition 2019 is popular, and the following frequently asked questions and answers will tell you everything else you need to know about it.
Can More than One Person Use Applications That Utilize SQL Server Express?
If the application is a desktop application, it can connect to all Express databases stored on other computers. However, you should remember that all applications are different, and not all are designed to be used by multiple people. Those designed for single-person use will not offer any options for changing the database location.
Where it is possible to share the database, the SQL Server Express Database must be stored in a secure, robust location, always be backed up, and available whenever needed. At one time, that location would have been a physical server located on the business premises but, these days, more and more businesses are opting for cloud-based storage options.
Can I Use SQL Server Express in Production Environments?
Yes, you can. In fact, some of the more popular CRM or accounting applications include Server Express. Some would tell you not to use it in a production environment, mostly because of the risks of surpassing your 10 GB data limit. However, provided you monitor this limit carefully, SWL Server Express Edition can easily be used in production environments.
Is SQL Server Express Edition Scalable?
There is a good reason why Microsoft allows you to download SQL Server Express Edition for free. It’s because, if it proves too small for your needs, at some point, you can upgrade to the premium SQL Server Standard version. While the Express Edition is limited and you are likely to outgrow it at some point, transferring your database over to the Standard version when the time comes is easy. Really, the Express version is just a scaled-down version of Standard. Any development you do on it is fully compatible with any other Edition of SQL Server and can easily be deployed.
Can I Use SQL Server Express in the Cloud?
Cloud computing is being adopted by more and more businesses and their applications. These days, many are now built in the cloud as web or mobile apps. However, when it comes to desktop applications, it is a slightly different story, as these need to be near the SQL Server Express Database to work properly. Suppose you host the database in the cloud but leave the application on the desktop. In that case, you are likely to experience poor performance, and you may even find your databases becoming corrupted.
You can get around this issue by running your application in the cloud, too, and this is easy using a hosted desktop (a hosted remote desktop service), which used to be known as a terminal service. In this case, the database and application reside on servers in the data center provided by the host and are remotely controlled by the users. As far as the user is concerned, it won’t look or feel any different from running on their own computer.
What Do I Get With SQL Server Express?
The premium SQL Server editions contain many features that you can also find in the free SQL Server Express Edition. Aside from the database engine, you also get:
- Advance Query Optimizer.
- Business Intelligence Development Studio.
- Configuration Manager.
- Import/Export Wizard.
- Service Broker.
- SQL Server Management Studio – while this is not included with the installation, it can be downloaded free from: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
- Surface Area Configuration Tool.
Plus, the Express licensing allows you to bundles SQL Server Express with third-party applications.
What Isn’t Included?
There are a few things you don’t get in the Express edition compared to SQL Server Standard. For a start, Express edition has limits not found in the premium editions:
- Each relational database can be no larger than 10 GB, but log files are not included as there are no limits on these
- The database engine is limited to just 1 GB of memory
- The database engine is also restricted to one CPU socket or four CPU cores, whichever is the lower of the two.
- All the SQL Server Express Edition components must be installed on a single server
- SQL Server Agent is not included – admins use this for automating tasks such as database replication, backups, monitoring, scheduling, and permissions.
- Availability Groups
- Backup Compression
- Database Mirrors limited to Witness Only
- Encrypted Backup
- Failover Clusters
- Fast recovery
- Hot add memory and CPU
- Hybrid Backup to Windows Azure
- Log Shipping
- Mirrored backups
- Online Index create and rebuild
- Online Page and file restore
- Online schema change
- Resumable online index rebuilds
Where Do I Find the SQL Server Express Edition Documentation?
You can find the relevant documentation at https://docs.microsoft.com/en-us/sql/?view=sql-server-ver15 and are urged to make good use of it. Refer to the documentation whenever you don’t understand something or want to learn how to do something new.
Microsoft SQL Server Express Edition 2019 is worth considering for small businesses, as it gives you a good starting point. As your business grows, you can upgrade to the premium versions without having to worry about learning a new system – you already know the basics, and your databases will transfer seamlessly over.
