MSSQL to HeliosDB Migration Guide¶

Version: 1.0 Last Updated: 2025-11-30

Pre-Migration Checklist¶

[ ] Backup SQL Server database
[ ] Review T-SQL compatibility
[ ] Estimate data volume
[ ] Plan migration window
[ ] Test migration script

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¶

[ ] Row count matches
[ ] Indexes created
[ ] Triggers migrated
[ ] Stored procedures tested
[ ] Query performance validated
[ ] Application tested end-to-end

Related Documentation: - MySQL Migration Guide - SQLite Migration Guide