Migration & Scalability

1. Database Migration

Data migration = moving data from one system to another. "System" is broader than it sounds: application + DBMS + OS + storage — any one of these four can be the reason for a migration.

Note

Data usually lives much longer than the system it's stored in.

2. Application Upgrade

Upgrades often require changes to the database schema.

Version control approach	Description
State based	The application contains the schema; a tool compares and runs `ALTER`
Migration based	The application contains migration scripts

Schema Migration Example

v1.0  →  ALTER TABLE employee ADD birth_date DATE;  →  v2.0
v2.0  →  ALTER TABLE employee MODIFY email VARCHAR(80);  →  v2.1

3. DBMS Upgrade or a Different DBMS

Scenario	Approach
DBMS upgrade (same vendor)	Usually automatic — otherwise a logical backup/restore
To a different DBMS	Special tools or manual export/import via CSV — not very common
Different server, same DBMS	Physical or logical backup/restore — e.g. hardware upgrade, cloud
Different hardware/VM	OS image

4. Migration Scenarios

Method	Description
Backup & restore	Backup on the source, restore on the target
Detach & attach	Detach the database and reattach it on another instance
Copy database	A wizard to copy a database to another instance
Generate scripts wizard	A script to transfer the database (logical backup)

5. Detach Database

Detaches the database from its SQL instance — preserves the data and log files in their current state.

Only possible if: - The database isn't replicated, published or mirrored - There are no snapshots - It's not a system database - The database isn't in a 'suspect' state

Via SSMSVia T-SQL

Object Explorer → right-click the database → Tasks → Detach...

USE master;
GO

EXEC sp_detach_db @dbname = [AdventureWorks2019];
GO

6. Attach Database

Reattaches the database to a SQL Server instance.

Only possible if all files are present
Allows specifying an alternative location for the files
Not possible for an older SQL instance version

Via SSMSVia T-SQL

Object Explorer → right-click Databases → Attach...

USE master;
GO

CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'D:\MySQLServer\AdventureWorks_Data.mdf'),
       (FILENAME = 'D:\MySQLServer\AdventureWorks_Log.ldf')
    FOR ATTACH;
GO

7. Copy Database

Copies the original database to another SQL instance (possibly on a different physical server).

The original database remains available to users
An ideal scenario for testing the impact on new hardware/software

Via SSMS: right-click the database → Tasks → Copy Database...

Transfer methods: - Detach and attach method — faster, requires the database to go offline - SQL Management Object method — slower, the database stays online

8. Generate and Publish Scripts Wizard

Creates a SQL script to transfer the database (logical backup).

Via SSMS: right-click the database → Tasks → Generate Scripts...

9. Scalability — Scale UP vs Scale OUT

Two fundamentally different choices when a system comes under pressure:

Strategy	Meaning
Scale UP	Give more resources to the existing server (more RAM, CPU, ...)
Scale OUT	Use more servers

Scale-OUT Options in SQL Server

Database mirroring
Database replication
SQL Server Always On
- Always On Availability Groups
- Always On Failover Cluster Instances

10. Database Mirroring

High availability for a single database via a synchronized copy.

The source (principal) forwards transactions to the mirror
The mirror is a 'warm/hot standby'
An additional witness server is needed for automatic failover
Every DML command is also executed on the mirror

Operating Modes

Mode	Data transfer
High-safety	Synchronous
High-performance	Asynchronous

Transport Security

Windows Authentication
Certificate-based Authentication

Requirements

Only possible for databases with the full recovery model
The mirror database's name must be identical to the primary's
The database must first be restored on the mirror instance with NORECOVERY
- Any differential backup must also be restored
- All log backups after the differential/full backup must also be restored

Via SSMS: right-click the database → Tasks → Mirror...

11. Database Replication

Technology to copy data to another SQL instance with synchronization.

An asynchronous process with a delay before data is available everywhere
Based on the publisher–distributor–subscriber principle

Roles

Role	Description
Publisher	Holds the source database — makes data available for replication
Article	Database objects included in the replication (tables, views, indexes, ...)
Publication	A collection of articles — needed for distribution
Distributor	The intermediary — receives transactions and distributes them to subscribers
Subscriber	Receives the changed data — can send data back or distribute it further
Subscription	A subscriber's request to receive a publication

Subscription Types

Type	Created on	Who initiates
Push	Publisher server	The publisher sends the data out
Pull	Subscriber server	The subscriber starts the replication process

Replication Types

Type	Description	Ideal for
Transactional	Uses the transaction log to replicate — one-way traffic	Fast updates at the subscriber
Snapshot	Copies all published data via a snapshot	Data that changes infrequently, small volumes
Merge	Replication in all directions — subscriber changes are also propagated	Offline updates that get synchronized
Peer-to-peer	Every server is simultaneously publisher, distributor and subscriber	Data spread across publishers, low chance of conflicts

Configuration (via SSMS)

Configure Distribution — set up the distributor, snapshot folder, distribution database
New Publication — publication type, select articles, filters, snapshot agent
New Subscription — choose publisher + publication, subscription database, sync schedule

Replication Monitor

Via SSMS: right-click Replication → Launch Replication Monitor

Only accessible as a member of sysadmin (Distributor) or replmonitor (distribution database)
Shows agent status, sync history, latency

12. SQL Server Always On

The solution for high availability and disaster recovery.

Technology	Description
AlwaysOn AG (Availability Groups)	Database-level HA — no shared storage
AlwaysOn FCI (Failover Cluster Instances)	Instance-level HA — requires shared storage

AlwaysOn Availability Groups (AG)

Databases can have 1 or more replicas on different SQL Servers
Each server stores the data locally — no shared storage
Replication can be synchronous or asynchronous
An Enterprise feature (available in a limited form from SQL Server 2016 Standard Edition)
Suited for disaster recovery across datacenters

AlwaysOn Failover Cluster Instances (FCI)

Requires shared storage: iSCSI or Fibre Channel SAN
Supports multisite clustering across subnets
Failover of SQL Server instances across datacenters is possible

FCI vs AG Comparison

Failover Cluster Instances	Availability Groups
A shared storage solution	No shared storage
Instance-level HA	Database-level HA
Logins, jobs, certs intact after failover	Must be manually added to secondaries
Active/Passive nodes	A DR replica can be an Active Secondary (backup, read-only)
Connection via a virtual server name	Connection via an AG listener name
No protection against I/O failure	Protection against I/O failure
No requirements for the recovery model	Databases must be in the FULL recovery model