What are the limitations of SQL Server Express
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).
Microsoft SQL Server is a Relational Database Management System (RDBMS) and is used by many software programs as a way to manage data in the application. There are various “flavours” of SQL Server: Microsoft SQL Server Enterprise, Business Intelligence, Standard, Web, and Express.
So is Microsoft SQL Server Express the cheap and cheerful option? Well, it is certainly cheap in that it is free, but that doesn’t mean it is lacking in the essential features.
SQL Server Express is a full database engine, and contains many of the same features as the Standard and Enterprise versions. It can be deployed to a standalone database server in its own right, although this may be overkill considering the system resource limitations, or is quite often embedded with applications by the application vendor.
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. So 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 version of Microsoft SQL Server.
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 also 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.
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.
Can SQL Server Express be hosted?
Many hosting companies offer SQL Server hosting and generally this would be a multi-tenanted services where you share a server or even share an instance of SQL Server. Due to its limitations SQL Server Express wouldn’t be suitable for multi-tenanted hosting but would be fine in a dedicated environment.
If you’re looking to host SQL Server Express as a component for a desktop application (as opposed to a database for a website or web application) then take a look at using a hosted remote desktop service, often called hosted desktops. Here you would have a dedicated Windows remote desktop server with your application and SQL Server Express also running on that server. This provides for the best application performance and cuts out any latency between the application and the database, as well as enabling access to your applications from anywhere on any device.
If your application and data outgrow the limitations of SQL Server Express and you are using a hosted remote desktop services then it should be possible to easily migrate to SQL Server standard. The remote desktop provider will likely have multi-tenanted SQL servers available and can rent you a dedicated instance. By renting you pay so much per user per month giving you a lot more flexibility than paying a significant upfront cost of buying SQL Server Standard outright.
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.