# How to add a private AI layer to a custom MES on SQL Server > Source: https://netray.co/erp-help/private-ai-layer-custom-mes-sql-server-on-prem-architecture > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## 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. ## Kind advanced ## System mfg-systems ## Erp Custom MES on SQL Server ## Module In-house MES database ## Query how to add AI to a custom MES on SQL Server without cloud ## Alternate Queries - 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 ## Applies To SQL Server 2016 and newer (Always On readable secondary, Change Data Capture, row-level security); Enterprise features noted where they apply. ## Steps ### Rollout for an in-house MES on SQL Server **Items:** Document 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. Create 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. Build 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. Create 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. Use 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. Write 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. Serve 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. Pilot with planners and supervisors on one line, review wrong answers weekly, and widen views only after a review. ## Sections ### Data access paths you control **Body:** 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. **Code:** -- 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 **Body:** 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 **Body:** 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 **Body:** 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. ## 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. ## Ai Angle 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. ## Faq ### Why a reporting copy instead of querying production? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms custom MES AI, SQL Server MES LLM, AI for in-house MES, chat with MES database, SQL Server row level security AI, readable secondary reporting AI, on-prem AI manufacturing execution, private RAG MES --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json