---
title: "MSSQL to HeliosDB Migration Guide"
---

# MSSQL to HeliosDB Migration Guide

:::caution[What the listeners serve today]
HeliosDB Full opens listeners for PostgreSQL, MySQL, Oracle, MongoDB,
Cassandra, Redis and SQL Server plus an HTTP gateway; MongoDB, Cassandra and SQL
Server clients can connect and authenticate, and reading and writing HeliosDB tables
through them is planned. Until then, load and query migrated data over the
PostgreSQL listener.
:::

**Version:** 1.0

---

## 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

```powershell
# 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

```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

```powershell
# 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

> **`ANALYZE` in the current release:** `ANALYZE` (including `VACUUM ANALYZE`) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. `EXPLAIN ANALYZE`, which runs the query and reports actual rows and timing, works today.

```sql
-- 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:**