If you find yourself asking, “Which Version of SQL Server should I use?”, you have landed on the right page.
This is the place where you will find all the answers you require to make your decision regarding which version of SQL server is best suited for your company’s needs. While you likely already know what SQL Server does, we will briefly touch upon this topic, as well as licensing models, while outlining the main differences between the three editions, enabling you to make a confident choice.
What is Microsoft SQL Server?
Microsoft’s SQL Server database is a full-featured relational database management system (RDBMS) that enables communication amongst a wide variety of software capable of transaction processing, business intelligence, and analytics applications in corporate IT environments.
Also referred to as a database engine, it is used by medium and large-sized enterprises to power any of their database storage and analysis requirements. SQL server’s role is to store and retrieve data as requested by other software applications. SQL Server software is a very sophisticated application due to the precipitous number of other software applications it must communicate with.
Typically companies that require SQL Server fall into one of two categories, a) organizations with a limited number of users and, b) organizations with thousands of users who access data via public access.
Server + Cal Model vs Core Model
Corporations with thousands or even millions of users such as a large organization like a credit card company or say, Amazon that provides access to the public, Microsoft offers SQL Server licenses by the server and includes a license for all users (as opposed to by the device). This is referred to as the core licensing model.
If your organization only has a limited number of users, such as on a private network, we recommend what is referred to as the Server + CAL model. This is where a separate license is required for each device that accesses the network.
Microsoft has seven versions of SQL Server currently supported and aimed at various customers and licenses several versions of the software targeting different audiences and serving a wide variety.
If you would like to learn more about what SQL is and its various pricing, please visit our SQL Knowledge Base article.
What is SQL Server Used for?
SQL Server stores and manages data that resides on a database server that can be used for a host of functions and comes with varying features depending on your needs, workload, and function. SQL Server also processes different kinds of data in terms of data warehousing, big data clusters, financial data, and performs data analysis.
SQL Server features include:
- Machine-learning services
- Data analytics
- Business Intelligence Studio
- Full-Text Search
See also: What is a Server Cluster?
How many versions of SQL are there?
Microsoft launched 21 versions of SQL in the period 1995-2019. The decision to license a particular edition from Microsoft can be challenging, it’s why choosing a trusted partner and reseller can be very important. Which Edition to choose is determined by multiple factors, including your particular budget, your server configuration, and your software applications. There are different licensing plans and finding the right edition requires taking all of these aspects into consideration.
SQL Server Editions
SQL Server Web edition is a low total cost-of-ownership option for Web hosts and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
SQL Server Standard edition delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premise and cloud, enabling effective database management with minimal IT resources.
The premium offering, SQL Server Enterprise edition delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence – enabling high service levels for mission-critical workloads and end-user access to data insights. This version has all the features required for big data.
SQL Server Express Edition is the free entry-level database version and is ideal for learning and building desktop and small server data-driven applications, but not meant for any serious computations, while also having incredibly limited features. SQL Server Evaluation Edition is the Enterprise Edition, however much like Express, it is highly limited and comes with an expiration date.
See also: SQL server cost comparison
How do I choose the right version of Microsoft SQL Server?
One of the most common questions we get asked is, “What version of Microsoft SQL do I need?”
There is always an ongoing debate between DBAs (database administrators) and their overlords (the ones who cut the check) as to which version to go with, the entry-level or the more expensive version. We call this debate: SQL Server Web vs Standard.
SQL Web Edition, in addition to its low cost, is a scalable and manageable option for both Windows as well as Linux. It enables small organizations to distribute web pages, services, and applications very quickly for internet-facing computations and actions. This is ideal for smaller companies such as web hosts and VAPs (virtual access points).
SQL Standard Edition is not quite as beefy as the Enterprise Edition, but offers many of the features of Enterprise, save for a few limitations. The purpose of this edition is to provide smaller organizations with the features and tools necessary and the need for less IT at a much lower price-point than the Enterprise Edition.
The Enterprise Edition is the most robust of the three, offering incredible database performance, an array of business intelligence tools, user access to data reporting, unlimited virtualization, along with a spectrum of additional features. For businesses requiring this type of solution, it is well worth the investment.
When choosing, you need to know what SQL server data tools you require. Here are some of the more common features that you may want to consider:
- Dynamic data masking
- SQL Server integration services
- Data quality services
- SQL server analysis services
- SQL server management studio
- Accelerated database recovery
- Database mail
- Tabular bi semantic model
- Business intelligence development studio
- reporting services features
With so many SQL Server versions to choose from, we have outlined for you the differences in features between all three. This is where you need to examine all of your requirements for both now and in the future. This future consideration could save time and money down the road.
See also: How to Install an SQL Server
SQL Server version comparison
SQL Server Web Edition features
A low-cost edition, powerful, yet limited and primarily geared towards web hosting and VAPs.
- SQL Management Studio and management packs allow you to manage your business data infrastructure
- SQL Server Agent, to schedule maintenance tasks like backups and monitoring status
- Built-in performance data collectors allow Administrators to collect performance-related data
- Automate various management tasks with the added support for Windows PowerShell
- Assess the impact of future upgrades of server, hardware, and operating system upgrades with Distributed Replay
- Support next-generation enterprise, web, business intelligence, mobile applications, and more with an array of development tools integrated into Visual Studio
- Optimize queries with DTA (Database Tuning Advisor)
- Content management support
- Buffer pool extension is not included
- Analysis services are included.
- This version is only available through an SPLA (Service Provider License Agreement) and not available by purchase
SQL Standard vs Enterprise
Both options offer rich programming tools, security solutions, and fast performance for mid-tier / middle-tier applications and data warehouses. Easily upgrade to Enterprise edition without altering any codes, both additions feature Basic integration services, however advanced sources and destinations, as well as advanced tasks and transformations, do not come with the Standard edition.
While Enterprise edition features master data services, Standard does not. You can access specialized cloud capabilities to increase efficiency for advanced analytics enterprise workloads, data protection applications and services that use advanced technology.
SQL Standard Edition Features
- Backup compression
- Support for up to two nodes on Always On
- Failover cluster instances
- Basic availability groups
- Encryption for backups
- Hybrid backup to Windows Azure (backup to URL)
- Clusterless availability group
- Buffer Pool Extension, which gives you the possibility to use an SSD or Ram device to dynamically increase the Buffer Pool
- Heterogeneous subscribers allow you to subscribe your SQL Server instance to non-SQL Server sources
- Transactional replication to Azure SQL Profiler (providing a user interface to the SQL Trace utility which gathers events as they occur in your SQL Server instance)
- MDX edit, debug, and design tools
- Send messages via the Service Broker
- Create cubes without a database
- Auto-generate staging and data warehouse schema
- Change data capture (records the DML activity on a particular table)
SQL Enterprise Edition features
- You have no limit on computing capacity and are allowed to use all the CPU’s available on your system
- Recover a partially corrupted database with Online page and file restore
- No limits on buffer pool size, Columnstore segment cache and memory-optimized data
- Always On Availability Groups – capacity to use up to 8 secondary replicas, including 2 synchronous secondary replicas
- Fast recovery in the initialization of databases
- Online indexing
- Master data services
- Modify tables Online with schema change
- Specify limits on resource consumption like memory, CPU and IOPS using the Resource Governor (allows you to NUMA Aware and Large Page Memory and Buffer Array Allocation)
- Automatic Tuning
- Resumable online index rebuilds
- Mirrored backups allow you to create 2 to 4 identical copies of a database backup
- Hot add memory and CPU
- Online non-clustered Columnstore index rebuild
- Batch Mode Adaptive Joins
- Batch Mode Memory Grant Feedback
- Transparent database encryption for data security
- Extensible key management (EKM), using the Microsoft Cryptographic API (MSCAPI) provider for encryption and key generation
- Parallel indexed operations
- Automatic use of indexed view by the query optimizer
- Oracle publishing
- Peer-to-Peer transactional replication
- Transactional replication updatable subscription
- Distributed partitioned views (allows you to use tables that reside in different databases which reside on different servers or different instances)
- Parallel consistency check
- SQL Server Utility Control Point
- StreamInsight Premium Edition with High Availability
- Advanced R integration
- Advanced Python integration
- Machine Learning Server
- Automatic star join query optimizations
- Scalable read-only Analysis Services configuration
- Parallel query processing on partitioned
- Tables and indexes that improve query performance on partitioned tables
- Global batch aggregation
SQL Server Comparison Table
The following table compares the scale limits between the three primary editions.
|Maximum memory utilized per instance of SQL Server Database Engine||64 GB||128 GB||O/S Maximum|
|Maximum Memory utilized per instance of analysis||N/A|
Tabular: 16 GB
MOLAP: 64 GB
|Maximum compute capacity used by a single instance – SQL Server Database Engine||Limited to 4 sockets or 16 cores||Limited to lesser of 4 sockets or 24 cores||O/S Maximum|
|Maximum compute capacity used by a single instance – Analysis Services of Reporting Services||Limited to 4 sockets or 16 cores||Limited to lesser of 4 sockets or 24 cores||O/S Maximum|
|Maximum memory utilized per instance of Reporting Services||64 GB||64 GB||O/S Maximum|
|Maximum relational database size||524 PB||524 PB||524 PB|
See also: Cloud SQL databases
ServerMania has been helping businesses with their data and storage needs for more than 20 years and is committed to providing customers with reliable, satisfying experiences on each new and used software or hardware product they require. We have eight data centers around the world to serve all of your needs.
Book your consultation today with one of our knowledgeable reps and find out which version of SQL Server meets your needs best.