What is SQL Server Express used for?

SQL Server Express is the free version of Microsoft’s acclaimed SQL Server relational database management system. It is used for small scale applications and development, and is ideal for desktop, web, and mobile applications. While it does have some restrictions and limitations, it is the exact same database and contains many of the features that are included in the paid for versions.

Microsoft’s SQL Server Express grew out of MSDE, the original Microsoft SQL Server Data Engine (aka Microsoft Desktop Engine). Microsoft wanted an alternative to Microsoft Access that would give software developers and vendors a defined path to the paid for versions of SQL Server Standard and Enterprise.

Who uses Microsoft SQL Server Express?

SQL Server Express is generally aimed at applications used by small businesses, these may be standard desktop applications, web applications or as a data source for mobile apps. It doesn’t include some of the more advanced features enjoyed by SQL Server Standard or SQL Server Enterprise but the average small business doesn’t have the luxury of a SQL Server database administrator (DBA) or a developer that would use these features anyway.

Many independent software vendors (ISVs) will embed SQL Server Express with their software as it is also free to distribute. In fact, Microsoft have created an even more lightweight version called SQL Server Express LocalDB, targeted at developers and ISVs, which runs “in-process” with applications rather than as a separate “Service”. SQL Server Express is also seen as a good starting point for people who want to learn about SQL Server.

What are the limitations of SQL Server Express?

At the time of writing (2018) Microsoft SQL Server Express is limited to a maximum database size of 10GB, 1GB of Memory (RAM) per instance, and the lesser of 1 Physical CPU (Socket) or 4 CPU Cores. There are also a number of components that are not included in the Express version compared with the Standard or Enterprise versions of SQL. These include the SQL Server Agent (used to automate certain tasks such as backups, job scheduling).

Are there ways around the limitations?

While you may be limited by the maximum database size of 10GB (this doesn’t include the Log Files), you can have an unlimited number of databases in an instance, so developers could potentially have multiple interconnected databases to get around this. There is still the limitation of 1GB Memory so the benefit of circumventing the limitations by splitting into multiple databases could be negated by the fact that your application may run like a dog. Of course, you can have multiple instances, up to 50 on a server, and each instance can have 1GB Memory. However, you may also find that the cost of developing the application to work this way may outweigh the cost of buying a SQL Server Standard licence. If the significant upfront outlay of SQL Server Standard is unpalatable see the section at the end of this article about renting SQL Server.

Can applications that use SQL Server Express be used by multiple people?

Desktop applications can connect to SQL Server Express databases saved on another computer. However, every application is different and some applications that are designed to only be used by one person may not give options for changing the location of the database.

Where the database can be shared then it is advisable that the SQL Server Express database is kept somewhere that is resilient and secure, can be backed up and is always available. Traditionally this may have been a “server” on the businesses premises but increasingly businesses are looking to cloud based solutions for their applications.

Can SQL Server Express be used in a Production environment?

Yes, most definitely, and it is included in some of the UKs most popular accounting or CRM applications aimed at SMEs. There are those who would advise against it in a production environment, mainly because of the risks if your data unexpectedly hits the 10GB limit, but as long as this is closely monitored then SQL Server Express is perfectly suitable in a production environment.

What about Scalability?

There’s a reason Microsoft give SQL Server Express away for free, it is so that when you out grow it you will move to SQL Server Standard, the paid for version. Microsoft have made it very easy to move your database from Express to Standard. Essentially SQL Server Express is the same thing as SQL Server Standard but with some limitations built in and fewer components. Any development done on Express is 100% compatible with, and can be deployed to, any paid for edition of Microsoft SQL Server.

Can you use SQL Server Express “in the cloud”?

Cloud computing is fast becoming the norm for modern business applications, with many being built to run in the cloud either as web applications (built into a web site) or for mobile apps. However, desktop applications really need to be “close” to their SQL Server Express database to perform properly. If the database is hosted in the cloud but the application remains on the desktop there is a good chance performance will be poor and there’s a risk of databases becoming corrupted.

The way round this is to run the desktop application in the cloud as well. This can be easily done by using a hosted remote desktop service (hosted desktop), previously known as terminal services. Here both the desktop application and the database sit on servers in the provider’s data centre and the users simply remote control them. From the user’s point of view it will look and feel no different than running on their local PC.

What is included with SQL Server Express?

There are a number of key components of the paid for versions of SQL Server that are also part of SQL Server Express. Beyond the core SQL Server database engine, these include:

• SQL Server Management Studio – This doesn’t actually get included when you download SQL Express but can be downloaded for free from: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
• Configuration Manager.
• Surface Area Configuration Tool.
• Business Intelligence Development Studio.
• Advance Query Optimiser.
• Service Broker.
• Import/Export Wizard.
• The SQL Express licencing allows it to be bundled with 3rd party applications.

What isn’t included in Microsoft SQL Server Express compared with SQL Server Standard?

The limitations and items that don’t come with the free version, SQL Server Express include:

• Maximum size of each relational database is limited to 10GB – This doesn’t include log files so these aren’t subject to limitation
• Maximum memory used by the SQL Server database engine is limited to 1GB
• Database engine restricted to 4 CPU cores or 1 CPU Socket, whichever is lower.
• All components of SQL Server Express have to be installed on one server.
• SQL Server Agent – used by admins to automate tasks such as backups, replication of database, job scheduling, monitoring and permissions.
• Analysis Services.
• Integration Services.
• Notification Services.
• Database mirroring.
• Smart Backups.
• Encrypted Backups.
• SQL Profiler

Find out more

To find out more about the benefits of hosting SQL Server Express based applications on a remote desktop service why not discuss with one of our technical sales consultants today to find out how Your Office Anywhere can help you achieve your goal, please go to the contact form below.