SQL Server: How to Partition a Large Table in a 2-Node Always On AG with Minimal Downtime

Scenario Overview

You have a large production table — in this example approximately 350 GB and 3.4 million rows — that needs to be partitioned by date. The database runs in a 2-node synchronous Always On Availability Group with automatic failover. All application traffic connects through the AG Listener.

The challenge is that partitioning a 350 GB table requires rebuilding the clustered index, dropping and recreating the primary key, and adding nonclustered indexes — all of which are destructive, I/O-intensive operations that take 4–9 hours. Running these operations on the primary node while users are connected is technically possible but practically very damaging.

Approach Comparison — Why AG Failover Is Recommended

Two approaches were evaluated. Option A (run directly on the primary) was initially proposed. Option B (AG Failover method) is the recommended approach after analysis.

Impact Summary

Environment Reference

The examples throughout this article use the following example names. Substitute your actual values:

Step 1a: Confirm AG Health

Both replicas must show SYNCHRONIZED and CONNECTED before proceeding. Do not start the maintenance window on a degraded AG.

-- Run on either node
SELECT
    ag.name                          AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.synchronization_health_desc,
    ars.connected_state_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar
    ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag
    ON ar.group_id = ag.group_id;

Step 1b: Confirm Node Roles

-- Confirm before starting:
-- Node A (PROD-DBNODE1) = PRIMARY
-- Node B (PROD-DBNODE2) = SECONDARY
-- AG Name: PROD-AG01
-- Listener: PROD-AGLISTEN (port 1433)

SELECT replica_server_name, role_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar
    ON ars.replica_id = ar.replica_id;

Step 1c: Full Backup

-- Run on PROD-DBNODE1 (current primary)
BACKUP DATABASE [AppDatabase]
TO DISK = N'\\backup-share\AppDatabase\AppDatabase_PrePartition_FULL.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

Step 1d: Disable Transaction Log Backups

-- Identify and disable log backup jobs that include AppDatabase
-- Record job names here before disabling:
-- Job Name: ____________________________

-- To disable a job:
EXEC msdb.dbo.sp_update_job
    @job_name = N'YourLogBackupJobName',
    @enabled  = 0;

Step 1e: Record Row Count

-- Record exact count — must match post-partition total in Step 8
SELECT COUNT(*) AS total_rows FROM dbo.TransactionDetail;

Step 2a: Execute Failover

-- Run on PROD-DBNODE2 (the current secondary)
ALTER AVAILABILITY GROUP [PROD-AG01] FAILOVER;

Step 2b: Verify Roles Have Swapped

SELECT replica_server_name, role_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar
    ON ars.replica_id = ar.replica_id;

Step 2c: Confirm App Connectivity Through Listener

Verify the application is connected and functioning through PROD-AGLISTEN on PROD-DBNODE2 before proceeding to Step 3.

Step 3a: Remove AppDatabase from AG on Node A

-- Run on PROD-DBNODE1 (now the secondary)
ALTER DATABASE [AppDatabase] SET HADR OFF;

This only removes AppDatabase from the AG on this node. The other databases (DB1, DB2, DB3, DB4) remain in the AG and remain fully protected.

Step 3b: Recover the Database on Node A

-- The database will be in RESTORING state after removal — recover it
-- Run on PROD-DBNODE1
RESTORE DATABASE [AppDatabase] WITH RECOVERY;

-- Run on PROD-DBNODE1
SELECT name, state_desc FROM sys.databases WHERE name = 'AppDatabase';

Execute all partition phases on PROD-DBNODE1. Because no application connections exist on this node, all operations run at full I/O speed with no contention, no blocking, and no lock escalation concerns.

Phase 1: Create Partition Function and Scheme

-- Run on PROD-DBNODE1 (isolated)
-- Adjust boundary dates to match your retention requirements
CREATE PARTITION FUNCTION pf_TransDetail_Date (DATE)
AS RANGE RIGHT FOR VALUES
(
    '2020-01-01', '2021-01-01', '2022-01-01',
    '2023-01-01', '2024-01-01', '2025-01-01',
    '2026-01-01', '2027-01-01'
);

CREATE PARTITION SCHEME ps_TransDetail_Date
AS PARTITION pf_TransDetail_Date
ALL TO ([PRIMARY]);  -- Adjust to target filegroups as needed

Phase 2: Rebuild Clustered Index with Partition Scheme

-- Drop existing clustered index and rebuild on partition scheme
-- This is the longest-running operation (3–6 hours on 350 GB)
-- No users on this node — runs at full speed

CREATE CLUSTERED INDEX CIX_TransactionDetail_Date
ON dbo.TransactionDetail (TransactionDate, TransactionID)
WITH (DROP_EXISTING = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON)
ON ps_TransDetail_Date (TransactionDate);

Phase 3: Rebuild Primary Key Partition-Aligned

-- Drop the existing PK (non-partitioned)
ALTER TABLE dbo.TransactionDetail
DROP CONSTRAINT PK_TransactionDetail;

-- Recreate PK partition-aligned
ALTER TABLE dbo.TransactionDetail
ADD CONSTRAINT PK_TransactionDetail
PRIMARY KEY CLUSTERED (TransactionDate, TransactionID)
ON ps_TransDetail_Date (TransactionDate);

-- Rebuild any nonclustered indexes partition-aligned
CREATE NONCLUSTERED INDEX IX_TransactionDetail_CustomerID
ON dbo.TransactionDetail (CustomerID)
INCLUDE (TransactionDate, Amount)
ON ps_TransDetail_Date (TransactionDate);

Phase 4: Validate Partitions

-- Confirm 12 partitions listed
SELECT
    p.partition_number,
    p.rows,
    prv.value AS boundary_value
FROM sys.partitions p
JOIN sys.tables t ON p.object_id = t.object_id
LEFT JOIN sys.partition_range_values prv
    ON prv.boundary_id = p.partition_number - 1
WHERE t.name = 'TransactionDetail'
AND p.index_id IN (0, 1)
ORDER BY p.partition_number;

Phase 5: Archive Setup and SWITCH

-- Create archive table (must match schema exactly)
CREATE TABLE dbo.TransactionDetail_Archive
(
    TransactionID   INT          NOT NULL,
    TransactionDate DATE         NOT NULL,
    CustomerID      INT          NULL,
    Amount          DECIMAL(18,2) NULL,
    -- match all columns from main table
    CONSTRAINT PK_TransactionDetail_Archive
        PRIMARY KEY CLUSTERED (TransactionDate, TransactionID)
        ON [PRIMARY],
    CONSTRAINT CK_Archive_Date
        CHECK (TransactionDate < '2023-01-01')
) ON [PRIMARY];

-- Switch old partitions to archive table
-- Partition 1 contains data before 2020, Partition 2 = 2020, etc.
ALTER TABLE dbo.TransactionDetail
SWITCH PARTITION 1 TO dbo.TransactionDetail_Archive;

ALTER TABLE dbo.TransactionDetail
SWITCH PARTITION 2 TO dbo.TransactionDetail_Archive;

-- SQL Server 2016+ syntax — purge partitions 1 and 2 directly
-- Minimal logging, no archive table required, no SWITCH prerequisites
TRUNCATE TABLE dbo.TransactionDetail WITH (PARTITIONS (1, 2));

Step 5a: Full Backup

-- Run on PROD-DBNODE1 (isolated, partitioned database)
BACKUP DATABASE [AppDatabase]
TO DISK = N'\\backup-share\AppDatabase\AppDatabase_Partitioned_FULL.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

Step 5b: Log Backup

-- Run on PROD-DBNODE1
BACKUP LOG [AppDatabase]
TO DISK = N'\\backup-share\AppDatabase\AppDatabase_Partitioned_LOG.trn'
WITH COMPRESSION, CHECKSUM, STATS = 5;

Step 6a: Remove AppDatabase from AG on Node B

-- Run on PROD-DBNODE2 (current primary)
-- Node B still has the OLD unpartitioned AppDatabase in the AG
ALTER AVAILABILITY GROUP [PROD-AG01] REMOVE DATABASE [AppDatabase];

AppDatabase is now out of the AG on both nodes. The other databases are unaffected. The AG Listener will no longer route AppDatabase connections until it is re-added in Step 6d.

Step 6b: Drop Old AppDatabase on Node B

-- Run on PROD-DBNODE2
-- This drops the old unpartitioned copy — confirmed it was removed from AG in 6a first
DROP DATABASE [AppDatabase];

Step 6c: Fail Over AG to Node A

-- Run on PROD-DBNODE1 (the node with the partitioned database)
ALTER AVAILABILITY GROUP [PROD-AG01] FAILOVER;

Step 6d: Add AppDatabase Back to AG from Node A

-- Run on PROD-DBNODE1 (now primary, has the partitioned AppDatabase)
ALTER AVAILABILITY GROUP [PROD-AG01] ADD DATABASE [AppDatabase];

-- Confirm AG roles
SELECT replica_server_name, role_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id;

-- Confirm AppDatabase synchronization state
SELECT database_name, synchronization_state_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_databases_cluster adc
    ON drs.group_database_id = adc.group_database_id;

Step 6e: Confirm App Connectivity Through Listener

Verify the application is connected and functioning through PROD-AGLISTEN, now pointing to PROD-DBNODE1 with the partitioned database.

Two options to reseed PROD-DBNODE2. Automatic seeding is simpler. Manual seeding (backup/restore) gives more control and is required if automatic seeding is not configured.

Option 1: Automatic Seeding (Recommended)

-- Step 7a: Verify automatic seeding is configured on Node A (primary)
SELECT
    ar.replica_server_name,
    ar.seeding_mode_desc
FROM sys.availability_replicas ar
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
WHERE ag.name = 'PROD-AG01';

-- If seeding_mode_desc shows MANUAL, change it to AUTOMATIC:
ALTER AVAILABILITY GROUP [PROD-AG01]
MODIFY REPLICA ON N'PROD-DBNODE2'
WITH (SEEDING_MODE = AUTOMATIC);

-- Step 7b: Grant CREATE DATABASE permission on Node B
-- Run on PROD-DBNODE2
ALTER AVAILABILITY GROUP [PROD-AG01] GRANT CREATE ANY DATABASE;

-- Monitor seeding progress from Node A
SELECT
    ag.name                  AS ag_name,
    drs.database_name,
    drs.current_state,
    drs.performed_seeding,
    drs.start_time,
    drs.completion_time,
    drs.failure_state_desc
FROM sys.dm_hadr_automatic_seeding drs
JOIN sys.availability_groups ag ON drs.ag_id = ag.group_id
WHERE drs.database_name = 'AppDatabase';

Option 2: Manual Seeding (Backup/Restore)

Use this if automatic seeding is not available or preferred. Log backup jobs for AppDatabase should already be disabled from Step 1d.

-- Run on PROD-DBNODE1 (primary)
BACKUP DATABASE [AppDatabase]
TO DISK = N'\\backup-share\AppDatabase\AppDatabase_Partitioned_Reseed_FULL.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

BACKUP LOG [AppDatabase]
TO DISK = N'\\backup-share\AppDatabase\AppDatabase_Partitioned_Reseed_LOG.trn'
WITH COMPRESSION, CHECKSUM, STATS = 5;

-- Run on PROD-DBNODE2
RESTORE DATABASE [AppDatabase]
FROM DISK = N'\\backup-share\AppDatabase\AppDatabase_Partitioned_Reseed_FULL.bak'
WITH NORECOVERY, STATS = 5;

RESTORE LOG [AppDatabase]
FROM DISK = N'\\backup-share\AppDatabase\AppDatabase_Partitioned_Reseed_LOG.trn'
WITH NORECOVERY, STATS = 5;

-- Join to AG on Node B
ALTER DATABASE [AppDatabase]
SET HADR AVAILABILITY GROUP = [PROD-AG01];

Step 7c: Re-enable Transaction Log Backups

EXEC msdb.dbo.sp_update_job
    @job_name = N'YourLogBackupJobName',
    @enabled  = 1;

SELECT
    ar.replica_server_name,
    ars.role_desc,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id
WHERE drs.database_id = DB_ID('AppDatabase');

Step 8a: Verify Partitions on Both Nodes

-- Run on BOTH PROD-DBNODE1 and PROD-DBNODE2
SELECT
    p.partition_number,
    p.rows,
    prv.value AS boundary_value
FROM sys.partitions p
JOIN sys.tables t ON p.object_id = t.object_id
LEFT JOIN sys.partition_range_values prv
    ON prv.boundary_id = p.partition_number - 1
WHERE t.name = 'TransactionDetail'
AND p.index_id IN (0, 1)
ORDER BY p.partition_number;

Step 8b: Verify Total Row Count Matches

SELECT
    (SELECT COUNT(*) FROM dbo.TransactionDetail)
    + (SELECT COUNT(*) FROM dbo.TransactionDetail_Archive)
    AS total_rows;
-- Must match pre-partition count recorded in Step 1e

Step 8c: Verify All Indexes Are Partition-Aligned

SELECT
    i.name            AS index_name,
    ds.name           AS data_space_name,
    ds.type_desc
FROM sys.indexes i
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('dbo.TransactionDetail')
ORDER BY i.index_id;
-- Every index should show ps_TransDetail_Date / PARTITION_SCHEME

Step 8d: Verify SQL Agent Maintenance Job on Both Nodes

SELECT name, enabled, date_created
FROM msdb.dbo.sysjobs
WHERE name = 'AppDatabase_TransDetail_Partition_Maintenance';
-- Note: if job only exists on Node A, run Phase 6 from the partition runbook on Node B

Step 8e: Application Smoke Test

Confirm the application is functioning correctly through the AG Listener against the partitioned database. Run representative queries and confirm expected response times.

Rollback Plan

Partition Work Phases Reference

All phases run on the isolated Node A with no application connections. Estimated total duration: 4–9 hours depending on hardware and data volume.

References

• Microsoft Docs – Always On Availability Groups Overview
• Microsoft Docs – Planned Manual Failover of an Availability Group
• Microsoft Docs – Partitioned Tables and Indexes
• Microsoft Docs – ALTER TABLE SWITCH
• Microsoft Docs – Automatic Seeding for Secondary Replicas
• Microsoft Docs – ALTER AVAILABILITY GROUP