SQL Server Version Reference

Cyrious Control uses Microsoft SQL Server as its database engine. The intent of this WIKI is to provide basic information on the Microsoft SQL products and their applicability to Cyrious Control. Links to the installation files for all versions of SQL Server that Microsoft offers can be found on this page.

How to determine what version of SQL Server you are using

A simple way to do this is to right click on your database in SQL Server Management Studio and choose New Query.
From there, run the following query:
SELECT @@version

SQL Server Version Query.png

Supported Versions and Editions

Microsoft has released several SQL Server versions and editions over the years. The following editions are or have been supported by Cyrious as noted:
Version
Editions
Support in Cyrious
2000
(v 8.0)
MSDE (SQL 2000 Express)
SQL 2000 Workgroup
SQL 2000 Standard
SQL 2000 Enterprise
No longer supported as of Control 5.1
2005
(v 9.0)
SQL 2005 Express
SQL 2005 Workgroup
SQL 2005 Standard
SQL 2005 Enterprise
No longer supported as of Control 5.5
2008
(v10.0)
SQL 2008 Express
SQL 2008 Workgroup
SQL 2008 Standard
SQL 2008 Enterprise
No longer supported as of Control 5.5
2008 R2
(v10.5)
SQL 2008 R2 Express
SQL 2008 R2 Workgroup
SQL 2008 R2 Standard
SQL 2008 R2 Enterprise
Supported through 2018
2012
(v11.0)
SQL 2012 Express
SQL 2012 Standard
SQL 2012 Business Intelligence
SQL 2012 Enterprise
Supported
2014
(v12)
SQL 2014 Express
SQL 2014 Standard
SQL 2014 Business Intelligence
SQL 2014 Enterprise
Supported
2016
(v13)
SQL 2016 Express
SQL 2016 Standard
SQL 2016 Enterprise
Supported
2017
(v14)
SQL 2017 Express
SQL 2017 Standard
SQL 2017 Enterprise
Supported

2015-04-13 15_57_52-Microsoft SQL Server Version List.png

32-bit v 64-bit Versions

With the advent of 64-bit CPUs, Microsoft has released 64-bit versions of most editions of SQL. When you have a 64-bit CPU and a 64-bit Operating System in place, Cyrious recommends use of a 64-bit version of SQL. The performance gains from 64-bit SQL have been noticeable, though we have not quantified them in concrete terms.

Which Edition Do You Need

Microsoft produces a variety of versions (at different price points) and knowing which one to use can be confusing. Here are some pros and cons of each:

Express Edition

Pros
  • Free!
  • All that is needed for small databases.
Limitations
  • Does not support active directory authentication for users on a domain.[1]
  • SQL will only use 1 processor (up to 4 cores), no matter how many processors you have.
  • SQL will only use 1 GB of memory, no matter how much you have.
  • SQL database size is limited to 4GB in SQL 2005 and SQL 2008, 10 GB in SQL 2008 R2, 2012, 2014.
Comments
  • For users with less than 3-5 users and a small amount of data this works well.
  • As the amount of data grows, users will probably need to upgrade to the Workgroup or standard version of SQL.
  • Warning this version is not recommended for any installations that will occur on a virtual machine.
Source
  • Available free from Microsoft here.

Standard Edition

Pros
  • Will take advantage of additional processors and memory.
  • Supports 128GB memory usage (SQL 2014).
  • Supports 528 PB database size (SQL 2014).
  • Support Active Directory authentication for domain users.
  • Works well for larger databases.
Limitations
  • SQL will only use the lesser of 4 sockets of 16 cores.
Comments
  • Good solution for most larger users.
Price
  • Typically around $2800 for the first 10 CALs.
  • Additional 5-pack of CALs for $750.
Source

Cyrious Recommendation

The exact version will depend on the number of users you have, the volume of business you are doing, and the responsiveness you desire. However, in the interest of simplicy, Cyrious recommends this SQL rule-of-thumb. Like most rules-of-thumb, it is helpful as a guideline but you should check with your IT administrator for more precise guidance.

  1. Always use the latest version of SQL if you are buying new. Currently, that means SQL 2014.
  2. Use the 64-bit version if possible.[2]
  3. Add the number of users and the years of data you have in the system. (For example, a 7 users system with 3 years of data would yield 10).
    • If that number is < 7, use SQL Express with a 4 core CPU, 4-8 GB server.
    • If the number is between 10 and 20, use SQL Standard on a 4+ core CPU, 4-8 GB server.
    • If that number is > 35, use SQL Standard with a powerful server running dual 4+ core CPU, 8-16 GB server.
    • For values not covered, consider your usage and response requirements to judge whether to go to the higher or lower edition that bounds that value.

See Also


  1. ^ Cyrious does not currently support the use of domain authentication so this is not a current limitation though it could become one in the future.
  2. ^ To use the 64-bit version of SQL requires a 64-bit version of Windows.