Anyone who hasn’t had IT experience is most likely confused about what Microsoft’s SQL Server is and what it’s used for. Chances are good they may never have heard of it. The Microsoft SQL Server is Microsoft's relational database management system (RDBMS). SQL Server is a full-featured database designed to compete against its main competition, Oracle Database (DB) and MySQL.
Like all major RBDMs, SQL Server supports ANSI SQL, which is the standard SQL language. SQL Server includes T-SQL, which is its SQL implementation. SQL Server Management Studio (SSMS) is SQL Server's interface tool. It supports 32-bit and 64-bit environments. You might also see SQL Server referred to as MSSQL or Microsoft SQL Server.
Like other RDBMS, Microsoft SQL Server is built on top of SQL, a standardized programming language that database administrators (DBAs) and other IT professionals use to manage databases and query the data they contain. SQL Server is tied to Transact-SQL (T-SQL), an implementation of SQL from Microsoft that adds a proprietary programming extension to the standard language. This is part of what makes it different.
How Many Versions/Editions of Microsoft SQL are there?
There are many different versions and a few editions of Microsoft SQL Server. It can get a little tricky to keep up with. Between 1995 and 2016, Microsoft released 10 versions of SQL Server. The early versions were designed for departmental and workgroup applications. Microsoft expanded SQL Server's capabilities to make it an enterprise-class relational DBMS that could compete with Oracle Database, DB2, and other popular databases.
Microsoft added data management and data analytics tools into SQL Server over the years, plus new functionality that supports new technologies that have appeared, including the Internet, mobile devices, and cloud integration.
The various SQL Server products offer different feature sets and a variety of pricing and licensing options. Determining which edition is right for you can be hard, which is why a trusted Microsoft partner and software reseller like SoftwareKeep can be so important. Here are some of the editions of SQL Server available:
- Enterprise: This is the top-of-the-line edition with a full feature set, ideal for large businesses and corporations with extensive server needs.
- Standard: Fewer features than Enterprise, but it does have all the basics and is ideal for mid to large companies that don’t need advanced features.
- Workgroup: The edition is ideal for companies that have remote offices. All of the basic features are here.
- Web: This edition is designed specifically to work with web applications.
- Developer: Similar to Enterprise, but it can only be licensed to only one user for development and testing. This edition can be upgraded to Enterprise without the need to reinstall.
- Express: A free entry-level database. It can utilize only 1 CPU and 1 GB of memory; the maximum database size is 10 GB.
- Compact: Free embedded database for mobile application development. The maximum size of the database is 4 GB.
- Datacenter: The major change in the new SQL Server 2008 R2 is Datacenter Edition. The data center has no memory limitation and supports more than 25 instances.
- Business Intelligence: Business Intelligence is new in SQL Server 2012. It includes all the features of the Standard Edition plus support for advanced BI features like Power View and PowerPivot. It doesn’t have support for advanced availability features like AlwaysOn Availability Groups and some other online operations.
- Enterprise Evaluation: The SQL Server Evaluation Edition lets you get a fully functional and free instance of SQL Server to learn it and see if it is right for you. It expires after 6 months.
The edition you choose depends on many factors, including your budget, specific needs, and other hardware and software you are using. There are different licensing plans and finding the right edition requires taking all of these factors into account. Let’s take a little deeper look at the purpose and uses for some of the widely used editions and versions.
SQL Server 2012 Standard: The Standard Edition of SQL Server is the most popular for big business database operations. It does have what you need in most cases. It handles up to 16 cores and has an unlimited amount of RAM.
In SQL Server 2012, there is a major licensing change from 2008. A per-core option became available. That means you can purchase per core license or purchase a server license with client access licenses. This is a great way to get a highly powerful version of SQL Server that fits the needs of most businesses, offers a full feature set, and fits comfortably into a wide budget range.
SQL Server 2012 Enterprise: Requires a bigger budget, but this is a crucial edition if you are a mission-focused database-driven company. If you have the newer hardware and software available to you, newer versions of these editions are available (2014 and 2016) offering a richer feature set.
If your budget allows, 2016 is a good bet because it is designed to handle the needs of large corporations with demanding server needs, but for companies with tighter budgets, SQL Server 2014 will provide you with plenty of server power.
Upgrading to SQL 2017
Since SQL Server 2012 fell out of Mainstream Support in 2017 and SQL Server 2014 falls out of mainstream support in, 2019, and since SQL Server 2008 and SQL Server 2008 R2 OSE on extended support in 2019, you may want to upgrade your server from these Legacy versions of SQL Server to a more modern version. This could be version 2016, but if you have the budget to go for the latest version, SQL Server 2017 could be the best fit for your company.
New Engine Features in SQL Server 2017:
- CLR Assemblies – These can now be whitelisted in SQL Server 2017
- Resumable Online index Rebuilds – When an index is interrupted due to failover, it can now be resumed.
- IDENTITY_CACHE – This helps avoid gaps in the values of identity columns
- Query Processing Improvements – New processing improvements that adapt optimization strategies to application workloads.
- Automatic Database Tuning – Analyzes query performance problems and will recommend solutions to the problems.
- Graph Database Capabilities – Modeling features for relationships between tables.
- Dynamic Management Views – New management views.
- Database Tuning Advisor – New options for improved performance.
- Database Scoped Credentials – Improved security measures.
- Automatic Plan Correction (relies on the Query Store feature introduced in SQL Server 2016). It helps detect and automatically correct query plan stability issues.
- Adaptive Query Processing (AQP) (enabled with compatibility mode 140) and is helpful with batch mode operations used with Columnstore indexes. AQP has three components, which include Batch Mode Adaptive Memory Grant Feedback, Batch Mode Adaptive Joins, and Interleaved Execution for Multi-Statement Table Valued Functions.
How to choose the version of Microsoft SQL Server that's right for me
Which version or edition of Microsoft SQL Server is right for you depends on what your needs are, how up-to-date your hardware is, and what your company budget is. These factors can help you determine the Microsoft SQL Server you need. The Standard Edition seems to be a good place to begin for many companies. SQL Server Standard Edition handles up to 16 cores with an unlimited amount of RAM. There is a per-core licensing structure that gives you two choices: Purchase per core licenses or a server license along with client access licenses. This is a great option for many business types and sizes.
Windows Server or Windows SQL Server
One question frequently asked is whether you should choose Windows Server or Windows SQL Server. SQL Server runs on top of your Windows Server. SQL Server is an RDBMS software (application) that requires a Windows OS to operate. SQL Server is strictly database management. Windows Server is a Windows Server operating system.
Differences between Versions of Windows SQL Server
Here is a comparison look at SQL Server versions from 2008 until 2017:
- In-memory OLTP is supported from 2014 on.
- In Memory, ColumnStore supported from 2012 forward.
- Real-time Operational Analytics in 2016 and 2017 only.
- Buffer Pool Extension to SSD available from 2014 forward
- Adaptive Query Processing only in 2017
- Basic Availability Groups only in 2016 and 2017.
- Transparent Data Encryption in all versions.
- Back-up encryption support from 2014 forward.
- Dynamic Data Masking and Row Level Security in 2016 and 2017.
- Separation of Duties from 2012 onward.
- Back-up to Azure available from 2012 to 2017
- Disaster recovery to Azure from 2014 onward.
- Optimized virtual machine images in Azure gallery from 2012 onward.
- Stretch Database in 2016 and 2017.
- Runs on Linux and Docker Containers 2017 only.
- Temporal tables in 2016 and 2017.
- JSON support in 2016 and 2017 only.
- Graph data support in 2017 only.
- Integration services were managed as a server from 2012 forward.
- PolyBase for T-SQL queries across Hadoop in 2016 and 2017.
- Tabular BI semantic model in all versions.
- Master data services in all versions.
- Data quality services in all versions except 2008.
- In-database advanced analytics in 2016 and 2017.
- End-to-end mobile BI on any device in 2016 and 2017.
Final Thoughts
If you’re looking for a software company you can trust for its integrity and honest business practices, look no further than SoftwareKeep. We are a Microsoft Certified Partner and a BBB Accredited Business that cares about bringing our customers a reliable, satisfying experience on the software products they need. We will be with you before, during, and after all the sales.
Keep Learning
» The Ultimate Guide to Windows Server
» SQL Server 2019 Install Guide
» How to Protect Your Data Online