PI System and Microsoft SQL Server Requirements. March, 2013

(1)

PI System and Microsoft SQL Server

Requirements

(2)

OSIsoft, LLC

777 Davis St., Suite 250 San Leandro, CA 94577 USA Tel: (01) 510-297-5800 Fax: (01) 510-357-8136 Web: http://www.osisoft.com

No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, mechanical, photocopying, recording, or otherwise, without the prior written permission of OSIsoft, LLC.

OSIsoft, the OSIsoft logo and logotype, PI Analytics, PI ProcessBook, PI DataLink, ProcessPoint, PI Asset Framework (PI AF), IT Monitor, MCN Health Monitor, PI System, PI ActiveView, PI ACE, PI AlarmView, PI BatchView, PI Coresight, PI Data Services, PI Event Frames, PI Manual Logger, PI ProfileView, PI WebParts, ProTRAQ, RLINK, RtAnalytics, RtBaseline, RtPortal, RtPM, RtReports and RtWebParts are all trademarks of OSIsoft, LLC. All other trademarks or trade names used herein are the property of their respective owners.

U.S. GOVERNMENT RIGHTS

Use, duplication or disclosure by the U.S. Government is subject to restrictions set forth in the OSIsoft, LLC license agreement and as provided in DFARS 227.7202, DFARS 252.227-7013, FAR 12.212, FAR 52.227, as applicable. OSIsoft, LLC.

(3)

Executive Summary ... 1

Overview ... 2

PI System Components with Dependency on Microsoft SQL Server ... 3

Microsoft SQL Server Features ... 4

PI AF Dependency on Microsoft SQL Server ... 6

CPU and Memory (RAM) Requirement ... 6

Database Size Requirement ... 6

SQL Server Features Used by PI AF ... 7

Business Intelligence (BI) ... 8

Microsoft StreamInsight ... 10

Other Notable SQL Server 2008 R2 Features ... 11

Other Notable SQL Server 2012 Features ... 12

(4)

(5)

Executive Summary

To fully utilize the capabilities of the PI Server, Microsoft SQL Server is required to support several PI Server components including, but not limited to, PI AF. As there are numerous SQL Server editions with different feature sets and many of the SQL Server features impact the performance and the ability to extract the most out of your PI Server, it is important to select the proper SQL Server edition to serve your present needs and future expansion of your PI Server. The table below summaries the PI Server and native SQL Server features that are important to your optimal usage of the PI Server, along with the required or recommended SQL Server edition. Additional details are provided later in this document. In general, for optimal performance of the PI Server under most conditions, SQL Server Enterprise is recommended.

Features Recommended SQL Server

2008 R2 Edition

Recommended SQL Server 2012 Edition

100 or more simultaneous client connections to PI AF

Enterprise Enterprise

50,000 or more PI AF elements Enterprise Enterprise

1,000,000 or more PI Event Frames Enterprise Enterprise PI AF High Availability (PI AF HA Primary) Enterprise Standard Microsoft StreamInsight – <5000 events per

seconds or > 5 seconds latency

Available with Standard Available with Standard

Microsoft StreamInsight – > 5000 events per second or < 5 seconds latency

Available with Datacenter Available with Enterprise

Administering and managing multiple SQL Server databases

Enterprise Standard

Performing Business Intelligence (BI) with your PI data using SQL Server Analysis Services

Enterprise Business Intelligence

PowerPivot sharing via SharePoint Enterprise (Required) Enterprise (Required) Building reports with your PI data using SQL

Server Reporting Services

(6)

PI System and Microsoft SQL Server Requirements

2

Overview

With the introduction of the PI Server 2010, the PI Server is a bundle of components consisting of the PI Data Archive, PI Asset Framework (PI AF), PI Event Frames1,_{PI Advanced Computing Engine (PI ACE), PI}

for StreamInsight2_,_{PI Notifications, PI Totalizer, PI Alarms, PI Performance Equations, PI Real-time SQC,}

MCN Health Monitor, PI System Access and PI System Management Tools. PI High Availability (HA) for the PI Data Archive is the only server option that can be added. In addition, starting with PI Server 2010 R3, PI AF is now an integral part of the PI Server for all customers. The PI Server along with PI client products make up the PI System with many of these components and clients relying on Microsoft SQL Server thus making it a requirement for the PI System. The purpose of this document is to provide a guide to Microsoft SQL Server requirements for the PI System.

1_{PI Event Frames is introduced with PI Server 2010 R3}

2_{With PI Server 2012, PI for StreamInsight requires product manager approval. See Microsoft StreamInsight section for} information on Microsoft StreamInsight and PI for StreamInsight.

(7)

PI System Components with Dependency on Microsoft SQL Server

PI System Components with Dependency on Microsoft SQL

Server

The PI System components with a direct dependency on SQL Server include PI AF, PI Event Frames, PI Notifications, PI for StreamInsight3_{, PI Manual Logger and PI Coresight. As PI Notifications and PI Event}

Frames are built on PI AF, for the purpose of this document, they will be considered part of PI AF and not be discussed as separate products. The different SQL Server editions provide a different feature set that affect (limit) the functions of the PI System components. Tables 1 and 2 summarize the impact of SQL Server 2008 R2 and SQL Server 2012 editions on PI System component functionality.

PI System Components SQL Server Express SQL Server Standard SQL Server Enterprise SQL Server Datacenter PI AF Reduced functionality Reduced functionality

Full functionality Full functionality

PI Manual Logger Full functionality Full functionality Full functionality Full functionality PI Coresight Full functionality Full functionality Full functionality Full functionality

Table 1. The impact of SQL Server 2008 R2 editions on PI System component functionality

PI System Components SQLServer Express SQL Server Standard SQL Server Business Intelligence SQL Server Enterprise PI AF Reduced functionality Reduced functionality

Reduced functionality Full functionality

PI Manual Logger Full functionality Full functionality Full functionality Full functionality PI Coresight Full functionality Full functionality Full functionality Full functionality

Table 2. The impact of SQL Server 2012 editions on PI System component functionality

As PI Manual Logger and PI Coresight retain full functionalities with any of the SQL Server editions listed in Tables 1 and 2, they will not be discussed any further in this document.

(8)

4

Microsoft SQL Server Features

Tables 3 and 4 summarize the Microsoft SQL Server 2008 R2 and SQL Server 2012 features that are currently used in one or more PI System components. This is not intended to be an exhaustive list of SQL Server features. For SQL Server 2008 R2, there are two (2) SQL Server Premium Editions;

Datacenter and Parallel Data Warehouse, two (2) SQL Server Core Editions; Enterprise and Standard, and six (6) Other Editions including Express. The SQL Server Premium Editions are not included in this table as they provide all available features of SQL Server and satisfy all PI System needs. Of the six (6) Other Editions, only Express is included in Table 3 as the remaining five (5) are for more unique environments and less applicable for PI Systems. For SQL Server 2012, Microsoft has revised their lineup of SQL Server editions to include three (3) main SQL Editions; Standard, Business Intelligence and Enterprise, two (2) specialized editions targeting specific workloads and three (3) free editions including Express. Only the three main SQL Server 2012 editions along with Express are included in Table 4 as the remaining editions are less mainstream and designed for specialized and/or unique environments.

SQL Server 2008 R2 Features SQL Server

Express

SQL Server Standard

SQL Server Enterprise

Maximum number of CPUs 1 4 8

Maximum memory utilized 1 GB 64 GB 2 TB

Maximum database size 10 GB 524 PB 524 PB

Database mirroring Witness only Yes (single thread,

synchronous only)



Automatic corruption recovery from mirror  

Backup compression  

Mirrored backups  

Fast recovery  

Online indexing  

Snapshot replication Subscriber only  

Transactional replication Subscriber only  

SQL Server Agent  

SQL Server Profiler  

SQL query, edit and design tools  

XML/A support  

Complex Event Processing (StreamInsight) Standard Premium

(9)

Microsoft SQL Server Features

SQL Server 2012 Features SQL Server Express SQL Server Standard SQL Server Business Intelligence SQL Server Enterprise Maximum number of CPUs Lesser of 1

socket or 4 cores Lesser of 4 sockets or 16 cores Lesser of 4 sockets or 16 cores OS maximum

Maximum memory utilized 1 GB 64 GB 64 GB OS maximum

Maximum database size 10 GB 64 GB 64 GB OS maximum

Database mirroring Witness Only Yes (Safety Full Only)

Yes (Safety Full Only) 

Failover Clustering 4 4 

Backup compression   

Online indexing 

Data compression 

Transactional replication Subscriber   

Snapshot replication Subscriber   

SQL Configuration manager    

SQL Command prompt tool    

SQL Server Management Studio   

SQL Profiler   

SQL Server Agent   

Dedicated admin connection Yes (under trace flag)

  

Parallel indexed operations 

Native XML support    

Date and Time datatypes    

Internationalization support    

Complex Event Processing (StreamInsight)

Standard Premium

Table 4. Comparison of Microsoft SQL Server 2012 features used in the PI System

4_{This feature is only available for 64-bit SQL Server 2012, and for 32-bit SQL Server 2012 when Address Windowing Extension} (AWE) is enabled.

(10)

6

PI AF Dependency on Microsoft SQL Server

Customers often seek guidance from OSIsoft as to the proper edition of SQL Server to use with their PI AF. The extreme flexibility of PI AF makes this seemingly simple question quite difficult to answer. The following criteria can be used to determine the proper SQL Server edition for PI AF:

1. CPU 2. RAM

3. Database size 4. PI AF features

CPU and Memory (RAM) Requirement

In addition to the RAM requirement for PI AF objects, PI AF utilizes stored procedures in SQL Server to perform various tasks. Each stored procedure requires different amounts of processing power (CPU) and memory (RAM). Therefore each client connection to PI AF utilizes some amount of CPU and RAM. Since each client may use PI AF differently, it’s difficult to correlate the number of users to CPU and RAM requirement. In house tests have shown that for optimal performance in larger systems, RAM should be sized to at least 60% of the PI AF database. Since SQL Server Express has a 1GB RAM limitation, when PI AF is expected to support many concurrent users with different needs and the PI AF database is

anticipated to be greater than several GB, it is recommended that a higher edition of SQL Server be used for optimal performance. Additional RAM improves read and write performance to the SQL Server.

Database Size Requirement

Each PI AF object consumes some amount of database storage. The type and quantity of objects stored as PI AF element attributes consumes different amounts of database storage. As there are infinite permutations of PI AF object types, counts and content, it is impossible to provide an absolute

quantification of database size requirements. However, we can provide some representative examples. Table 5 provides database sizes of some representative PI AF systems. The default PI AF SQL database is named “PIFD” and its size can be found using either SQL Server Management Studio, which is provided with all SQL Server editions including SQL Server 2012 Express, or by traversing to the folder location of the PIFD database using Windows Explorer.

(11)

PI AF Dependency on Microsoft SQL Server

PI AF Element Count PI Notifications Count PI Event Frames Count SQL Server DB Size (MB)

0 0 0 39 1,000 1,000 10,000 166 2,000 2,000 20,000 286 3,000 3,000 30,000 404 4,000 4,000 40,000 521 5,000 5,000 50,000 638 6,000 5,000 60,000 756 10,000 5,000 100,000 1,100 20,000 5,000 200,000 2,228 30,000 5,000 300,000 3,300 40,000 5,000 400,000 4,323 50,000 5,000 500,000 5,379 100,000 5,000 1,000,000 10,640

Table 5. PI AF object counts and their impacts on SQL Server database size

For Table 5, each PI AF object has 25 attributes. 50% of the attributes are double precision real numbers with the remaining attributes containing 20 character text strings. Database size is approximate.

A typical PI AF system with >50,000 elements and PI Notifications and PI Event Frames will start approaching SQL Server Express’ 10GB database size limit with room for future expansion. Other PI AF features such as element versioning and audit trail will increase the PI AF database size. Users can get information on their PI AF object counts using PI System Explorer (System Properties -> Count).

SQL Server Features Used by PI AF

PI AF uses all the SQL Server features as listed in Tables 3 and 4. It is important to select the proper SQL Server edition to serve your needs today while taking into account for future expansion. For example, one key feature, PI AF High Availability (HA) is not supported by SQL Server Express in the PI AF HA primary.

(12)

8

Business Intelligence (BI)

Business Intelligence is a combination of database and visualization technologies that enables users to explore large quantities of data at an aggregate level. Common objectives include identifying an area of focus from a top down approach, comparison of measurements or key performance indicators by multiple axes, report generation, predictive or what-if analyses of complex systems, and dependent variable analysis. In short, BI supports better business decision making. Often times the PI System contains the time series data that is necessary to perform these analyses in conjunction with data from business systems. Microsoft recognizes these BI needs and provides key features and functions within SQL Servers to enable BI analyses and reports. Tables 8 and 9 describe BI dependencies on the various main SQL Server editions.

SQL Server 2008 R2 Features SQL Server

Express

SQL Server Standard

SQL Server Enterprise

SQL Server Analysis Services backup  

Dimension, attribute relationship, aggregate, and cube design

 

Translations  

Personalization extensions  

SQL Server PowerPivot for SharePoint 

Financial aggregations 

Partitioned cubes and distributed artitioned cubes 

Custom rollups 

Semi-additive measures 

Writeback dimensions 

Linked measures and dimensions 

Binary and compressed XML transport 

Account intelligence 

Perspectives 

Scalable shared databases 

Report Builder 3.0 for Ad Hoc Reporting  

Excel 2007 and Visio 2007 Add-in support  

(13)

Business Intelligence (BI)

SQL Server 2012 Features SQL Server

Standard SQL Server Business Intelligence SQL Server Enterprise Basic OLTP   

Programmability (T-SQL, Data Types, File Table)   

Manageability (SQL Server Management Studio, Policy-based Management)

  

Basic High Availability5 _ _ _

Basic Corporate BI (Reporting, Analytics, Multidimensional Semantic Model, Data Mining)

  

Basic Data Integration (Built-in Data Connectors, Designer Transforms)

  

Self-Service Business Intelligence (Alerting, Power View, PowerPivot for SharePoint Server)6

 

Advanced Corporate BI (Tabular BI Semantic Model, Advanced Analytics and Reporting, VertiPaq™ In-Memory Engine)

 

Advanced Data Integration (Fuzzy Grouping and Lookup, Change Data Capture, Advanced Data Mining)

 

Enterprise Data Management (Data Quality Services, Master Data Services)

 

Advanced Security (SQL Server Audit, Transparent Data Encryption)



Data Warehousing (ColumnStore Index, Compression, Partitioning)



Advanced High Availability (Multiple, Active Secondaries; Multi-site, Geo-Clustering)5



Table 7. Business Intelligence (BI) Dependency on Microsoft SQL Server 2012

5_{Basic includes log shipping, database mirroring, server core support and two-node Failover Clustering. Windows Server} Enterprise edition or above is a system requirement for AlwaysOn / Failover Clustering.

6_{SharePoint Server with Enterprise CAL is a system requirement for Power View and PowerPivot for SharePoint. Alerting is} accessible through SharePoint Foundation or above.

(14)

10

Microsoft StreamInsight

Microsoft StreamInsight version 1.1 or higher is required for use with PI for StreamInsight7_{. Microsoft}

StreamInsight version 2.1 or higher is required for use with the code examples available on the OSIsoft Virtual Campus site.

Microsoft StreamInsight was introduced with SQL Server 2008 R2. Microsoft StreamInsight requires a Microsoft SQL Server 2008 R2 or SQL Server 2012 license. However, a Microsoft SQL Server installation is not required. Microsoft StreamInsight is designed to handle a wide variety of event-driven scenarios and is used for Complex Event Processing. Microsoft StreamInsight is available in two editions: Premium and Standard. The differences between the editions are in two areas: Rate and Latency. Table 6 and 7 summarize the Microsoft StreamInsight dependency on the main SQL Server 2008 R2 and SQL Server 2012 editions respectively. In order to utilize StreamInsight, a minimum of SQL Server Standard is required for StreamInsight Standard while SQL Server DataCenter (2008 R2) or SQL Server Enterprise (2012) edition is required for StreamInsight Premium.

SQL Server Express SQL Server Standard SQL Server Enterprise SQL Server Datacenter StreamInsight Rate N/A < 5000 events per

second

< 5000 events per second

> 5000 events per second

StreamInsight Latency N/A > 5 seconds > 5 seconds < 5 seconds

Table 8. Microsoft StreamInsight dependency on SQL Server 2008 R2 editions

SQL Server Express SQL Server Standard SQL Server Business Intelligence SQL Server Enterprise StreamInsight Rate N/A < 5000 events per

second < 5000 events per second > 5000 events per second StreamInsight Latency

N/A > 5 seconds > 5 seconds < 5 seconds

Table 9. Microsoft StreamInsight dependency on SQL Server 2012 editions

(15)

Other Notable SQL Server 2008 R2 Features

There are a few features not available on a basic SQL Server Express installation that would enable users to better manage larger and more complex PI Systems and derive additional values from their PI System data. These include8_:

 SQL Server management tools

 SQL Server Management Studio is an integrated environment for accessing, configuring,

managing, administering, and developing all components of SQL Server. These are included with SQL Server Standard version or higher, but can be installed with SQL Server Express.

 SQL Server Analysis Services

 SQL Server Analysis Services provides a group of online analytical processing (OLAP) and data mining capabilities allowing users to perform business intelligence (BI) functions with their data. SQL Server Standard Edition is required with full functionality and Data Warehousing capabilities in Enterprise edition or higher.

 Managed Self Service Business Intelligence

 Enabling PowerPivot sharing and collaboration through SharePoint requires SQL Server 2008 R2 Enterprise Edition or higher.

 SQL Server Reporting Services

 SQL Server Reporting Services provides a full range of ready-to-use tools and services to help users create, deploy, and manage reports for their organization, as well as programming features that enable users to extend and customize their reporting functionality. SQL Server Standard or higher is required for the full suite whereas a subset of features is available with SQL Server Express.

8_{Some of these features are available with SQL Server Express with Advanced Services or they are installed with a higher SQL} Server editions and applied towards SQL Server Express.

(16)

12

Other Notable SQL Server 2012 Features

 Managed Self Service Business Intelligence

 Power View is an interactive data exploration, visualization, and presentation experience. It provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers. They can easily create and interact with views of data from tabular models based on PowerPivot workbooks published in a PowerPivot Gallery, or tabular models deployed to SQL Server 2012 Analysis Services (SSAS) Release Candidate 0 (RC 0) instances. Power View is a browser-based Silverlight application launched from SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.

(17)

Microsoft SQL Server Express Limitations

Although one can use any one of the many SQL Server editions for the PI System, it is worthwhile to note that SQL Server Express has some limitations. They are:

 1 CPU limit – this limit can cause sub-optimal user experience in a multiuser environment or if PI AF is installed in a shared fashion with other applications.

 1 GB RAM limit – for optimal performance in larger systems, in house test has shown that RAM should be sized to at least 60% of the PI AF database size.

 10 GB database size limit – A typical PI AF system with >50,000 elements with PI Notifications and PI Event Frames will approach SQL Server Express’ 10GB database size limit. Other PI AF features such as the element versioning and audit trail could dramatically increase the PI AF database size.

 Lacks SQL Server features that would enable users to better manage larger and more complex PI Systems and to derive additional values from their PI System data.

 Does not support Microsoft StreamInsight

 Does not support PI AF High Availability

 Does not support BI functionalities

We recommend that the highest edition of SQL Server be used that fits the user’s current and future needs.

PI System and Microsoft SQL Server Requirements. March, 2013