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
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 |
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.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. * 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 | |||
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.
- Always use the latest version of SQL if you are buying new. Currently, that means SQL 2014.
- Use the 64-bit version if possible.To use the 64-bit version of SQL requires a 64-bit version of Windows.
- 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.