Section 4: Database Platforms for Migration. Lesson 17.
What is a database?
A database is an organised digital collection of structured information, designed so computers can store, search and retrieve it quickly. It can be efficiently stored, accessed, modified and managed by administrators.
Think of a large container holding smaller containers: the database holds tables, and tables hold rows. Instead of searching everything by hand, a query asks for items 1 and 2 and the database returns exactly those, immediately.
Databases are the main systems for storing, managing, organising and retrieving business data, so most migration projects move data from one database environment to another. That can mean upgrading a database, changing platforms entirely, or consolidating several databases into one.
A table, like a spreadsheet
Columns define what is stored (customer_id, full_name, email and so on), rows hold one record each, and a key such as customer_id identifies each record uniquely.
DBMS: the database management system
A DBMS is the software that sits between a database and its users or applications, letting them create, read, update and delete data efficiently (CRUD). It works like a meticulous digital librarian:
- Organises the raw data into tables, indexes and schemas
- Enforces security rules and permissions
- Removes the redundancy common in plain file storage
- Handles transactions, concurrency, backups and recovery
Examples of relational DBMSs: Microsoft SQL Server, MySQL, PostgreSQL, Oracle Database. Non-relational examples: MongoDB, Cassandra, Redis.
Three directions of migration
- Up (upgrade): from a legacy or older system to a newer one. For example, SQL Server 2012 to SQL Server 2022, or an on-premises Oracle database to a managed cloud database. The most common direction.
- Down (downgrade): deliberately moving from a newer system back to an older one. Rarer. Reasons include a failed upgrade that needs rolling back, a partner or regulator that only supports an older version, or licensing costs.
- Neutral (lateral): moving to a different platform at roughly the same level, where the goal is a change of platform rather than an upgrade. For example, MySQL to PostgreSQL, or one cloud provider's managed database to another's.
A downgrade needs extra care: the older target may lack data types, functions or features the source uses, so compatibility analysis matters even more.
Popular database platforms
- Microsoft SQL Server: Microsoft's relational database server, common in enterprises and Windows environments (covered in lesson 18).
- Amazon RDS (Relational Database Service): an easy-to-manage relational database service built for low total cost of ownership. It is simple to set up, operate and scale with demand, and automates routine database management such as provisioning, configuration, backups and patching. It runs engines including MySQL, PostgreSQL, MariaDB, Oracle and SQL Server.
- Azure SQL Database: a fully managed cloud version of Microsoft SQL Server, part of Azure. It handles upgrades, patching, backups and monitoring without you managing servers, and offers a free tier, which makes it a good place to practise.
- Also covered in this section: MySQL (lesson 19) and Oracle Database (lesson 20). PostgreSQL appears throughout as a common migration target.
Source and target databases
- The source database holds the existing data that needs to be migrated.
- The target database is where the data will be permanently stored after migration.
Platforms differ in architecture, data types, constraints, indexing and SQL syntax, so the team must analyse compatibility and transformation requirements before moving anything.
Checking feasibility
The lecture asks ChatGPT: "I want to migrate from SQL Server to PostgreSQL. Is it feasible?" The answer breaks the work down by object type, roughly in order of difficulty:
Everything is possible with effort, but check feasibility first. Tools such as AWS Schema Conversion Tool, Ora2Pg, pgloader and SQL Server Migration Assistant (SSMA) produce assessment reports that list which objects convert automatically and which need manual work.
Migration is more than copying rows
A database migration usually involves:
- Schema migration: tables, keys, indexes, constraints
- Data transformation: type conversions and reformatting
- Validation: counts, checksums, sample comparisons
- Performance optimisation: indexes and query tuning on the new engine
- Application compatibility testing: the application's queries must work against the new database
Example: a table saved in Oracle cannot simply be copied into SQL Server as-is. The two engines use different types and syntax, so an unmodified copy fails or produces a broken migration.
-- Oracle source
CREATE TABLE customers (
customer_id NUMBER(10) PRIMARY KEY,
full_name VARCHAR2(100) NOT NULL,
email VARCHAR2(255),
created_at DATE DEFAULT SYSDATE
);
-- SQL Server target: types and defaults must change
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
full_name NVARCHAR(100) NOT NULL,
email NVARCHAR(255),
created_at DATETIME2 DEFAULT SYSDATETIME()
);Structured, semi-structured and unstructured data
- Structured data is organised into predefined, standardised formats, which makes it easy for people and machines to read and interpret. Examples: relational tables, spreadsheets, transaction records. Stored in SQL databases.
- Semi-structured data does not live in traditional relational tables, but uses tags, markers or metadata to separate and label its elements. Examples: JSON, XML, YAML, email headers, log files. Stored in document databases such as MongoDB, or in JSON columns in PostgreSQL and SQL Server.
- Unstructured data is raw, native information with no predefined format or model, which makes it harder to store, process and analyse in traditional systems. Examples: documents, PDFs, images, audio, video, free-text notes. Stored in object storage (S3, Azure Blob), often with metadata in a database.
{
"customer_id": 1001,
"name": "Sarah Johnson",
"tags": ["vip", "newsletter"],
"address": { "city": "London", "postcode": "EC1A 1BB" }
}The JSON above is semi-structured: its keys act as tags that label each value, but records don't all have to share the same fields. Migrating between these types (for example flattening JSON into relational columns) is a transformation step in its own right.
Key takeaways
- A database is an organised collection of data built for fast storage and retrieval; a DBMS is the software that manages it.
- Migrations go up (upgrade), down (downgrade) or neutral (lateral change of platform).
- Common platforms: SQL Server, Amazon RDS, Azure SQL, MySQL, Oracle and PostgreSQL.
- Check source-to-target feasibility first: tables are easy; views, procedures, functions and triggers take effort.
- Data comes structured, semi-structured or unstructured, and each is stored and migrated differently.
Next: 18. Working with Microsoft SQL Server.