SQL Server Setup — SQL Server Guide

Choosing Your SQL Server Deployment

Data Ocean SQL Server can be deployed in three configurations depending on your infrastructure:

Option	Best For	Minimum Spec
SQL Server on-premises	Enterprise environments with existing SQL Server infrastructure	SQL Server 2019 Standard, 8GB RAM, SSD storage
Azure SQL Database	Cloud-native deployments with managed maintenance	General Purpose, 4 vCores, 20.4GB RAM
SQL Server in Docker	Development, staging, or containerized environments	SQL Server 2022 Developer or Express image

Step 1 — Install SQL Server

On-Premises (Windows Server)

Download SQL Server 2022

Download from Microsoft's official page. Choose Standard for production or Developer for local development (Developer is feature-identical to Enterprise but licensed for non-production use only).

Run the Installer

Select Database Engine Services as the only required feature. Full-Text Search is recommended if you plan to use SQL-level full-text search on top of semantic search.

Configure Authentication

Choose Mixed Mode (Windows + SQL Server authentication) and set a strong SA password. BizFirstGO will connect via a dedicated SQL login — not the SA account.

Docker (Development)

docker run -e "ACCEPT_EULA=Y" \
           -e "MSSQL_SA_PASSWORD=YourStrong!Password" \
           -p 1433:1433 \
           --name dataocean-sql \
           -d mcr.microsoft.com/mssql/server:2022-latest

Step 2 — Create the Data Ocean Database

-- Connect as SA or a sysadmin account and run:
CREATE DATABASE DataOcean_CustomerData
    COLLATE SQL_Latin1_General_CP1_CI_AS;
GO

-- Use READ_COMMITTED_SNAPSHOT isolation to avoid lock contention
-- from concurrent workflow reads and writes
ALTER DATABASE DataOcean_CustomerData
    SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER DATABASE DataOcean_CustomerData
    SET RECOVERY FULL;  -- Required for point-in-time restore
GO

One Database Per Application Domain

Name your database to reflect its domain — DataOcean_CustomerData, DataOcean_Inventory, DataOcean_Finance. Each domain is registered as a separate datasource with its own datasourceId, allowing workflows to target the correct database precisely.

Step 3 — Create the Application Login

-- Create a SQL Server login for the BizFirstGO application
USE master;
CREATE LOGIN BizFirstApp
    WITH PASSWORD = 'A_Strong_Password_Here!',
         DEFAULT_DATABASE = DataOcean_CustomerData,
         CHECK_EXPIRATION = OFF,
         CHECK_POLICY = ON;
GO

-- Create a database user for this login
USE DataOcean_CustomerData;
CREATE USER BizFirstApp FOR LOGIN BizFirstApp;
GO

-- Grant the minimum required permissions
-- db_datareader: SELECT on all tables
-- db_datawriter: INSERT, UPDATE, DELETE on all tables
-- db_ddladmin: CREATE TABLE (needed for EF Core migrations, optional for runtime)
ALTER ROLE db_datareader ADD MEMBER BizFirstApp;
ALTER ROLE db_datawriter ADD MEMBER BizFirstApp;
GO

Principle of Least Privilege

Do not grant the application login db_owner or sysadmin. Use db_datareader + db_datawriter for runtime. If you need to run schema migrations from the application, add db_ddladmin — but consider running migrations manually in production via a separate DBA-owned process.

Step 4 — Configure Network Access

The BizFirstGO backend must be able to reach TCP port 1433 on your SQL Server. Check:

SQL Server Configuration Manager: Enable TCP/IP protocol under SQL Server Network Configuration → Protocols for [Instance]
Windows Firewall: Inbound rule allowing TCP 1433 from the BizFirstGO server IP range
Azure SQL: Add the BizFirstGO server's outbound IP to the SQL Server firewall rules in the Azure portal
Private Endpoints: For production Azure deployments, use Private Endpoint + Private DNS Zone instead of public firewall rules

Step 5 — Verify Connectivity

-- From the BizFirstGO server, test connectivity using sqlcmd:
sqlcmd -S your-sql-server.database.windows.net -U BizFirstApp -P "YourPassword" \
       -d DataOcean_CustomerData -Q "SELECT @@VERSION"

-- Expected output: SQL Server version string
-- If this fails, check firewall rules and network path first

Connection String Format

Once the server is reachable, the connection string you will register in the BizFirstGO credential store follows this format:

-- Standard SQL Server
Server=your-sql-server;Database=DataOcean_CustomerData;User Id=BizFirstApp;Password=YourPassword;TrustServerCertificate=True;MultipleActiveResultSets=True;

-- Azure SQL Database
Server=your-server.database.windows.net;Database=DataOcean_CustomerData;User Id=BizFirstApp@your-server;Password=YourPassword;Encrypt=True;TrustServerCertificate=False;MultipleActiveResultSets=True;

Store Connection Strings in the Credential Store — Never in Code

Never put the connection string directly in appsettings.json, workflow definitions, or SQL node configuration. Always register it in the BizFirstGO credential store as a credential, then reference it by credentialId. See the Security and Credentials page for details.

← Overview Next: Datasource Registration →