Skip to content
Contact →

Connecting to Databases

Updated
25 Sept 2026
3 min read
6 sections
Page 23 of 29
Data Migration Masterclass

Section 4: Database Platforms for Migration. Lesson 23.

Connecting to a database means establishing a reliable line of communication between a client (an application, a migration tool or a script) and the database management software. In a migration it is one of the very first technical tasks: nothing can be analysed, extracted, transformed, validated or loaded until connections to both source and target work.

The connection as a bridge

Say you are migrating from Microsoft SQL Server to MySQL. You work with two databases at once, and the migration process holds a connection, or bridge, to each. Through those bridges the tool can:

  • Read from the source system
  • Execute queries
  • Create tables in the target
  • Insert and update records
  • Run validation checks on both sides
text
┌──────────────┐   connection A   ┌────────────────┐   connection B   ┌──────────────┐
│ Source DB    │ ◀──────────────── │ Migration tool │ ────────────────▶ │ Target DB    │
│ (SQL Server) │   read + verify   │ / script / ETL │   write + verify  │ (MySQL)      │
└──────────────┘                  └────────────────┘                  └──────────────┘

In the lecture's whiteboard example, a MySQL server and a PostgreSQL server are "connected" by taking the credentials of both. In practice the two databases rarely talk to each other directly. A migration tool or script sitting in the middle holds a connection to each, using each one's credentials. Direct links do exist (SQL Server linked servers, Oracle database links, PostgreSQL foreign data wrappers), but they are the exception.

Connection parameters

Default ports and drivers

Each platform uses different drivers and connection mechanisms, but the parameters are the same everywhere. SQL itself is broadly shared, although each engine has its own dialect (T-SQL, MySQL SQL, PL/SQL), as the previous lessons showed.

Connection strings

text
SQL Server (ODBC):
  Driver={ODBC Driver 18 for SQL Server};Server=db-prod-01,1433;Database=sales;
  Uid=migration_reader;Pwd=***;Encrypt=yes;

MySQL (URL):
  mysql://migration_writer:***@mysql-new.internal:3306/sales?ssl-mode=REQUIRED

PostgreSQL (URL):
  postgresql://migration_writer:***@pg-new.internal:5432/sales?sslmode=require

Oracle (JDBC):
  jdbc:oracle:thin:@//ora-legacy.internal:1521/SALESPDB

Connecting from Python to both sides

python
import os
import pandas as pd
from sqlalchemy import create_engine

# Credentials come from the environment or a secrets manager, never hard-coded
source = create_engine(
    "mssql+pyodbc://{u}:{p}@db-prod-01:1433/sales"
    "?driver=ODBC+Driver+18+for+SQL+Server&Encrypt=yes".format(
        u=os.environ["SRC_USER"], p=os.environ["SRC_PASS"]))

target = create_engine(
    "mysql+pymysql://{u}:{p}@mysql-new.internal:3306/sales".format(
        u=os.environ["TGT_USER"], p=os.environ["TGT_PASS"]),
    connect_args={"ssl": {"ca": "/etc/ssl/rds-ca.pem"}})

# Read through bridge A, write through bridge B, in chunks
for chunk in pd.read_sql("SELECT * FROM dbo.customers", source, chunksize=10_000):
    chunk.to_sql("customers", target, if_exists="append", index=False)

# Validate: row counts must match
src_n = pd.read_sql("SELECT COUNT(*) AS n FROM dbo.customers", source).n[0]
tgt_n = pd.read_sql("SELECT COUNT(*) AS n FROM customers", target).n[0]
print("source", src_n, "target", tgt_n, "OK" if src_n == tgt_n else "MISMATCH")

Each database as its own account

In MySQL, and on most platforms, each database created on a server acts as a separate, self-contained unit with its own objects and its own permissions. The lecture compares it to social media accounts: each has its own login and its own boundaries, even though they all live on the same platform. Treating each database and each user as a distinct entity makes it easier to keep security and organisational boundaries across servers and services.

For migrations, that means creating dedicated accounts rather than reusing admin or application logins:

sql
-- MySQL target: a writer that can only touch the sales database
CREATE USER 'migration_writer'@'10.0.%' IDENTIFIED BY '***' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, CREATE, INDEX ON sales.* TO 'migration_writer'@'10.0.%';

-- SQL Server source: a read-only login for extraction
CREATE LOGIN migration_reader WITH PASSWORD = '***';
CREATE USER migration_reader FOR LOGIN migration_reader;
ALTER ROLE db_datareader ADD MEMBER migration_reader;

Security

Connections must be secure, reliable and properly configured:

  • Protect credentials: use a secrets manager or environment variables, and never commit passwords to code or share them in chat.
  • Least privilege: read-only on the source, only the rights needed on the target, and access limited to authorised users and hosts.
  • Encrypt in transit: TLS/SSL (Encrypt=yes, sslmode=require, REQUIRE SSL).
  • Restrict the network: firewalls, private endpoints, VPN or bastion hosts; don't expose database ports to the internet.
  • Make accounts temporary: expire or remove migration accounts after cutover, and log their use.

Troubleshooting connections

Key takeaways

  • Connecting to source and target is the first technical task in any migration.
  • A migration tool bridges both databases, reading from one and writing to the other.
  • The parameters are server, port, database name, username, password and authentication method.
  • Drivers differ by platform (default ports 1433, 3306, 5432, 1521); the parameters don't.
  • Secure every connection: protected credentials, least privilege, TLS and restricted networks.

This completes Section 4: Database Platforms for Migration.