This article from InSource addresses the SQL Server Express version and discusses its limitations.
- Author: Bruce Telford
- Published: 03/27/2018
- Applies to: System Platform 2014 R2 & 2017
Microsoft SQL Server with Wonderware Products plus References (3/27/2018 … this will likely be dated in a few years)
The removal of Microsoft SQL Server Standard license in larger size Wonderware products that was announced on February 15, 2017 continues with the 2017 release. Therefore, Microsoft SQL Server Standard license will not be included in 2017 System Platform, System Platform Starters, Historian, Dev Studio, Advanced Studio sizes 25000 and smaller and will not be included in 2017 Information Server. The System Platform, Historian, and DEV Studio will continue to include MS SQL Server Standard & CAL Licenses.
I believe, if DVD’s for it are needed, then be sure to order the appropriate DVD Media Kit. I believe we now offer media kits that include MS SQL Server DVD and media kits that do not. (this may need confirmation)
If you do install SQL Server Express install “SQL Server Express with Advanced Services” on Historians (and only 64bit) this way you can get SSRS if required.
SQL Server Express Advanced (SQLEXPRADV) download links …
- SQL Server 2014 Express with Advanced Services
- SQL Server 2014 SP1 Express with Advanced Services
- SQL Server 2014 SP2 Express with Advanced Services
- SQL Server 2016 Express (All)
“Microsoft SQL Server Express is Free”
It is very capable these days in my opinion:
- The Tier 2 Historian must have full version because we/others probably want to use the SQL Agent to harvest KPI other information from the Historian tags, and in time it could be bigger.
- The Tier 1 Historians can use Express as long as the tag count does not exceed 50k (I believe?). There is also a database size and configuration limitations (see limitation links below), but The Historian Database does not get big only the Extension tables do and they are files external to the database. In Express versions the SQL Server Agent is not supported, nor is the SQL Profiler, no SSIS designer and runtime, and no email are key takeaways. I would guess that for most clients/customers that are using Historian out-of-the-box this is fine, but for extensibility no SQL Agent could be a blow.
I believe, you have options as to the version compatibility (see WW Notes below), but SQL 2016 has more reporting capability in SSRS than before, like dashboards.
Limitations/Comparisons (links below):
A side note for developers SQL 2016 “Developer” (was free last time I checked) and fully functional, but for development only, NOT for Production
SQL Tips for Better Performance
There are many sites dedicated to server and query optimization, most are great and can be rather detailed. However, here are some general guidelines.
- Separate SQL Server, SQL Data and SQL Log files into separate partitions (spindles, if possible)
- Allow tempdb room for expansion of the disk
- Clamp Memory to about 75-80% of total Memory in SQL 2012 and higher
- Maintenance has a built-in interface in the SMSS, Backup, DBCC Check DB, Reindex, Update statistics, are all good things to do at some regular frequency depending on database usage.
- As in above Reindex and/or Reorg indexes regularly, Microsoft recommends that if indexes are fragmented between 5% - 30% the ‘ALTER INDEX REORGANIZE’ them, if greater than 30% then ‘ALTER INDEX REBUILD WITH (ONLINE = ON)’ routine can be built via SQL scripts to do this.
Extracts from the Wonderware System Platform 2017 readme file, and some info on SQL Express
- Windows Server 2016 System Requirements
- Windows Server 2012 R2 System Requirements
- Windows Server 2012 System Requirements
- Windows 10 System Requirements
Note: Wonderware System Platform 2017 is not supported on Windows 10 versions prior to version 1607 (Anniversary update; OS build 14393).
SQL Server requirements
- Hardware and Software Requirements for Installing SQL Server 2016
- Hardware and Software Requirements for Installing SQL Server 2014
- Hardware and Software Requirements for Installing SQL Server 2012
System Requirements by installation size
Small installation (1 to 25,0000 I/O per system):
- Any operating system, database, and virtualization software supported by Application Server.
- Exceptions: None.
Medium installation (25,000 to 50,000 I/O per system):
- Any operating system supported by Application Server.
- Exceptions: 32-bit versions of Windows 8.1 are not supported (64-bit versions are supported).
- Any database software supported by Application Server.
- Exceptions: SQL Express-SSMSE versions are not supported.
- Any virtualization software supported by Application Server.
- Exceptions: None
Large installation (50,000 to 400,000 I/O per system):
- Only Windows Server operating systems (for example, Windows Server 2016) supported by Application Server.
- Client operating systems, such as Windows 8.1 and Windows 10, are not supported for large installations.
- Any standard or enterprise version of database software supported by Application Server.
- Exceptions: SQL Server Express-SSMSE versions are not supported.
- Any virtualization software supported by Application Server
- Exceptions: None
Considerations for SQL Server Express
- SQL Server Express is supported for use on an InTouch, Application Server, or Historian node, recommended for use only in small or development configurations. SQL Server 2014 Express with Tools SP1 is automatically installed for when you install for InTouch, Application Server, or the Historian, if, at time of installation, no other SQL Server elements are installed on the computer.
- If you plan to use SQL Server Express with Wonderware Information Server and Wonderware InTouch HMI on the same node, the following limitations apply:
- Installing Wonderware Information Server first, using the MS SQL Express default instance name set to "SQLEXPRESS", and then installing Wonderware InTouch HMI will work without issue. Note that in this scenario, each component uses a dedicated instance of SQL Server 2014 Express with Tools SP1.
- Installing Wonderware InTouch HMI first, which silently installs and configures SQL Server 2014 Express with Tools SP1, and then installing Wonderware Information Server will not allow Wonderware Information Server to work. In this case, during the Wonderware Information Server configuration, an error message will appear in the Configuration Utility stating: "SQL Server client components not found." To work around this issue, configure Wonderware Information Server to use an instance of SQL Server Express (or a non-Express edition) on a remote node.
- The computing capacity of SQL Server Express 2014 with Tools SP1 is limited to the lesser of one CPU socket or four processor cores.
- For InTouch HMI-only installations, the Wonderware System Platform installer will automatically install SQL Server Express 2014 with Tools SP1 if these conditions are met:
- No version of SQL Server is installed on the computer at the time of installation.
- You select only the InTouch Development and Runtime Wonderware System Platform installation option. When you select InTouch Development and Runtime, a Galaxy Repository will be installed.
Additional SQL Server Notes for Wonderware Application Server
- If multiple versions of SQL Server are installed, the one used as the Galaxy Repository must be the default instance. Named instances are not supported.
- The Galaxy Repository locks the SQL Server maximum memory usage to 65% of the computer's physical memory.
- TCP/IP must be enabled on the computer hosting a SQL Server database. The TCP/IP protocol setting can be verified from the SQL Server Network Configuration under SQL Server ConfigurationManager.
- To use the Alarm DB Logger with SQL Server Express, you need to change the default authentication mode from Windows-based to Mixed Mode.