AdvancedCustom MES on SQL ServerIn-house MES database

How to add a private AI layer to a custom MES on SQL Server

Question
how to add AI to a custom MES on SQL Server without cloud

Also searched as

  • AI assistant for in-house MES database
  • SQL Server MES LLM on-prem
  • chat with MES SQL Server data locally
  • readable secondary SQL Server for AI queries

Short answer

A custom MES has the advantage that you own the schema. Put a readable secondary or reporting copy beside it, expose approved views through a tool server, mirror your application roles with row-level security, and run an open-weight model on local GPUs. Write-back goes only through the MES application's own API with human approval.

Applies to: SQL Server 2016 and newer (Always On readable secondary, Change Data Capture, row-level security); Enterprise features noted where they apply.

Rollout for an in-house MES on SQL Server

  1. 1Document the schema that matters: work orders, operations, lots and serials, machine states, downtime, scrap, quality results. List who owns each table and which are sensitive.
  2. 2Create a reporting copy: an Always On readable secondary where licensing allows, otherwise log shipping, replication or a scheduled restore. AI queries must never run against the primary.
  3. 3Build a schema of read-only views for the assistant (for example ai.v_work_order_status) that hide columns such as personal data, pricing or export-controlled attributes.
  4. 4Create a dedicated SQL login or database user for the tool server with SELECT on the ai schema only. Add row-level security so the same filters used by the MES apply.
  5. 5Use Change Data Capture or Change Tracking on the tables whose text fields feed the retrieval index, so notes and comments are re-indexed when they change.
  6. 6Write the tool server: fixed, parameterised queries for the top 20 questions, plus a guarded generic query tool that only accepts SELECT against the ai views and enforces row limits and timeouts.
  7. 7Serve an open-weight model with vLLM or Ollama locally and give it only the tool server. Log the user, question, tool calls, SQL text and row counts to an append-only table.
  8. 8Pilot with planners and supervisors on one line, review wrong answers weekly, and widen views only after a review.

Data access paths you control

In a custom MES the integration layer is whatever your team built: SQL Server tables and stored procedures, a service layer such as REST or WCF endpoints, and message queues. For an AI layer, read through views on a reporting copy, and use the application's own service layer for anything that writes. Do not let the model call stored procedures that modify data.

SQL Server features help. A readable secondary in an availability group gives an up-to-date copy that is safe to query. Row-level security policies enforce per-user filters in the database itself. Dynamic data masking can hide sensitive columns. Change Data Capture records inserts, updates and deletes so the retrieval index stays current.

If the schema is old, the first value of the project is documentation. Table and column comments, a data dictionary and a few reviewed example queries make the assistant noticeably better and help your own developers.

-- Read-only view exposed to the assistant (table and column names illustrative)
CREATE SCHEMA ai;
GO
CREATE VIEW ai.v_work_order_status AS
SELECT wo.WorkOrderNo, wo.PartNo, wo.QtyPlanned, wo.QtyGood, wo.QtyScrap,
       wo.Status, wo.LineId, wo.PlannedStart
FROM dbo.WorkOrder wo;
GO
GRANT SELECT ON SCHEMA::ai TO ai_tool_reader;
-- Row-level security so a user only sees lines they are assigned to
CREATE FUNCTION sec.fn_line_filter(@LineId int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS ok
WHERE EXISTS (SELECT 1 FROM dbo.UserLine ul
              WHERE ul.UserName = SESSION_CONTEXT(N'app_user')
                AND ul.LineId = @LineId);

Reference architecture

The components are: the primary MES database, the reporting copy, the ai schema of views, a retrieval index over free text and documents (work instructions, quality notes, downtime comments), a tool server, the local model and an audit log. The tool server sets the SQL session context to the end user's identity before each query, so row-level security applies per person, not per service account.

Retrieval complements SQL. Use SQL for counts, status and trends, and use the index for questions about why: operator comments, nonconformance text, engineering change notes. Combine them in the answer, and show both the query and the cited passages.

For air-gapped sites, nothing in this design needs outbound connectivity. Model weights, container images and package mirrors are brought in through the site's controlled process. SQL Server 2025 adds a native vector type; on earlier versions, keep embeddings in a separate local vector store.

Permissions, write-back and limits

Mirror the MES roles: supervisor, planner, quality, maintenance, operator. Store the mapping in one place the tool server and the application both read. Apply the same filters by line, plant and customer contract, and treat export-controlled or customer-restricted part numbers as their own scope.

Write-back should go through the MES's own service layer, which already validates business rules. Candidate actions are adding a downtime reason, creating a quality hold or posting a comment. The assistant proposes the action with its parameters; a named person approves; the service layer executes; the audit table records all three.

Limits: poorly named columns, overloaded status codes and business logic buried in stored procedures reduce accuracy. The model cannot infer rules that exist only in application code. If you only need a few fixed reports, build the reports; the AI layer is worth it when questions are open-ended.

Sizing and phased rollout

The database side is modest: the reporting copy needs the storage of the primary plus indexes, and the tool server is lightweight. Model serving sizes with users and questions per day, typically one or two GPUs for a plant of a few hundred users using a 7B to 32B class model.

Phase 1: read-only status and history questions with visible SQL. Phase 2: drafted shift reports, scrap analyses and handover notes. Phase 3: approved actions through the service layer. Review a sample of answers against the database each week and keep a regression list of questions and expected results.

Try it here, free

Custom MES on SQL Server Private AI Layer Blueprint Generator

Open full tool

Fill in your Custom MES on SQL Server environment and get a shareable architecture plan for a private, read-only AI layer: components, controls, rollout and open questions.

Common pitfalls

  • !Pointing the assistant at the primary database and letting a long query block production transactions.
  • !Using one service login for all users so row-level security cannot reflect the person asking.
  • !Granting access to base tables instead of curated views, which exposes columns nobody reviewed.
  • !Letting the model execute any stored procedure. Only fixed read procedures belong on the allowlist.
  • !Indexing comments without redaction when they contain personal data or restricted part details.
  • !Having no regression question set, so schema changes silently break answers.

How an ERP-grounded AI assistant handles this

DataRay connects to SQL Server and file shares on-prem and shows the SQL behind each answer. For an in-house MES, Netray typically builds the ai views, the tool server with per-user context and the local model serving, and documents the schema as it goes so your developers keep what is built.

See ERPray

Frequently asked questions

Why a reporting copy instead of querying production?

AI questions can generate large or odd queries. A readable secondary or reporting copy isolates that load from the MES transactions and gives you a place to apply views, masking and row limits without touching the production design.

Can the model write its own SQL?

Only inside guardrails: SELECT against curated views, row limits, timeouts and logging. Frequent questions should use fixed parameterised queries. Free SQL against base tables is where leaks and slow queries come from.

How do we respect existing MES roles?

Map roles and line assignments into row-level security and set the end user's identity in the session before each query. Then the database enforces what the user may see, regardless of what the model asks for.

Does this need SQL Server Enterprise?

Not strictly. Availability group readable secondaries need Enterprise in many versions, but log shipping, replication or a scheduled restore can provide a reporting copy on other editions. Check your licensing.

How do changes get back into the MES?

Through the MES service layer with human approval. The assistant drafts the action, a named person approves, the application validates it under its normal rules, and the audit table records it.

Related

Advanced

ISA-95 and B2MML: How to Integrate ERP with MES

ISA-95 defines the boundary between business systems (Level 4, the ERP) and manufacturing operations (Level 3, the MES) and the information that crosses it. B2MML is the XML implementation of those models. In practice you exchange a ProductionSchedule from ERP to MES, and ProductionPerformance, plus material and equipment information, back, mapping each element to your ERP's work order and confirmation APIs.

How-to

How to Send Production and Labor Reporting from MES to ERP

Treat the ERP work order as the system of record for cost and quantity, and the MES as the system of record for what physically happened. Send one confirmation message per operation (or per shift or lot) containing work order, operation sequence, good quantity, scrap quantity with reason, and labor start/stop per employee and work center, post it through the ERP's supported transaction API, and make the message idempotent so a retry never double-posts.

How-to

How to Design Scrap and Downtime Reason Codes for MES and ERP

Keep one governed list of reason codes with a small hierarchy, owned in a single system and synchronised to the other. Separate scrap reasons from downtime reasons, mark each downtime code planned or unplanned and each scrap code as process, material, design or operator related, and map codes to ERP accounts or cost categories where accounting needs them. Fewer, clearer codes beat long lists nobody uses correctly.

How-to

How to Calculate OEE from MES Data and ERP Standards

OEE equals Availability times Performance times Quality. Availability comes from MES run and stop events against planned production time, Performance compares actual output to the ideal cycle time (usually the routing standard from the ERP), and Quality is good units divided by total units. The numbers are only trustworthy if planned time, ideal cycle time and the good/scrap split are defined once and used consistently.

Advanced

How to Connect OPC UA Machine Data to Your ERP

Do not connect OPC UA servers straight to the ERP. Read machine tags through an edge gateway or MES, convert signals into business events (cycle complete, stop, state change, count), attach the work order context, and post summarised confirmations to the ERP through its supported API. The ERP wants quantities, times and reasons per order, not millions of raw tag values.

How-to

How to Sync an NCR From Your QMS to ERP Quarantine and MRB Disposition

The reliable pattern is two events: when the NCR opens, the ERP moves or flags the affected lot or serial into a non-nettable quarantine status; when the Material Review Board closes it, the disposition (use as is, rework, repair, scrap, return to vendor) triggers exactly one matching ERP transaction. The NCR number is the key that ties both sides together.

AI for ERP

AI Agents for ERP, Running On-Prem

A practical guide to on-prem AI agents for ERP: what they can safely automate, where human approval belongs, and how to design the guardrails.

AI for ERP

An Air-Gapped Private LLM for SyteLine, Built for Defense Suppliers

Deploy a private LLM on an air-gapped network alongside Infor SyteLine for defense suppliers: no internet egress, ITAR and CMMC-aware architecture.

Stuck on Custom MES on SQL Server?

Talk to engineers who work inside Custom MES on SQL Server every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

Guides

AI on your ERP

Integrations

Comparisons

Software

TermsManufacturing Execution System (MES)Text-to-SQL