I have mostly seen standard and enterprise editions in customer environments. However there are few customers who keep SQL Server Express edition and later understand its limitations. One customer wanted to upgrade his express edition and wanted to know major limitations of express editions so that he can convince his management for edition upgrade.
So Below are few of the considerable limitations:
Express edition of SQL Server is free and entry-level edition. It is fine to be used for learning & for building desktop/small server data-driven applications. If customers need more advanced database features, SQL Server Express edition could be easily upgraded to standard or enterprise editions.
SQL Server Express Edition limitations:
- Compute Capacity : Express edition could utilize to lesser of 1 socket or 4 cores even if server is having more sockets and cores available.
- Maximum memory for buffer pool per instance of SQL Server Database Engine : 1410 MB
SQL Server buffer pool memory is a memory cache used for caching data and index pages for the entire SQL instance. The real purpose of buffer pool is to reduce database file I/O and improve the response time for data retrieval. Other caches in SQL Server memory like procedure cache, thread stack, backup buffers etc. consume memory outside buffer pool memory. The memory consumed by other caches outside buffer pool is not limited by above memory limit and can grow up to limits depending on “max server memory”.
- Buffer pool extension not available: The buffer pool extension feature extends the buffer pool cache with nonvolatile storage (mainly SSD) for better I/O performance. Due to this extension, the buffer pool can accommodate a larger database working set.
- Maximum database size : 10 GB
- High availability & Disaster Recovery Solutions Not available : Log shipping , Always On availability groups, replication not available.
- Backup Comression not available
- Encrypted backup, TDE not available
- SQL Server Agent is not available. Task Schedulers could be utilized for automating tasks like backups, database maintenance etc.