Architecture Overview

SQL Server uses a thread-based architecture where incoming requests are converted into tasks executed by worker threads managed internally by SQL OS (SOS Scheduler). This design enables efficient multitasking, concurrency handling, and CPU utilization within a centralized engine process.

PostgreSQL uses a process-based architecture where every client connection is handled by a dedicated backend operating system process. Query execution scheduling is managed directly by the operating system scheduler rather than an internal scheduler like SQL Server.

This architectural difference is one of the most important distinctions between the two database systems and directly affects scalability, memory usage, parallelism behavior, troubleshooting, and connection management.

SQL Server Architecture

SQL Server Execution Flow

SQL Server is Thread-Based Model.

SQL Server converts incoming requests into tasks and assigns worker threads from a shared thread pool. These worker threads are scheduled on CPU cores through SOS Schedulers.

SQL OS and Scheduler

SQL Server contains an internal operating layer called SQL OS. It manages:

• Worker threads
• Scheduling
• Memory allocation
• Synchronization
• I/O operations

The SOS Scheduler is responsible for mapping SQL Server worker threads onto CPU cores efficiently.

PostgreSQL Architecture

PostgreSQL Process-Based Model

PostgreSQL uses a dedicated process architecture.

Each client connection creates a separate backend process at the operating system level.

Unlike SQL Server:

• No centralized thread pool exists
• No internal scheduler similar to SOS Scheduler exists
• The operating system scheduler handles process execution

SQL Server and PostgreSQL Request Lifecycle

PostgreSQL Process Tree

postmaster
  ├── checkpointer
  ├── WAL writer
  ├── background writer
  ├── autovacuum launcher
  ├── autovacuum worker 1
  ├── autovacuum worker 2
  ├── backend_process_101
  ├── parallel_worker_1
          ├── parallel_worker_2
  ├── backend_process_102
  ├── backend_process_103
          ├── parallel_worker_1
          ├── parallel_worker_2

Shared Background Processes

Even with many client connections, several PostgreSQL system processes remain singleton shared processes for the entire instance.

Example

500 client connections

10,000 active queries

Possible process distribution:

500 backend processes

BUT still only:

• 1 WAL writer
• 1 checkpointer
• 1 background writer
• 1 autovacuum launcher

Some PostgreSQL worker processes scale dynamically based on workload:

• Autovacuum workers
• Parallel query workers

Configuration parameters include:

• autovacuum_max_workers
• max_parallel_workers
• max_parallel_workers_per_gather

Parallelism

SQL Server Parallelism

SQL Server parallel execution uses multiple worker threads from the shared thread pool.

Parallel Thread Lifecycle

Parallel worker threads
↓
Borrowed from thread pool
↓
Execute query
↓
Returned to thread pool

Characteristics

• Lightweight thread reuse
• Lower scheduling overhead
• Efficient for high concurrency
• Managed internally by SQL OS

PostgreSQL Parallelism

PostgreSQL parallel execution uses multiple operating system processes called parallel workers.

Parallel Worker Lifecycle

Parallel worker processes
↓
Created temporarily
↓
Execute parallel query
↓
Terminated or released

Characteristics

• Full operating system processes
• Higher creation overhead than threads
• Higher scheduling cost
• Managed by operating system scheduler

This is one reason PostgreSQL parallelism behaves differently from SQL Server under heavy workloads.

External Command Execution

SQL Server xp_cmdshell Behavior

SQL Server allows execution of operating system commands using xp_cmdshell.

These commands execute outside the normal SQL execution engine.

If the external operating system process hangs:

• The SQL Server worker thread waiting on it may also become stuck
• KILL may not terminate the session successfully
• The worker thread can remain blocked in external system calls
• SQL Server service restart or operating system intervention may be required

PostgreSQL External Process Isolation

PostgreSQL can face similar issues during external program execution.

However, because PostgreSQL uses isolated backend processes:

• Stuck operations usually affect only a single backend process
• Individual backend processes are easier to terminate independently
• Overall database stability is generally less affected

This provides stronger isolation and fault containment compared to SQL Server.

Connection Scalability

SQL Server

SQL Server handles large connection counts efficiently because worker threads are shared through a centralized thread pool.

Memory usage per connection is relatively low.

PostgreSQL

PostgreSQL creates one backend process per connection.

Large numbers of connections can significantly increase:

• Memory usage
• Process management overhead
• CPU scheduling overhead

For this reason, PostgreSQL environments commonly use connection pooling tools such as:

• PgBouncer
• Pgpool-II

SQL Server vs PostgreSQL Internal Components

Both databases implement similar core database concepts but with different internal architectures.

PostgreSQL vs SQL Server

Query Processing Similarities

Although their architectures differ, both databases generally follow similar processing principles.

General Flow

Client Query
↓
Check memory cache
↓
If data exists:
Return data
Else:
Read from disk
↓
Modify pages in memory
↓
Write transaction log first
↓
Checkpoint writes dirty pages to data files

Both systems implement Write-Ahead Logging (WAL) principles to guarantee durability and crash recovery.

MVCC and Concurrency Control

PostgreSQL MVCC

PostgreSQL uses native MVCC (Multi-Version Concurrency Control).

When rows are updated or deleted:

• Old row versions are not immediately removed
• UPDATE creates a new row version
• DELETE marks rows as dead tuples

Important Behavior

UPDATE does NOT overwrite rows in place

DELETE does NOT physically remove rows immediately

Dead tuples remain inside tables until cleaned later.

Consequences of Excessive Dead Tuples

• Table bloat
• Index bloat
• Slow scans
• Wasted storage

Autovacuum

Purpose

PostgreSQL uses Autovacuum to maintain MVCC storage efficiency.

Autovacuum does not continuously scan all tables. It triggers dynamically based on thresholds such as dead tuple counts.

Components

Autovacuum Launcher

Responsible for monitoring databases and scheduling cleanup activity.

Autovacuum Workers

Actual worker processes that clean dead tuples and update statistics.

The number of workers can increase dynamically depending on workload.

Autovacuum Responsibilities

1. Vacuum

• Cleans dead tuples
• Marks space reusable

2. Analyze

• Updates optimizer statistics
• Helps query planner generate efficient execution plans

MVCC vs SQL Server Versioning

PostgreSQL MVCC is conceptually similar to SQL Server row versioning, but implementation differs significantly.

SQL Server

• Primarily lock-based concurrency
• Optional row versioning
• Version store maintained in TempDB

PostgreSQL

• Native MVCC storage engine
• Every update creates new row versions
• Old versions cleaned later by vacuum

Isolation and Concurrency

PostgreSQL default isolation level is:

READ COMMITTED

Concurrency is achieved using snapshot-based MVCC, allowing:

• Readers and writers to operate concurrently
• Reduced blocking behavior

Important Note

MVCC continues functioning even without vacuum,

but performance eventually degrades severely.

Storage Engine Differences

SQL Server Storage Engine

• Page-based storage engine
• In-place updates
• Locking-based concurrency
• Optional row versioning

PostgreSQL Storage Engine

• Native MVCC storage engine
• Every update creates new row versions
• Vacuum-based cleanup mechanism

Key Architectural Differences Summary

Conclusion

SQL Server and PostgreSQL achieve similar database goals using fundamentally different architectural approaches.

SQL Server emphasizes centralized thread scheduling, lightweight worker reuse, and integrated resource management through SQL OS.

PostgreSQL emphasizes process isolation, operating system scheduling, and native MVCC-based concurrency control.

These differences affect:

• Performance tuning
• Connection scalability
• Parallel query execution
• Memory consumption
• Troubleshooting behavior
• Maintenance operations

Understanding these architectural differences is essential when transitioning from SQL Server administration to PostgreSQL administration.

Recently, I worked on a Proof of Concept (POC) for migrating Microsoft SQL Server 2019 to PostgreSQL 16.

For the initial migration approach, I chose pgloader because it is relatively easy to set up and provides direct support for SQL Server to PostgreSQL migration.

At the beginning, pgloader looked like a very good option for the migration. However, once the migration started moving closer to a production-grade setup, several limitations and failures became visible. I experienced multiple issues while testing with the AdventureWorks 2019 database.

In this blog, I will cover:

• Environment setup
• Migration approach
• Problems faced during migration
• Fixes and workarounds
• Practical limitations of pgloader
• Alternative considerations for complex migrations

Environment Setup

I built two separate virtual machines for the migration setup.

Windows VM

Used as the source database server.

Setup included:

• SQL Server 2019 Developer Edition
• AdventureWorks2019 database restored
• Separate login created for migration user
• TCP/IP enabled in SQL Server Configuration Manager
• Windows firewall and network configuration updated to allow Ubuntu VM access and pgloader connectivity

Ubuntu VM

Used as the PostgreSQL and migration server.

Setup included:

• Ubuntu Server
• Network configuration using DHCP
• SSH configuration
• PostgreSQL 16 installation
• pgloader installation
• Python and pyodbc installed for connectivity testing
• PostgreSQL database created for migration

Personally, Linux environments always feel easier and cleaner to work with for database and migration-related activities.

Installation and setup documentation for both environments are attached in the reference link.

Prerequisites Before Migration

Before starting the migration, several configurations were required.

FreeTDS Configuration

When loading data from SQL Server, pgloader depends on FreeTDS.

I had to configure the ~/.freetds.conf file as follows:

[global]
 tds version = 7.4
 client charset = UTF-8
 min pool conn = 20
 max pool conn = 8192
 max member age = 120

Without this configuration, pgloader continuously failed with:

Max connections reached, increase value of TDS_MAX_CONN

In several cases, pgloader crashed completely and dropped into the Lisp debugger.

PostgreSQL Compatibility and Collation

The PostgreSQL database needs to be created carefully with matching compatibility expectations and collation settings.

Collation differences between SQL Server and PostgreSQL can lead to unexpected behavior after migration.

--SQL Server Database Collation
SELECT
    name,
    collation_name
FROM sys.databases
WHERE name = 'AdventureWorks2019';

--Postgres Database Collation
SELECT
    datname,
    encoding,
    datcollate,
    datctype
FROM pg_database
WHERE datname = 'testdb';

UUID Extension

UUID support was required in PostgreSQL and needed to be enabled manually before migration.

CREATE EXTENSION "uuid-ossp";

Authentication Issues

Authentication failures occurred because of PostgreSQL password encryption policies.

The postgresql.conf file needed verification and adjustment based on the pgloader compatibility.

Network Accessibility

Connectivity between both virtual machines must be validated before starting migration:

• SQL Server port accessibility
• PostgreSQL accessibility
• SSH connectivity
• Firewall rules
• DNS/IP resolution

Migration Approach Using PGLoader

The migration was planned as a two-phase approach.

Phase 1 — Schema Only Migration

Phase 2 — Data Only Migration

This approach helped isolate schema conversion problems from data movement issues.

Phase 1 — Schema Migration

pgloader supports schema-only migration without loading data.

pgloader works very well for:

• Normal tables
• Primary keys
• Standard indexes
• Regular datatypes
• Bulk loading operations

However, SQL Server contains many proprietary features that PostgreSQL does not support directly, and pgloader cannot fully translate all SQL Server semantics automatically.

Problems Faced During Schema Migration

Computed Columns

Computed columns from SQL Server were not migrated correctly.

Instead of preserving the logic, pgloader converted them into plain text columns.

The computed logic had to be recreated manually in PostgreSQL.

Foreign Keys and Composite Keys

PostgreSQL requires referenced columns in foreign keys to be either:

• Primary keys
• Unique keys

SQL Server is more flexible in some scenarios.

The existing schema design in AdventureWorks2019 did not always satisfy PostgreSQL requirements.

Because of this:

• Some foreign keys could not be created directly
• Tables had to be migrated first without constraints
• Foreign keys were added later manually

However, this created another issue during Phase 2.

If orphan records existed in child tables, referential integrity validation failed while creating constraints later.

Index Creation Issues

Some SQL Server indexes could not be migrated properly.

Problems included:

• XML indexes
• SQL Server-specific indexing behavior
• Unsupported index structures

PostgreSQL XML indexing does not behave the same way as SQL Server XML indexes.

In many cases, the best approach was:

1. Skip index creation during migration
2. Validate schema and data
3. Recreate indexes manually afterward

Naming Convention Limitations

PostgreSQL allows only 63 characters for object names.

SQL Server supports up to 128 characters.

Several objects required renaming because names exceeded PostgreSQL limits.

Unsupported Proprietary Datatypes

One of the major blockers was proprietary SQL Server datatypes.

Examples included:

• SYBMSXML
• SYBMSUDT
• CLR types
• hierarchyid
• geography
• geometry

pgloader generated errors such as:

unsupported type SYB-MSUDT

These errors originated from FreeTDS and pgloader while reading SQL Server metadata.

Because of these unsupported datatypes:

• Metadata extraction failed
• Row conversion failed
• Parser crashes occurred
• Runtime instability increased significantly

Phase 2 — Data Migration

This phase introduced even more stability and datatype-related issues.

Problems Faced During Data Migration

Unsupported Datatype Failures

Data movement failed for tables containing unsupported datatypes like SYB-MSUDT.

The failure pattern was:

• pgloader encounters unsupported type
• Conversion process fails
• FreeTDS cleanup breaks
• SBCL runtime crashes

Runtime Failures During Full Database Migration

Migrating the complete database in a single run was not feasible.

pgloader internally uses:

• SBCL (Lisp runtime)
• FreeTDS (SQL Server driver)

Error handling becomes unstable when:

• Unsupported datatypes are encountered
• Connections fail unexpectedly
• Cleanup operations break

Large database migrations with complex schemas caused runtime crashes repeatedly.

Switching to Table-Level Migration

Since full database migration failed continuously, I changed the strategy to table-level migration.

The loading sequence followed:

1. Lookup and master tables
2. Parent tables
3. Child tables
4. Transaction tables

This improved control over migration dependencies.

However, tables containing:

• hierarchyid
• geometry
• geography
• Complex CLR types

still failed consistently because pgloader could not deserialize these datatypes correctly.

Final Outcome of the POC

The migration succeeded for:

• Tables with simpler datatypes
• Standard relational structures
• Simpler relationships

However, tables containing:

• Proprietary SQL Server datatypes
• Spatial types
• Complex relationships
• Advanced SQL Server-specific features

required:

• Custom ETL solutions
• Manual schema recreation
• Alternative migration tools

Additionally:

• Foreign keys were created manually
• Indexes were recreated manually
• Constraint validation required manual verification

Conclusion

pgloader is a very good tool for:

• Small to medium migrations
• Standard relational databases
• Simple schema structures
• Quick POCs

But for production-grade SQL Server migrations involving:

• Proprietary SQL Server features
• Spatial datatypes
• CLR types
• Complex relational dependencies
• Enterprise-scale databases

pgloader alone may not be sufficient.

A hybrid migration strategy involving:

• Manual schema adjustments
• Custom ETL pipelines
• Alternative migration utilities
• Incremental table-level migration

becomes necessary for stable migration execution.

GitHub repository containing migration scripts, setup documentation, configuration files, and troubleshooting references used during the SQL Server 2019 to PostgreSQL 16 migration POC.

Disaster Recovery (DR) in Microsoft SQL Server is a crucial process for ensuring business continuity and minimizing data loss in the event of hardware failures, cyber-attacks, system crashes, or natural disasters. A well-defined DR strategy ensures that databases remain available with minimal downtime. SQL Server provides multiple recovery options, each with different levels of Recovery Point Objective (RPO) and Recovery Time Objective (RTO).

Why Disaster Recovery is Important

• Ensures business continuity
• Reduces downtime and data loss
• Protects against cyber threats and accidental deletions
• Meets compliance and regulatory requirements

Key Disaster Recovery Strategies in MSSQL

1. Backup and Restore(For detailed implementation, refer to Backup and Restore Guide)

The foundation of any DR plan is a reliable backup and restore strategy. SQL Server supports multiple backup types:

• Full Backup: Captures the entire database.
• Differential Backup: Backs up changes since the last full backup.
• Transaction Log Backup: Captures transactional changes, enabling point-in-time recovery.

2. Log Shipping (For detailed implementation, refer to Log Shipping Guide)

Log Shipping automatically copies and restores transaction logs from a primary server to one or more secondary servers. It provides a warm standby solution with some delay in synchronization.

Key Features:

• Automates transaction log backups and restores
• Supports multiple secondary servers
• Provides disaster recovery with minimal complexity

3. Database Mirroring (For detailed implementation, refer to Mirroring Guide)

Database Mirroring maintains a real-time copy of a database on a secondary server, providing high availability and disaster recovery.

Modes of Operation:

• Synchronous (High-Safety Mode): Ensures zero data loss but may introduce latency.
• Asynchronous (High-Performance Mode): Provides better performance with a risk of minimal data loss.

4. Always On Availability Groups

Always On Availability Groups (AGs) provide advanced disaster recovery and high availability by supporting multiple readable secondary replicas.

Key Benefits:

• Allows automatic and manual failover
• Enables read-only queries on secondary replicas
• Offers near-zero data loss with synchronous replication

5. Replication

Replication involves copying and distributing database objects from one server to another, keeping them in sync. It is mainly used for data distribution and reporting.

Types of Replication:

• Snapshot Replication: Transfers a static copy of the database.
• Transactional Replication: Continuously replicates changes in near real-time.
• Merge Replication: Allows updates on both primary and secondary databases.

6. Failover Clustering

SQL Server Failover Cluster Instances (FCI) provide high availability by grouping multiple servers into a cluster. If one node fails, another takes over, ensuring continuous availability.

Choosing the Right Disaster Recovery Strategy

Conclusion

Disaster Recovery in MSSQL is essential for protecting business-critical data. The right strategy depends on business requirements, system architecture, and tolerance for downtime and data loss. While backup and restore is the most fundamental approach, log shipping, mirroring, Always On, and failover clustering provide advanced solutions for minimizing downtime and ensuring data integrity.

For detailed guides on implementing specific disaster recovery methods, refer to:

• Log Shipping Guide
• Mirroring Guide

Introduction

SQL Server mirroring maintains two copies of a single database on separate server instances of the SQL Server Database Engine. This technology enhances high availability by keeping a synchronized copy of the primary (principal) database on a secondary (mirror) server and offers both manual and automatic failover options. As a disaster recovery strategy, mirroring minimizes downtime and data loss by ensuring that if the primary server fails, the mirror is ready to take over immediately.

Prerequisites

Understanding Backups and Log Shipping

Before setting up mirroring, it’s important to understand:

• Backups: Regular full, differential, and transaction log backups secure your data and are part of a broader disaster recovery strategy.
• Log Shipping: Automatically sends transaction log backups from one server (primary) to another (secondary) to maintain an up-to-date copy of the database.
• For more details about Log Shipping https://medium.com/@mohammedtharif30/deep-dive-into-log-shipping-in-mssql-79e411fb46dc

Why It Is Needed

• Minimize Downtime: Allows for a quick switch to the mirror if the primary fails.
• Ensure Data Consistency: Synchronizes transactions in real time, keeping data consistent across servers.
• High Availability: Supports critical applications that require continuous uptime.

What is Mirroring?

Mirroring is a high-availability solution that creates and maintains two copies of a database:

• Principal Server: The primary server connected to clients where all transactions are initiated.
• Mirror Server: A secondary server that continuously receives and applies changes from the principal.
• Witness Server (Optional): Monitors the connection between the principal and mirror and helps trigger automatic failover if needed.

Note: Mirroring requires the database to use the Full Recovery Model.

Log Shipping vs. Mirroring

Log shipping copies transaction logs at regular intervals from a primary server to a secondary server, providing a backup that is slightly delayed. Mirroring continuously synchronizes a secondary server with the primary, offering a near real-time duplicate for quick failover.

Real-World Example

Imagine an online store:

• They use mirroring for their order processing system to ensure immediate failover if the primary server fails, keeping transactions live.
• They use log shipping for their reporting system, updating data every few minutes so the analytics team can generate reports without impacting the live environment.

Components for the Mirroring

Principal Server

• Hosts the primary database.
• Processes client transactions and writes transaction logs.

Mirror Server

• Maintains a near real-time copy of the principal database.
• Receives and applies committed transaction logs via dedicated TCP endpoints.

Witness Server (Optional)

• Monitors the status of the principal.
• Participates in a voting process to trigger automatic failover when necessary.

How it Works

What is Failover?

Failover is the process of switching operations from a primary database server to a standby server when the primary becomes unavailable. This ensures that applications continue to operate with minimal interruption and data loss.

Failover: Automatic vs. Manual

• Manual Failover:
• Requires only the principal and mirror.
• An administrator manually reassigns roles, resulting in a period of downtime.
• Automatic Failover:
• Involves the principal, mirror, and witness servers.
• Heartbeat signals from all three servers determine availability. If the witness loses connection to the primary, the mirror is automatically promoted to primary, minimizing downtime.
• Note: If the principal loses connection to the mirror but remains connected to the witness, automatic failover does not occur.

There are two primary operational modes in SQL Server mirroring, each offering different trade-offs between safety and performance:

• Synchronous Mode (High-Safety):
  Ensures that every transaction is committed on both the principal and mirror before confirming the commit to the client. This mode maximizes data protection by fully synchronizing both servers, albeit at a potential performance cost.
• Asynchronous Mode (High-Performance):
  Prioritizes transaction speed by confirming commits on the principal immediately. The transaction log is sent to the mirror after the fact, which may lead to a slight lag in synchronization but boosts overall performance.

Example Scenario for the Synchronous and Asynchronous modes:

Synchronous Mode (High-Safety)

1. A client initiates a transaction on the principal.
2. The principal writes the transaction to its local log.
3. The transaction log is sent immediately to the mirror.
4. The mirror writes the log record to its own log and sends an acknowledgment.
5. Once the acknowledgment is received, the principal confirms the commit to the client.

Result: The transaction is committed on both servers before confirmation, ensuring full synchronization.

Asynchronous Mode (High-Performance)

1. A client initiates a transaction on the principal.
2. The principal writes the transaction to its log and immediately confirms the commit to the client.
3. The transaction log is sent to the mirror after the commit.
4. The mirror applies the transaction later, which may introduce a slight delay.

Result: Improved performance with a potential minor lag on the mirror.

Note: Automatic failover in SQL Server mirroring is supported only when operating in synchronous (high-safety) mode with a witness server configured. Manual failover, on the other hand, can be initiated in both synchronous and asynchronous modes.

Implementation Using TCP Endpoints

Mirroring uses dedicated TCP endpoints to transfer transaction logs between the principal and mirror servers. This section shows how to configure these endpoints using T‑SQL, and notes that a GUI method (via SQL Server Management Studio) is also available.

There are two ways to set up SQL Server instances for mirroring:

1. Creating Two Instances on a Single Machine — This method involves installing multiple SQL Server instances on the same machine. It is useful for testing and development purposes.
2. Creating Two Instances Under a Domain in a Server — This approach is used in enterprise environments where SQL Server instances are installed on different machines within a domain, allowing authentication and communication using domain accounts.

Using T‑SQL Commands

Create Mirroring Endpoints on Principal and Mirror Servers

On the Principal Server:

— Create the mirroring endpoint on the Principal Server
CREATE ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = ALL);
- Grant connect permission to the public role
GRANT CONNECT ON ENDPOINT::[Mirroring] TO PUBLIC;

On the Mirror Server:

 — 
CREATE ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = ALL);
 - Grant connect permission to the public role
GRANT CONNECT ON ENDPOINT::[Mirroring] TO PUBLIC;

Configuring the Database for Mirroring

On the Principal Server:

- Configure the database to set up mirroring
ALTER DATABASE YourDatabase
SET PARTNER = 'TCP://MirrorServer:5022';
 - (Optional) Configure the witness server for automatic failover
ALTER DATABASE YourDatabase
SET WITNESS = 'TCP://WitnessServer:5022';

Verifying the Mirroring Configuration

SELECT
DB_NAME(database_id) AS DatabaseName,
mirroring_state_desc,
mirroring_partner_name,
mirroring_witness_name
FROM sys.database_mirroring
WHERE DB_NAME(database_id) = 'YourDatabase';

Using the GUI (SQL Server Management Studio)

Alternatively, you can configure mirroring via SSMS:

1. Open SSMS and connect to your SQL instance.
2. Right-click on the database → select Properties.
3. Navigate to the Mirroring page and follow the wizard to configure the principal, mirror, and (optional) witness servers.
4. Provide the TCP endpoint details for each server and complete the configuration.

Database Snapshots and Standby Mode

Database Snapshots

Purpose:

Since a mirrored database is in standby (or no recovery) mode and cannot be directly accessed, database snapshots provide a read-only view for reporting or analysis.

Use Cases:

• Reporting and Analytics: Enables DBAs or developers to query current data without interfering with the mirroring process.
• Historical Analysis: Offers point-in-time copies to analyze changes over time.
• How It Works:
  Snapshots capture the actual data pages at a moment in time, stored separately from the live mirrored database.

Standby Mode

In standby mode, the mirror database is continuously updated yet remains inaccessible to clients. Database snapshots allow read operations on a stable, historical view of the data without disrupting ongoing mirroring.

Conclusion

SQL Server mirroring is a powerful solution for ensuring high availability and data integrity. By using dedicated TCP endpoints and choosing between synchronous (high-safety) and asynchronous (high-performance) modes, organizations can minimize downtime and data loss. Whether configured via T‑SQL or the SSMS GUI, mirroring — with components such as the principal, mirror, and optional witness servers — ensures continuous data protection. Additionally, database snapshots enable access to read-only data for reporting and analysis, even while the mirrored database remains in standby mode.

1. Understanding Backup in MSSQL

Backup in MSSQL is essential for disaster recovery, minimizing downtime and data loss. Two key recovery objectives define an effective backup strategy:

• Recovery Time Objective (RTO): Maximum downtime allowed after a disaster.
• Recovery Point Objective (RPO): Maximum acceptable data loss in case of failure.

2. Types of Backups in MSSQL

2.1 Recovery Models

MSSQL offers three recovery models that determine how backups are managed:

• Simple Recovery Model — No log backups; minimal storage usage.
• Full Recovery Model — Requires log backups; ensures point-in-time recovery.
• Bulk-Logged Recovery Model — Reduces log size for bulk operations but still allows point-in-time recovery.

2.2 Backup Types

MSSQL provides different types of backups for various scenarios:

• Full Backup — Backs up the entire database, including logs.
• Differential Backup — Captures only changes made since the last full backup.
• Transaction Log (T-Log) Backup — Backs up transaction logs for point-in-time recovery.
• Tail-Log Backup — Captures the active portion of the transaction log before restoring or recovering a database to prevent data loss.

2.3 Backup File Formats

• .bak → Used for full and differential database backups.
• .trn → Used for transaction log backups.
• .diff → Sometimes used for differential backups.

2.4 Backup Strategy Example

• Full Backup — Daily or Weekly (as per RPO)
• Differential Backup — 4hours or Daily (as per RPO)
• T-Log Backup — Every 10–15 minutes (as per RPO)

3. Example Scenario: Backup Schedule in a Day

Consider a real-time System with frequent transactions.

A well-structured backup schedule ensures minimal data loss and efficient recovery. Below is an example of a database backup plan throughout a working day:

This backup schedule ensures:

• Full backup provides a complete recovery point.
• Differential backups track changes after the full backup, reducing restore time.
• T-Log backups allow point-in-time recovery in case of failure.

4. Ways to Implement Backup and Restore

4.1 Using T-SQL

Setting Recovery Model

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabase';
ALTER DATABASE YourDatabase SET RECOVERY FULL;
Performing Backups

Performing Backups

Full Backup (Overwrite existing backup)

BACKUP DATABASE YourDatabase 
TO DISK = 'C:\Backups\YourDatabase_Full.bak' 
WITH INIT, COMPRESSION, STATS = 10;

Differential Backup

BACKUP DATABASE YourDatabase 
TO DISK = 'C:\Backups\YourDatabase_Diff.bak' 
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

T-Log Backup

BACKUP LOG YourDatabase  
TO DISK = 'C:\Backups\YourDatabase_TailLog.trn'  
WITH NORECOVERY, STATS = 10;

Performing a Tail-Log Backup

A Tail-Log Backup is used when the database is damaged, but you need to back up the transaction log before performing a restore. This ensures no transactions are lost.

BACKUP LOG YourDatabase  
TO DISK = 'C:\Backups\YourDatabase_TailLog.trn'  
WITH NORECOVERY, STATS = 10;

Restoring Backups

Restore Full Backup

RESTORE DATABASE YourDatabase 
FROM DISK = 'C:\Backups\YourDatabase_Full.bak' 
WITH NORECOVERY, STATS = 10;

Restore Differential Backup

RESTORE DATABASE YourDatabase 
FROM DISK = 'C:\Backups\YourDatabase_Diff.bak' 
WITH RECOVERY, STATS = 10;

Restore T-Log Backup

RESTORE LOG YourDatabase 
FROM DISK = 'C:\Backups\YourDatabase_Log.trn' 
WITH STOPAT = '2024-02-27 15:30:00', RECOVERY, STATS = 10;

Restore Tail-Log Backup Before Recovery

RESTORE LOG YourDatabase  
FROM DISK = 'C:\Backups\YourDatabase_TailLog.trn'  
WITH NORECOVERY, STATS = 10;

4.2 Backup Using GUI (SQL Server Management Studio — SSMS)

1. Open SSMS and connect to the SQL instance.
2. Right-click the database → Select Tasks → Back Up…
3. Choose Backup Type (Full, Differential, or Transaction Log).
4. Specify the destination path.
5. Click OK to start the backup.

4.3 Automating Backups with SQL Server Agent (Maintenance Plan)

• Using SQL Server Agent

1. Ensure SQL Server Agent is running.
2. In SSMS, navigate to SQL Server Agent → Jobs.
3. Right-click and select New Job → Configure the backup command.

• Using Maintenance Plans

1. Go to Management → Maintenance Plans in SSMS.
2. Create a New Maintenance Plan.
3. Add a Back Up Database Task and configure it with schedule settings.

5. Copy-Only Backup (Non-Disruptive Backup)

A Copy-Only Backup creates a backup without affecting the existing backup chain.

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backups\YourDatabase_CopyOnly.bak'
WITH COPY_ONLY, COMPRESSION, STATS = 10;

Conclusion

Understanding RTO, RPO, and backup strategies is critical for database recovery. By implementing the right combination of full, differential, T-log, and tail-log backups, along with automated maintenance plans, you can minimize downtime and data loss effectively in MSSQL.

Before setting up Log Shipping, it’s important to understand the key backup and restore concepts:

• Full Backup: A complete backup of the database, used as the baseline for recovery.
• Transaction Log Backup (T-Log Backup): Captures all transactions that have occurred since the last transaction log backup.
• Tail-Log Backup: Ensures that all uncommitted transactions are captured before a restore operation.

Why Log Shipping?

Database availability is crucial for business continuity. If a disaster occurs (e.g., hardware failure, software crash, or data corruption), you need a reliable disaster recovery strategy. Two common methods to achieve this are Log Shipping and Mirroring. In this article, we will focus on Log Shipping.

What is Log Shipping?

Log Shipping is a disaster recovery technique that periodically backs up the transaction logs on a primary server (Active Mode) and transfers them to a secondary server (Standby Mode) as per a predefined schedule.

How Log Shipping Works

Log Shipping operates in the following phases:

1. Regular Backups

• The primary server takes transaction log backups at scheduled intervals.
• These backups contain all committed transactions since the last backup.

2. Transfer of Transaction Log Backups

• The backup files are automatically copied from the primary server to the secondary server over the network.

3. Restoration on the Secondary Server

• The secondary server restores these transaction log backups in sequence.
• The database on the secondary server is kept in sync with the primary server.
• The database can be in Standby Mode (allowing read-only access) or No Recovery Mode (for faster failover readiness).

4. Failover Process

• If the primary server goes offline, the secondary server can take over.
• Failover is a manual process in Log Shipping (unlike automatic failover in Mirroring or AlwaysOn Availability Groups).
• In Standby Mode, the secondary server can be used for read-only operations before a full failover occurs.

Ways to Implement Log Shipping

There are two ways of implementing Log Shipping, let’s see in this section.

1.Manual:

Take backups manually and store them in a shared folder or a server using a domain account.

Step 1: Perform a Full Backup on the Primary Server

— -Take a full database backup on the primary server 
BACKUP DATABASE [YourDatabase] 
TO DISK = ‘\\SharedFolder\YourDatabase_Full.bak’ 
WITH FORMAT, INIT, NAME = ‘Full Backup of YourDatabase’;

Step 2: Take a Transaction Log Backup on the Primary Server

— Take a transaction log backup on the primary server 
BACKUP LOG [YourDatabase] 
TO DISK = ‘\\SharedFolder\YourDatabase_TLog.trn’ 
WITH INIT, NAME = ‘T-Log Backup of YourDatabase’;

Step 3: Manually Transfer the Backup Files

After taking the backups, manually copy the .bak and .trn files from the primary server to a shared folder or the secondary server.

Step 4: Restore the Full Backup on the Secondary Server

— Restore the full database backup on the secondary server in NORECOVERY mode 
RESTORE DATABASE [YourDatabase] 
FROM DISK = ‘\\SharedFolder\YourDatabase_Full.bak’ 
WITH NORECOVERY, REPLACE;

Step 5: Restore the Transaction Log Backup on the Secondary Server

— Restore the transaction log backup on the secondary server in NORECOVERY mode 
RESTORE LOG [YourDatabase] 
FROM DISK = ‘\\SharedFolder\YourDatabase_TLog.trn’ 
WITH NORECOVERY;

Automating Log Shipping Using SQL Server Agent Jobs

Instead of manually running the backups and restores, we can schedule SQL Server Agent jobs to automate the process:

1. On the Primary Server:
2. Create a SQL Server Agent job that runs Step 2’s transaction log backup query.
3. Schedule it to run every 15 minutes.

On the Secondary Server:

1. Create a SQL Server Agent job that runs Step 5’s log restore query.
2. Schedule it to run every 15 minutes.

Failover Process

In case of primary server failure, restore the secondary database to full operational mode using:

— Bring the secondary database online after failover 
RESTORE DATABASE [YourDatabase] WITH RECOVERY;

2.Inbuilt Method (GUI):

Use the built-in Log Shipping wizard in SQL Server to automate the backup, copy, and restore process.

Step 1: Open Log Shipping Wizard

• Right-click on the primary database in SQL Server Management Studio (SSMS).
• Navigate to Tasks → Ship Transaction Logs.

Step 2: Enable Log Shipping for the Primary Database

• Check the box “Enable this as a primary database in log shipping configuration.”

Step 3: Configure the Backup Settings

• Click Backup Settings and define the backup schedule for transaction log backups.
• Choose a network path or shared folder where backups will be stored.
• Set the retention period for old transaction log backups.

Step 4: Add a Secondary Server Instance

• Click Add to specify the secondary SQL Server instance.
• Connect to the instance (ensure it’s under a domain account or a second instance on the same machine).

Step 5: Choose Database Initialization Method

On the secondary server, choose one of the following options for initializing the database:

• (A) Create a new database: SQL Server will automatically take a full backup and restore it on the secondary server.
• (B) Use an existing full backup: If a full backup already exists on the secondary server, specify its location.
• (C)Database is already initialized: If the database has been restored manually using NORECOVERY or STANDBY mode, specify the location of the database files.

Step 6: Configure Transaction Log Destination

• Set up the destination folder where transaction log backups will be copied and restored on the secondary server.

Step 7: Choose Database Recovery Mode

• No Recovery Mode: The secondary database remains non-accessible and is always in restoring mode.
• Standby Mode: The secondary database allows read-only access for reporting.
• If using Standby Mode, switch to Master Database and refresh SSMS after configuration.
• This prevents issues with automatic log restoration

Advantages of Log Shipping

• Simple to configure and maintain.
• Supports multiple secondary servers.
• Allows read-only access to the standby database (useful for reporting).
• Low overhead on the primary server.
• Useful for disaster recovery and migration scenarios.

Limitations of Log Shipping

• No automatic failover — Requires manual intervention.
• Potential data loss — Transactions since the last log backup may be lost.
• Latency — The secondary server is not real-time; it depends on the log shipping frequency.
• Additional storage requirement — Backup files need to be stored before transfer.

Conclusion

Log Shipping is an effective database level disaster recovery solution in SQL Server, providing an easy-to-implement method for maintaining a standby server. While it lacks automatic failover, it offers a cost-effective and simple way to ensure high availability and data redundancy.

By understanding its prerequisites, working mechanism, and advantages, organizations can leverage Log Shipping to enhance their SQL Server disaster recovery strategy.