TN WW205 Microsoft SQL Server with Wonderware Products plus References

Last updated
Save as PDF
Share
1. Share

InSource_Logo_Transparent (1).png

Description

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

Details

Microsoft SQL Server with Wonderware Products plus References (3/27/2018 … this will likely be dated in a few years)

Since 02/15/2017

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)

Generally

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 …

“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.