Portal Community
Data Ocean

SQL Server in Data Ocean

SQL Server is the primary relational backend for Data Ocean — the database where your application data, business entities, and AI-enriched records live. This guide covers how to connect it, design for AI, and query it from workflows.

What Is the Data Ocean SQL Server?

When we refer to Data Ocean SQL Server, we mean a dedicated SQL Server database instance provisioned specifically for your application data — separate from the BizFirstGO system database that the platform uses internally. This separation ensures that your business data is isolated, independently scalable, and fully under your control.

Your Data Ocean SQL Server stores the tables, views, and stored procedures that your BizFirstGO workflows read and write. Flow Studio connects to it via the Datasources capability using SqlQueryNode and SqlUpdateNode, which execute parameterized SQL against any registered datasource.

One Database Per Application Domain

Best practice: register a separate datasource (with its own database) for each logical application domain — for example, one database for customer data, another for inventory, another for financial records. This keeps schemas clean and access control precise.

When to Use SQL Server vs. the Entity Service

Use SQL Server (Data Ocean)Use the Entity Service
You need custom schema design with complex relationshipsYou need a generic key-value or document store without schema work
You need SQL-level reporting, JOINs, aggregationsYou need simple CRUD on loosely structured entities
You want AI-enrichment columns (embedding vectors, classifications)You need rapid prototyping without database design
You need integration with existing SQL tooling (SSMS, Power BI)You want zero database administration overhead
Your data volume and query patterns require indexed relational queriesVolume is low and query patterns are simple lookups

Feature Overview

Datasource Registration

Register your SQL Server instance with a datasourceId. Credentials are stored securely via the credential resolver — no raw connection strings in code.

AI-Ready Schema

Follow the Data Ocean schema convention — standard metadata columns, AI enrichment columns, and vector-ready design patterns baked in from the start.

Flow Studio SQL Nodes

Use SqlQueryNode and SqlUpdateNode in workflow definitions to read and write data without any custom code.

AI Form Generation

Describe your SQL table to the AI — it generates a complete Atlas Form with field bindings, validation rules, and the corresponding SQL migration script.

Tenant Isolation

The datasource layer enforces TenantId scoping on all queries. Cross-tenant data access is blocked at the SQL node execution level.

Performance Patterns

Indexes tuned for workflow query patterns, connection pool configuration, and pagination strategies designed for Flow Studio data retrieval.

Architecture Position

The SQL Server datasource sits in the Data Ocean tier, between your workflows (Flow Studio) and the raw data storage layer:

Flow Studio Workflow
    └── SqlQueryNode / SqlUpdateNode
            └── Datasources Capability
                    └── IDatasourceConnectionFactory
                            └── ICredentialResolver → Connection String
                                    └── SQL Server (Data Ocean DB)
                                            └── Your Application Tables

Prerequisites

Next: SQL Server Setup →