Microsoft SQL Server Express edition
- There is no requirement of a license for using it, as it is free for distribution.
- It is an easy to use version, designed for building simple data-driven applications up to 10 GB in size.
- Includes SQL Server Management Studio. This helps administrators and developers of different skill levels use SQL Server efficiently.
- Applications develop faster through the deep integration with Visual Web Developer, Visual Studio, and so on.
- Enhanced reporting services help create and share reports that answer complex questions through rich visualizations.
- Easy backup and restore functionality to Microsoft Azure.
- It comes with same monitoring tools that come with Standard and Enterprise editions.
- It comes with various security features – row-level security, always encrypted, dynamic data masking, basic auditing, and fine-grained auditing. One also gets features like user- defined roles and contained databases.
- It allows one to install Machine Learning services with both Python and R language.
- It allows you to scale your applications across Server editions as you grow – without modifying application code.
- It’s completely supported by Microsoft, including updates and patches from time to time.
- SQL Server Express can be installed on a server with many CPUs. But, it can use only one CPU at a time.
- 4GB database size limit was there for SQL2005/2008 but after 2008R2 it has been increased to up to 10GB for each database. The limit is applicable on database data files and not log files.
- Can only use a maximum of 1410 MB memory per instance.
- There’s no SQL Server Agent with the Express edition.
- The performance analysis tool, Profiler, is not included with the SQL Server Express edition.
- The functionality to create and attach a schedule to a job (Job Scheduler) is not available with the express edition.
- Analysis and Integration Services are also not provided, but you can import or export the data with SQL Server Import and Export Wizard feature available with the SQL server express.
Microsoft SQL Server Web edition
- You can centrally manage your business infrastructure with its SQL Management Studio and management packs.
- Provides an added support for Windows PowerShell to automate various management tasks.
- Has robust development tools integrated into Visual Studio to support next-generation enterprise, web, business intelligence, mobile applications and so on.
- You get Distributed Replay feature that helps you assess the impact of future upgrades of server, hardware and operating system upgrades.
- You get SQL Server Agent, which can be used for scheduling maintenance tasks like backups and monitoring their execution status.
- You can optimize queries with DTA (Database Tuning Advisor).
- If you are a DB administrator, you can collect performance related data using in-build performance data collectors.
- Provides strong support for content management.
- Buffer pool extension is not available.
- Analysis services are not there.
- Not available to be purchased by the public. It is only for the web hosting providers through an SPLA (Service Provider License Agreement).
Important differences between MS SQL Server Express edition and Web edition
- Cross box scale limits:
Feature name Web edition Express edition Maximum memory utilized 64 GB 1410 MB Maximum compute capacity Limited to < 4 Sockets or 16 cores Limited to < 1 Socket or 4 cores Maximum relational database size 524 PB 10 GB
- Management tools:
Feature name Web edition Express edition Distributed replay – Admin tool Yes No Distributed replay – Client Yes No Distributed replay – Controller Yes (1 Client) No SQL server agent Yes No Microsoft System Center Operations Manager Management Pack Yes No
- RDBMS Manageability:
Feature name Web edition Express edition User instances No Yes Dedicated admin connection Yes Yes (with trace flag) Policy automation Yes No Performance data collector Yes No Standard performance reports Yes No PowerShell scripting support Yes Yes Direct query of indexed views Yes Yes
Feature name Web edition Express edition Full-text and semantic search Yes No Basic R & Python integration Yes No JSON Yes Yes Native XML support Yes Yes Specification of language in query Yes No Transact-SQL endpoints Yes No
- Reporting services:
Feature name Web edition Express edition Supported catalog database Yes Yes Supported data source Yes Yes Role-based security Yes Yes (with advanced services) Export to Excel, PowerPoint, Word, PDF & images Yes Yes (with advanced services) Report server Yes Yes (with advanced services) Report designer Yes Yes (with advanced services)