Microsoft SQL Server comes in multiple versions and the price points vary dramatically. The entry level version is SQL Server Express which is a limited version but is free. We are often asked by clients if they need to buy a paid version of SQL Server since SQL can be quite expensive to purchase. The answer is generally going to be governed by the size of the server that is required. SQL Server Express is a good solution for smaller database loads and is very often used as a back end for small applications that run on a single machine. It can be used for smaller shared databases but the limitations often force multi-user databases to use a paid version of SQL Server.
If you are looking to install SQL Server you should look at these limits from not only the perspective of your present needs but also your future needs. A move from SQL Server Express to SQL Server Standard can be more costly then if you purchased SQL Server Standard at the start. The upgrade can be tricky and you should always have tested backup before the upgrade. Add in some downtime and it is worth looking at SQL Server standard if you think you may outgrow the free version in the near future.
Limitations
There are a number of limitations in terms of database size and resources that prevent SQL Server Express from being used for larger database loads. The most important limitation is that SQL Server Express does not support databases larger than 10 GB. This will prevent you from growing your database to be large. There are also memory limits that will prevent scaling to many users and heavy transaction loads that need to be looked at. The list below addresses the limitations and also shows the limits of the next step up which is SQL Server Standard Edition.
10 GB limit on Database Size: (SQL Server Standard allows for up to 524 PB)This is a hard limit but it is per database. These are some applications where you can split the data into multiple databases and then cross link the databases in order to have more then 10 GB of data on the server. In reality this is hard to manage and it will hurt your performance significantly. If you plan to grow your database Beyond 10GB you should consider this to be a showstopper.
If you hit the 10 GB limit you will likely have an outage. Hitting the 10GB limit will prevent any write transactions to the database and the database engine will return an error to the application when each write is attempted. At best you will get errors and be unable to add data until you purchase a SQL Server License and upgrade the server. At worst your application could crash and possibly cause data loss.CPU limited to lesser of 1 socket or 4 cores: (SQL Server Standard allows for 4 sockets or 24 cores) This means you cannot use the entire computing power of the CPU’s in your powerful server. This may or may not be important and will become a problem as you scale up to more transactions and more users.
Maximum buffer pool memory (per instance) limited to 1410 MB: (SQL Server Standard is 128 GB)
Maximum columnstore segment memory (per instance) limited to 352 MB: (SQL Server Standard is 32 GB)
Maximum memory-optimized data size (per database) limited to 352 MB: (SQL Server Standard is 32 GB)
Additional Feature Limitations
Log Shipping not supported
Database Mirrors limited to Witness Only
Backup Compression not supported
Failover Clusters not supported
Availability Groups not supported
Online Page and file restore not supported *
Online Index create and rebuild not supported *
Resumable online index rebuilds *
Online schema change not supported *
Fast recovery not supported *
Mirrored backups not supported *
Hot add memory and CPU not supported *
Encrypted backup not supported
Hybrid backup to Windows Azure not supported
* These features are also not available in Standard edition and require Enterprise edition.
An additional consideration to keep in mine with SQL Server Express is that it does not provide the SQL Server Agent. The Agent is the back end tool which allows for the automation of many tasks which include backup of databases and maintenance tasks. This means that if you use the Express version for a production database you will need to have someone run these tasks manually. This can be costly in time/labor and also requires a skilled SQL Server Admin to be in your system on a regular basis.
SQL Express Checklist
The following checklist is a good starting point to see if you are going to need SQL Server Standard. If you answer “Yes” to any of these you should be looking at SQL Server Standard. If you do not answer yes you should be doing additional assessment to be 100% sure you will not run into problems.
Is your database over 10GB?
Is your database under 10 GB but growing?
Will you have more than a few users?
Will your database need to support a heavy load of queries or updates?
Do you need to automate Backups within SQL Server?
Do you need to automate your database maintenance routine(s)?
Do you need to do log shipping?
Do you need to high availability on this database? (Mirroring, clustering, etc)
Do you need to encrypt or compress your backups?
Do you need to backup to the cloud or Azure?
More Information
For all the details on the versions of SQL and limitations you can review the Microsoft article here. The process can be complex and Capstan Services can help. Please call us at (469) 312-8100 if you want to discuss SQL Server options or if you would like assistance with your next SQL Server project.