IMD SolutionComputing LibraryShop main store

Explainer

Does the PI Data Archive store tag data in SQL Server

Edge computing and IIoT Published 9 min read

Short answer

No. The PI Data Archive writes time-series tag values to its own proprietary storage and never uses SQL Server for tag data; SQL Server is in the PI Server stack only for PI AF, whose asset model lives in a SQL database treated as a black box. A SQL Server backup therefore protects the asset model and none of the tag history.

On this page
  1. What the PI Data Archive stores tag data in
  2. Where SQL Server sits in a PI System
  3. Which store answers a trend, an attribute or an asset name
  4. Why the AF SQL database is a black box
  5. What a SQL backup protects on a one-server PI System
  6. Does a SQL Server outage stop tag collection
  7. When you actually need tag values in SQL Server
  8. The decisions, case by case
  9. Questions asked

No. The PI Data Archive does not keep tag data in SQL Server. It writes every time-series value to its own proprietary store, built for high-rate timestamped writes and range reads, and nothing in that store is a relational table. Microsoft SQL Server is in the PI Server stack for a different component: PI AF, the Asset Framework, keeps its asset model — elements, element templates, attribute definitions — in a SQL database. The two stores usually share one server, but they hold different data, fail independently and back up independently. SQL Server has been a requirement for the PI Server since the 2010 release, when the Data Archive reached version 3.4.385, which is why every PI Server you inherit carries a SQL instance even though the archive never touches it. This page separates the two stores and ends with the decision for each case.

What the PI Data Archive stores tag data in #

The Data Archive writes time-series events to its own proprietary storage. There is no SQL instance to install for it, no SQL client connects to it, and its capacity planning follows the archive's own configuration — number and size of archive files, point count, update rate and retention — through the vendor's Data Archive documentation, not through database file-growth settings.

That split changes troubleshooting. A slow trend is not a missing index: tuning SQL Server indexes or shrinking a transaction log addresses a layer the Data Archive never used. Look at the Data Archive's own message logs, its archive status and interface buffering instead.

PI is not alone in this design, and the choice has real consequences for reporting and recovery. The trade-offs between keeping values in SQL Server and keeping them in a proprietary historian store are set out side by side in SQL Server versus a proprietary historian database.

Where SQL Server sits in a PI System #

The component that uses SQL Server is PI AF. The AF server application reads and writes a SQL database that holds the asset model: elements, element templates, attribute definitions and the related configuration. Clients — displays, build tools and code — talk to the AF server application, and the AF server application talks to SQL Server. No client ever opens that database itself.

The PI Server install kit creates the AF SQL database as one of its selectable features, and the vendor's deployment guidance recommends running PI AF servers and Microsoft SQL Servers on separate, redundant computers for the best performance and availability. On a small system both roles often share one host; when they do, a SQL Server failure takes the asset model offline while the Data Archive keeps collecting and serving values on its own storage. PI Vision's administration portal is where the SQL databases and archive locations behind a system are configured and verified.

Which store answers a trend, an attribute or an asset name #

Pick any value on a display and it has a two-store path behind it. A trend of raw tag values over the last shift is answered by the Data Archive. The asset tree, element names and template attribute lists are answered by AF out of its SQL database. An attribute that displays a value does both: AF resolves the attribute's data reference to a PI point, and the Data Archive supplies the values.

What you are looking at Store that answers it First thing to check when it fails
Trend of a raw tag Data Archive Archive status, interface status, point configuration
Asset tree, element and attribute names AF, in SQL Server AF service state and the SQL instance behind it
An attribute showing a value AF mapping, then the Data Archive The attribute's data reference and its PI point

The signatures are clean. A blank trend under a healthy asset tree points at the Data Archive side; a missing asset tree while direct point trends still plot points at AF or its SQL instance. For the collection-side faults behind an empty trend, Historian trend empty: PLC tag collection troubleshooting walks the interface and point checks.

Why the AF SQL database is a black box #

The vendor's guidance is to treat the AF SQL database as a black box: nothing reads it or writes it directly. The supported interfaces are the AF client tools and the AF programming interfaces, and every change through them passes the AF server's validation. A direct read, write or schema edit bypasses that validation and can corrupt the model in ways that surface later as missing elements or failed upgrades.

The routes that work:

  • Report on assets and attributes: AF client tools or the AF programming interfaces, not SELECT statements against AF tables.
  • Bulk-edit elements: AF import and export tools or the programming interface, not UPDATE statements in SQL.
  • Read historical values: the Data Archive through PI client tools or the data access interfaces, not SQL anywhere in the PI System.
  • Patch and back up the instance: standard DBA practice within the vendor's guidance, with no schema or data edits.

One audit makes the exposure visible: search your reports and scripts for a connection string that points at the AF instance. Every hit is a direct-access dependency to replace.

The urge to open the database is strongest during recovery, and on historians whose archives do live in SQL Server, recovering deleted history from a SQL-based historian archive is a real workflow with its own pitfalls. The AF database is not that kind of store: its recovery runs through the vendor's tools, never through the tables.

What a SQL backup protects on a one-server PI System #

Take the common small-plant layout as the case: one Windows host runs the Data Archive and PI AF together, one SQL Server instance on that host holds the AF database, and a nightly SQL Server backup job covers the AF database. The layout is illustrative; the shares below are not, because they follow from the documented storage split. The inputs:

  • 100% of time-series events are written to the Data Archive's proprietary store, per the vendor's documentation.
  • The AF database holds the asset model and metadata and none of the stored tag events: its share of tag history is 0%.
  • The nightly job backs up SQL Server databases only, so its reach is the AF database.

The rule in words: a SQL Server backup can only protect data that lives in SQL Server, so the share of tag history it protects equals the share of tag history stored there.

The arithmetic, in units of stored tag events: events held in SQL Server = 0 events; all stored events = the total; share protected by the SQL job = 0 events ÷ all events = 0%. The same arithmetic for the asset model: model records in the AF database = all model records, so the job protects 100% of the model.

The result: after a loss restored from that SQL backup alone, every element, template and attribute definition comes back, and not one tag value does.

The decision: if the only scheduled backup touching the PI Server is a SQL Server job, protected tag history sits at 0% and stays there, so the Data Archive backup is not optional. Schedule it with the Data Archive's own documented backup facility and store that copy off the server; keep the SQL job for the AF database on its own schedule; record both run times so the two restore points can be matched after a recovery; and test-restore both to a non-production host. The two restores do not substitute for each other: a restored Data Archive does not bring back the asset model, and a restored AF database does not bring back tag values.

Does a SQL Server outage stop tag collection #

No. The Data Archive runs with no dependency on a SQL Server instance, so a SQL outage should leave tag collection and direct point trends running while the AF asset tree and every attribute that resolves through it fail. Displays built on AF attributes degrade to errors; raw point trends keep plotting.

One safety note: verify this on a test system in a maintenance window, because stopping SQL Server on a production PI Server takes down every AF-dependent display at once.

The test itself is short. Stop the SQL Server instance behind AF and confirm the asset tree disappears while interfaces keep writing and direct point trends keep plotting. Restart SQL Server and confirm the tree and attribute mappings return with archived values unchanged. If direct point trends die with SQL Server, the values were not coming from the Data Archive, and the first job is to find what store they actually came from.

When SQL Server does drop and come back, the applications behind it have reconnect behavior of their own to get right; Reconnect an edge PC to SQL Server after connection loss covers that recovery end to end.

When you actually need tag values in SQL Server #

Some reports must join tag values with maintenance, lab or batch data in the same query. The supported way to get there is not to read the AF database; it is to copy values out through supported interfaces into your own database, where you own the schema and the queries. A logging interface that writes PLC or OPC values into SQL Server on a schedule you set does exactly that: Logging PLC values from an OPC server into SQL Server builds one step by step.

If you are choosing the store itself for time-series on a plant-floor PC, database options for time-series data on an industrial Linux PC set the candidates side by side. And before building any of it, confirm the architecture needs a historian at all rather than a tag server: Do you need a historian or just an OPC tag server? works that decision through.

The decisions, case by case #

  • Tag values, storing or reading: the Data Archive, through PI client tools or the data access interfaces. There is no SQL path to them.
  • The asset model, reading or editing: AF client tools or the AF programming interfaces. The SQL database behind AF stays sealed.
  • Backups: two independent jobs. The Data Archive's documented backup facility protects 100% of tag history and none of the model; the SQL Server job protects 100% of the model and none of the history.
  • A SQL Server outage: expect collection and raw point trends to continue and AF displays to fail. Prove it on a test system, not on the production server.
  • Reports that need values in SQL: copy the values into your own tables with a logging interface.

Questions asked

Does PI AF store its data in the Data Archive?

No. PI AF stores everything it owns in its SQL Server database. When an attribute shows a value, AF is only resolving that attribute's data reference to a PI point; the value itself is read from the Data Archive at display time. AF never writes tag values into the Data Archive and never keeps copies of them in its own database.

What stops when the Data Archive itself goes down?

Collection and every value read stop, because the values live only there. The AF side keeps working: the asset tree still renders, because it comes from SQL Server. Attributes that display values show errors instead of numbers, since the points they resolve to cannot be read. No SQL Server work fixes this; the Data Archive service and its archive files are what to check.

What SQL Server privileges do AF database installs and updates need?

Installing the AF SQL Server database on a new system requires an account with sysadmin privileges on the instance. Updates to an existing AF database require db_owner on that database. Routine patching and backup of the instance afterwards is standard DBA work, carried out within the vendor's guidance and without touching schema or data by hand.

Can the Data Archive be placed in a DMZ away from SQL Server?

Yes, and it is a documented pattern: only the Data Archive sits in the DMZ, while SQL Server and the AF server stay on the LAN. It works precisely because clients read tag values from the Data Archive without touching SQL Server. The AF server on the LAN keeps its own SQL instance close, and the two stores keep their independent failure domains.

Is there any SQL query that returns PI tag values for a report?

No. The Data Archive exposes no relational schema, so there is nothing for a report to join against, and the AF database is off limits. If a report needs values next to relational data, a logging interface copies the values into your own SQL Server tables on a schedule you control, and the report reads your tables.

Keep reading

  • How-to

    Logging PLC values from an OPC server into SQL Server

    SQL Server has no OPC client, so it cannot read OFS items by itself. Put an OPC client component between the server and the database: a packaged gateway or a small application that reads tags and writes rows. Prove the PLC read through an OPC test client first, then prove one database write under the runtime account before logging production values.

    Edge computing and IIoT

  • Comparison

    Choosing edge storage for high-rate vibration data

    Keep the CSV-in-ZIP vibration archive that already works at 10 kHz, and treat Parquet as a candidate format, not a capacity fix. Move only after the Parquet writer proves exact sample fidelity, sustained throughput at the real acquisition rate, and recoverable completed archives after power loss. A format change is an engineering change with its own acceptance test, and compression in either format never repairs precision lost upstream.

    Edge computing and IIoT

  • Explainer

    Do you need a historian or just an OPC tag server?

    No. An OPC tag server (OPC DA) supplies the current value, quality and timestamp of each item and stores nothing, so monthly figures need a historian or a SQL logger collecting from that same server. Keep a reporting client like ActiveFactory only if you also run the historian it expects.

    Edge computing and IIoT