migration & data transfer

6 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

MSSQL to Supabase migration overview

Supabase provides a process to migrate Microsoft SQL Server databases to Supabase's Postgres database. This enables access to Postgres capabilities and Supabase features including a Postgres database, authentication, instant APIs, edge functions, real-time subscriptions, and storage.

Required MSSQL database information for migration

Before migrating from MSSQL to Supabase, collect the following database details from your MSSQL provider: hostname or IP address, database name, username, and password.

How to retrieve Supabase host for migration

To retrieve your Supabase host: (1) Create a project in Supabase and note the password, which can be reset in database settings if forgotten. (2) On the project dashboard, click Connect. (3) Under the Session pooler section, click View parameters under the connect string to find your Host ($SUPABASE_HOST).

MSSQL migration tools available

Three tools are available for migrating MSSQL to Supabase: (1) Supabase migration tool on Google Colab (fastest method). (2) pgloader - a flexible data migration tool supporting MSSQL and other database engines, migrating to Postgres. (3) pg_dump and psql command line tools - recommended for Postgres-engine source databases, included in full Postgres installation.

Google Colab MSSQL migration steps

To migrate MSSQL using Google Colab: (1) Select the Database Engine from the Source database dropdown. (2) Set environment variables (HOST, USER, SOURCE_DB, PASSWORD, SUPABASE_URL, and SUPABASE_PASSWORD) in the Colab notebook. (3) Run the first two steps in order - the first sets the engine and installs necessary files. (4) Run the third step to start the migration, which takes a few minutes.

pgloader MSSQL migration configuration

To migrate using pgloader: (1) Install pgloader. (2) Create a configuration file (e.g., config.load). (3) For the destination, use your Supabase connection string with connection pooling enabled and mode set to Session, available from Database Settings. (4) Run 'pgloader config.load' to execute the migration. The configuration file should include: LOAD DATABASE FROM mssql://USER:PASSWORD@HOST/SOURCE_DB INTO postgres://postgres.xxxx:password@xxxx.pooler.supabase.com:5432/postgres; ALTER SCHEMA 'public' OWNER TO 'postgres'; set wal_buffers = '64MB', max_wal_senders = 0, statement_timeout = 0, work_mem to '2GB';

Give your agent this brain