MSSQL to HeliosDB Migration Guide

Version: 1.0 Last Updated: 2025-11-30

Pre-Migration Checklist

Step 1: Export MSSQL Schema

# Export schema using SQL Server Management Studio
Right-click Database → Tasks → Generate Scripts

# Or use sqlcmd
sqlcmd -S server_name -U username -P password -Q "sp_helptext table_name" > schema.sql

Step 2: Convert T-SQL to SQL

-- Common conversions:

-- MSSQL: IDENTITY → PostgreSQL: SERIAL or GENERATED ALWAYS AS IDENTITY
-- MSSQL: [column_name] → PostgreSQL: "column_name"
-- MSSQL: NVARCHAR → PostgreSQL: VARCHAR or TEXT
-- MSSQL: INT IDENTITY(1,1) → PostgreSQL: SERIAL
-- MSSQL: GETDATE() → PostgreSQL: CURRENT_TIMESTAMP
-- MSSQL: ISNULL(col, val) → PostgreSQL: COALESCE(col, val)
-- MSSQL: CAST(x AS INT) → PostgreSQL: CAST(x AS INTEGER)
-- MSSQL: TOP N → PostgreSQL: LIMIT N

Step 3: Export Data

# Export to CSV using BCP
bcp "SELECT * FROM TableName" queryout "table.csv" -S server_name -T -c -t,

# Or use SQL Server Integration Services (SSIS)

Step 4: Import into HeliosDB

-- Create table in HeliosDB first
-- Then import data
COPY table_name FROM '/path/to/table.csv' WITH (FORMAT csv);

-- Verify
SELECT COUNT(*) FROM table_name;

Step 5: Migrate Stored Procedures

-- MSSQL procedures with differences:

-- MSSQL CREATE PROCEDURE
CREATE PROCEDURE usp_GetCustomers @country NVARCHAR(50) AS
  SELECT * FROM Customers WHERE Country = @country

-- PostgreSQL equivalent
CREATE FUNCTION get_customers(country VARCHAR) RETURNS TABLE(...) AS $$
  SELECT * FROM customers WHERE country_name = $1;
$$ LANGUAGE SQL;

Step 6: Migrate Triggers

-- MSSQL CREATE TRIGGER
CREATE TRIGGER trg_UpdateCustomer
  ON Customers
  AFTER UPDATE
  AS BEGIN
    UPDATE OrderCount SET count = count + 1
  END

-- PostgreSQL equivalent
CREATE TRIGGER trg_update_customer
  AFTER UPDATE ON customers
  FOR EACH ROW
  EXECUTE FUNCTION update_customer_trigger();

CREATE FUNCTION update_customer_trigger() RETURNS TRIGGER AS $$
BEGIN
  UPDATE order_count SET count = count + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Common Compatibility Issues

MSSQL	HeliosDB Solution
`XML` type	Use `XML` or `JSONB`
`UNIQUEIDENTIFIER`	Use `UUID` type
`DATETIME2`	Use `TIMESTAMP`
`DECIMAL(18,2)`	Use `NUMERIC(18,2)`
`BIT` (0/1)	Use `BOOLEAN`
`CTE (WITH)`	Fully supported ✓

Step 7: Testing

-- Compare row counts
SELECT COUNT(*) FROM table_name;

-- Validate sample data
SELECT TOP 100 * FROM table_name;

-- Test queries
SELECT * FROM table_name WHERE condition;

Migration Strategy

Option 1: Cut-over (downtime required)

1. Stop applications
2. Export from MSSQL
3. Import to HeliosDB
4. Test
5. Switch applications

Option 2: Parallel run (no downtime)

1. Dual-write to both databases
2. Sync data continuously
3. Validate on HeliosDB
4. Switch when ready

Performance Tuning

-- After migration
ANALYZE;
VACUUM FULL;

-- Create indexes
CREATE INDEX idx_name ON table_name(column_name);

-- Enable caching for hot tables
ALTER TABLE frequently_accessed ENABLE INTELLIGENT_CACHING;

Post-Migration Verification

Related Documentation: