Series note: This is Part 9 of the 12-part SharePoint 2019 to Subscription Edition Migration series. By this point your SPSE farm is live, content databases have been migrated, and log shipping used during the migration window (Post #6) has been decommissioned. Now it is time to bolt on the permanent high-availability layer.

Next: Post 10 — Search Architecture in SPSE →

A SharePoint farm without a SQL Server Availability Group has no automatic failover. When the primary SQL instance goes down — hardware failure, OS patch reboot, storage hiccup — the farm goes down with it and stays down until a DBA manually intervenes. For most production SPSE deployments, that is not an acceptable risk posture.

SQL Server Always On Availability Groups (AGs) are the correct answer to that problem, and for SharePoint Subscription Edition they are not one option among several — they are the only Microsoft-supported high-availability path for content databases. Database mirroring was deprecated in SQL Server 2012 and removed in SQL Server 2022. Log shipping is a useful migration tool (see Post #6) but it is not an HA mechanism. If you are running SPSE on SQL Server 2019 or 2022, an AG with an AG listener is what Microsoft supports and what this post covers.

The challenge is not understanding what an AG is. It is executing the unforgiving seeding sequence correctly — full backup, log backup, restore WITH NORECOVERY on the secondary, log restore WITH NORECOVERY, join on primary, join on secondary, verify SYNCHRONIZED — and doing it across every content database in the farm without error. A 50-database farm means 50 repetitions of that sequence. Done manually, that is a day of careful T-SQL. Done wrong once, the AG join fails and you are left with an out-of-sync secondary until you diagnose and retry.

This post walks through every prerequisite, the complete join sequence in T-SQL, the synchronous vs asynchronous mode decision, and the two PowerShell scripts that automate the entire operation.

Why SQL Server Availability Groups for SharePoint Subscription Edition

The only supported HA path

Microsoft’s SharePoint HA documentation is clear: Always On Availability Groups are the supported high-availability mechanism for SharePoint Subscription Edition content databases. Database mirroring is gone — sys.database_mirroring is removed in SQL Server 2022, and even on SQL 2019 it is deprecated and unsupported for new SharePoint deployments. Log shipping can replicate data to a standby server, but it requires manual intervention to fail over and leaves the secondary in a read-only restoring state; it is not automatic HA.

AGs give you what the alternatives cannot:

• Automatic failover to a synchronized secondary replica when Windows Server Failover Cluster detects the primary is unhealthy
• Database-level health detection — the cluster detects unhealthy databases, not just unhealthy servers
• AG listener — a virtual network name and IP that always resolves to the current primary; SharePoint connects to the listener, not to a named SQL instance, so failover is transparent to the application layer
• Readable secondary replicas for backup offload and reporting without impacting the production primary
• Zero data loss in synchronous commit mode — the primary does not acknowledge a transaction commit until the secondary has hardened it to disk

Where this fits in the migration series

At Post #9, the SPSE farm is fully operational. The source SharePoint 2019 farm has been decommissioned or is in read-only mode. The SQL log shipping jobs that kept the migration window tight have been torn down. What remains is the operational gap: the farm is live but not protected by automatic failover. This post closes that gap. Post #10 will cover the search service application and its databases, which have specific AG considerations — search component databases must be handled differently from content databases.

Prerequisites — What Must Be in Place Before You Add SharePoint Databases to an AG

This checklist catches the failures before they happen. Work through every item before issuing any T-SQL.

Infrastructure prerequisites

• ☐ Windows Server Failover Cluster (WSFC) is configured and healthy. Both SQL nodes must be cluster members. Verify with Get-ClusterNode — all nodes should show Up.
• ☐ SQL Server Enterprise Edition on all replicas. Always On AGs require Enterprise Edition on SQL Server 2019. (SQL Server 2022 introduced limited AG functionality in Standard Edition, but SharePoint’s production HA requirements still assume Enterprise.)
• ☐ The Availability Group already exists with a listener configured. This post covers adding databases to an existing AG — not creating the AG from scratch. The AG name and listener DNS name should be known and documented before you start.
• ☐ AG listener is reachable from all SharePoint servers on port 1433. Test with:

Test-NetConnection -ComputerName SPSE-AG-Listener -Port 1433

TcpTestSucceeded : True is the expected result from every SharePoint server in the farm.

Database-level prerequisites

• ☐ All SharePoint content databases are in FULL recovery model. This is the single most common prerequisite failure. Simple or Bulk-Logged recovery breaks the log chain and prevents AG seeding entirely. Verify with:

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name LIKE 'WSS_%' OR name LIKE 'SP_%'
ORDER BY name;

Every row must show FULL. If any show SIMPLE or BULK_LOGGED, correct them before proceeding:

ALTER DATABASE [WSS_Content_Intranet] SET RECOVERY FULL;
-- Repeat for each affected database

After changing recovery model, take a full backup immediately to start a clean log chain.

• ☐ No active log shipping jobs targeting the same databases. Log shipping and AG are mutually exclusive at the database level. Confirm all post-migration log shipping jobs have been removed.
• ☐ Databases are online on the primary and not in read-only or restoring state.

Permissions prerequisites

• ☐ Primary SQL Server service account has read/write access to the backup share (the UNC path used during seeding).
• ☐ Secondary SQL Server service account has read access to the same backup share.
• ☐ Operator account has sysadmin or the appropriate AG management role on both primary and secondary SQL instances.
• ☐ SharePoint farm service account has db_owner on all content databases. Verify this before and after the AG join — the join operation itself does not change permissions, but it is a good checkpoint.

SharePoint-specific checks

• ☐ AG listener DNS name resolves from all SharePoint servers. Test with Resolve-DnsName SPSE-AG-Listener from a SharePoint WFE and from the Central Administration server.
• ☐ SharePoint databases are not referenced by their SQL instance name in any hardcoded connection strings in web.config files — those will break after the listener update in the post-setup step.

The Seeding Sequence — How SharePoint Databases Join an AG

Why the sequence is non-negotiable

AG join has a strict dependency chain. The database must already exist on the secondary in NORECOVERY (restoring) state before the primary can issue the ALTER AVAILABILITY GROUP ... ADD DATABASE command. The backup used for seeding must have been taken after the database was set to FULL recovery — any backup taken before the recovery model change is unusable because the log chain is broken. Attempt the AG join before the secondary restore is complete and SQL Server returns an error. Skip the log backup restore and the LSN chain is broken.

The sequence is:

1. Full backup on primary
2. Log backup on primary (immediately after the full backup)
3. Restore full backup on secondary WITH NORECOVERY
4. Restore log backup on secondary WITH NORECOVERY
5. Join database to AG on the primary
6. Join database to AG on the secondary
7. Verify SYNCHRONIZED state

The T-SQL join sequence

The following block shows the complete seeding sequence for a single content database. In practice you will run this for every content database — which is exactly what Add-DatabasesToAG.ps1 automates.

-- ============================================================
-- Step 1: Full backup on the PRIMARY
-- ============================================================
BACKUP DATABASE [WSS_Content_Intranet]
TO DISK = N'\\SPSE-BACKUPS\AGSeed\WSS_Content_Intranet.bak'
WITH INIT, FORMAT, COMPRESSION, STATS = 10;
-- ============================================================
-- Step 2: Log backup on the PRIMARY (immediately after full)
-- Ensures log chain continuity from the seeding point forward
-- ============================================================
BACKUP LOG [WSS_Content_Intranet]
TO DISK = N'\\SPSE-BACKUPS\AGSeed\WSS_Content_Intranet.trn'
WITH INIT, FORMAT, COMPRESSION;
-- ============================================================
-- Step 3: Restore full backup on the SECONDARY (NORECOVERY)
-- Run this on the secondary SQL instance
-- ============================================================
RESTORE DATABASE [WSS_Content_Intranet]
FROM DISK = N'\\SPSE-BACKUPS\AGSeed\WSS_Content_Intranet.bak'
WITH NORECOVERY, STATS = 5;
-- ============================================================
-- Step 4: Restore log backup on the SECONDARY (NORECOVERY)
-- Advances the LSN to match the primary seeding point
-- ============================================================
RESTORE LOG [WSS_Content_Intranet]
FROM DISK = N'\\SPSE-BACKUPS\AGSeed\WSS_Content_Intranet.trn'
WITH NORECOVERY;
-- ============================================================
-- Step 5: Join the database to the AG on the PRIMARY
-- ============================================================
ALTER AVAILABILITY GROUP [SPSE-AG]
ADD DATABASE [WSS_Content_Intranet];
-- ============================================================
-- Step 6: Join the database to the AG on the SECONDARY
-- Run this on the secondary SQL instance
-- ============================================================
ALTER DATABASE [WSS_Content_Intranet]
SET HADR AVAILABILITY GROUP = [SPSE-AG];
-- ============================================================
-- Step 7: Verify sync state (run on primary or secondary)
-- Synchronous commit target: SYNCHRONIZED
-- Asynchronous commit target: SYNCHRONIZING (expected, not an error)
-- ============================================================
SELECT
    db.name,
    rs.synchronization_state_desc,
    rs.synchronization_health_desc,
    rs.log_send_queue_size,
    rs.redo_queue_size
FROM sys.dm_hadr_database_replica_states rs
JOIN sys.databases db ON rs.database_id = db.database_id
WHERE db.name = 'WSS_Content_Intranet';

Do not proceed to the next database until the current database shows SYNCHRONIZED (synchronous) or a stable SYNCHRONIZING with a draining redo_queue_size (asynchronous).

Synchronous vs Asynchronous Commit — Which Mode to Use for SharePoint

The choice of commit mode directly affects SharePoint write performance and your RPO/RTO posture. The table below covers the scenarios relevant to SPSE deployments.

SharePoint-specific guidance: Synchronous commit is the right default for any single-datacenter SPSE farm. The commit latency overhead on a modern 1GbE or 10GbE datacenter network is measured in sub-millisecond increments — it is not perceptible in SharePoint page load times under normal conditions. If you observe SharePoint response time degradation after adding the AG, diagnose secondary replica round-trip latency before investigating SharePoint configuration. A slow secondary disk or a congested replication network is the most common culprit.

Automating the Seeding Sequence with Add-DatabasesToAG.ps1

Running the seven-step T-SQL sequence manually for one database is tedious. For 50 databases it is error-prone and takes a full working day. Add-DatabasesToAG.ps1 automates the entire primary-side seeding loop.

What the script does

The script discovers all SharePoint content databases (via Get-SPContentDatabase or a supplied CSV list), then for each database executes the complete seeding workflow: full backup, log backup, invokes the secondary-side restore (coordinated with Add-SecondaryDatabases.ps1), issues the AG join DDL on the primary, then polls sys.dm_hadr_database_replica_states until the database reaches the target sync state before moving to the next database.

All T-SQL operations go through Invoke-Sqlcmd — no SSMS, no manual steps, no clipboard errors.

Databases that fail the join are logged to a FailedDatabases.csv file and the script continues with the remaining databases in the batch. The CSV gives you a clean retry list without re-running the entire farm.

The script is idempotent: databases already joined to the AG are detected via sys.availability_databases_cluster and skipped automatically. Re-running after a partial failure picks up exactly where it left off.

Key parameters

.\Add-DatabasesToAG.ps1 `
    -AGName         "SPSE-AG" `
    -ListenerName   "SPSE-AG-Listener" `
    -BackupShare    "\\SPSE-BACKUPS\AGSeed" `
    -SyncMode       "Synchronous_Commit" `
    -DatabaseList   "C:\Migration\ContentDatabases.csv" `
    -SecondarySQL   "SPSE-SQL2" `
    -Verbose

Why it matters at scale

A typical enterprise SPSE farm has 20–80 content databases. At five minutes per database (backup + restore + join + sync wait for a mid-size content database), that is 100–400 minutes of uninterrupted manual T-SQL if done by hand. Add-DatabasesToAG.ps1 runs the same loop unattended, logging progress per database, and completes a 50-database farm in 2–4 hours depending on database sizes and network throughput — with zero manual intervention after the initial parameter review.

Preparing the Secondary Replica with Add-SecondaryDatabases.ps1

RESTORE WITH NORECOVERY must be issued against the secondary SQL instance — it cannot be driven from the primary side. This is why the secondary requires its own script.

What the script does

Add-SecondaryDatabases.ps1 runs on or is invoked against the secondary SQL replica. For each database in the batch it:

1. Restores the full backup from the shared backup path WITH NORECOVERY
2. Immediately restores the corresponding log backup WITH NORECOVERY to advance the LSN to the seeding point
3. Validates the database is in NORECOVERY (restoring) state using sys.databases
4. Signals readiness to the primary-side script so the ALTER AVAILABILITY GROUP ... ADD DATABASE command proceeds only after the secondary restore is confirmed complete

.\Add-SecondaryDatabases.ps1 `
    -BackupShare    "\\SPSE-BACKUPS\AGSeed" `
    -DatabaseList   "C:\Migration\ContentDatabases.csv" `
    -AGName         "SPSE-AG" `
    -Verbose

Why the secondary side needs its own script

The coordination between primary-side join and secondary-side restore is the most failure-prone part of manual AG seeding. If the primary issues ALTER AVAILABILITY GROUP ... ADD DATABASE while the secondary restore is still running — or hasn’t started yet — the join fails. Add-SecondaryDatabases.ps1 provides the checkpoint: Add-DatabasesToAG.ps1 waits for a success signal from the secondary script before issuing each join command.

Common failure modes the script detects:

• Backup file not accessible from the secondary — logged per database; the primary-side script marks the database as failed and continues
• Database already exists on the secondary in a RECOVERY (online) state — detected and reported; an operator must decide whether to drop and re-seed or investigate why a previous join attempt left the database online
• LSN mismatch between full backup and log backup — caught via RESTORE HEADERONLY before the restore begins, preventing a cryptic LSN error mid-restore

Common Issues and How to Diagnose Them

Database not in FULL recovery model

Symptom: BACKUP LOG cannot be performed because the database is using the SIMPLE recovery model.

Fix: Set the database to FULL recovery and take a new full backup before attempting to seed:

ALTER DATABASE [WSS_Content_Intranet] SET RECOVERY FULL;
-- Then take a fresh full backup before seeding
BACKUP DATABASE [WSS_Content_Intranet]
TO DISK = N'\\SPSE-BACKUPS\AGSeed\WSS_Content_Intranet.bak'
WITH INIT, FORMAT, COMPRESSION;

Prevention: The prerequisites checklist query (SELECT name, recovery_model_desc FROM sys.databases) catches this before any seeding starts.

Missing permissions on the backup share

Symptom: Access to the path '\\SPSE-BACKUPS\AGSeed\...' is denied. during backup on the primary or restore on the secondary.

Fix: Verify that both the primary and secondary SQL Server service accounts have the required share permissions:

# Inspect current share permissions
Get-Acl -Path "\\SPSE-BACKUPS\AGSeed" | Format-List
# Grant access if needed (run on the file server)
icacls "D:\AGSeed" /grant "CONTOSO\SPSE-SQL1-svc:(OI)(CI)M"
icacls "D:\AGSeed" /grant "CONTOSO\SPSE-SQL2-svc:(OI)(CI)R"

The primary service account needs Modify; the secondary needs Read. NTFS permissions must be set in addition to share permissions.

AG listener not reachable from SharePoint servers

Symptom: After updating SharePoint to use the listener name (see next section), page loads and crawls fail with SQL connection errors. ULS logs show An exception occurred when connecting to the database.

Diagnosis:

# Run from a SharePoint WFE server
Test-NetConnection -ComputerName SPSE-AG-Listener -Port 1433
Resolve-DnsName SPSE-AG-Listener

Fix checklist:

• DNS: Confirm the AG listener IP is registered in DNS and resolves correctly
• Firewall: Confirm Windows Firewall rules on both SQL nodes allow inbound TCP 1433 from SharePoint server subnets
• WSFC: Confirm the AG listener resource is online in Failover Cluster Manager
• Kerberos (if using Windows auth): Confirm SPNs are registered for the listener name

Secondary database stuck in SYNCHRONIZING

Symptom: sys.dm_hadr_database_replica_states shows synchronization_state_desc = SYNCHRONIZING for an extended period on a synchronous-commit replica (expected on async replicas; concern on sync replicas).

Diagnosis:

SELECT
    db.name,
    rs.synchronization_state_desc,
    rs.log_send_queue_size,
    rs.redo_queue_size,
    rs.log_send_rate,
    rs.redo_rate
FROM sys.dm_hadr_database_replica_states rs
JOIN sys.databases db ON rs.database_id = db.database_id;

A large redo_queue_size that is decreasing means the secondary is catching up — wait for it to drain. A redo_queue_size that is not decreasing points to secondary disk I/O contention.

LSN mismatch error during secondary restore

Symptom: The log in this backup set begins at LSN X, which is too recent to apply to the database.

Fix: This means the log backup was applied before the full backup completed its restore, or the wrong log backup file was used. Drop the database from the secondary, re-restore the full backup WITH NORECOVERY, then restore the log backup WITH NORECOVERY in the exact order they were taken. The Add-SecondaryDatabases.ps1 script’s RESTORE HEADERONLY pre-check catches this before the restore attempt.

Post-Setup — Updating SharePoint to Use the AG Listener

The AG is configured. All content databases are joined and showing SYNCHRONIZED. There is one mandatory step remaining: update SharePoint so it connects to the AG listener name — not the SQL instance name — for every content database.

This step is not optional. If SharePoint is still connecting to SPSE-SQL1 directly, a failover to SPSE-SQL2 will drop all database connections and the farm will be down until SharePoint is reconfigured. The entire point of the AG listener is to provide a stable network name that follows the active primary; SharePoint must be directed to that name.

Update the content database connection strings

# List current content database server connections
Get-SPContentDatabase | Select-Object Name, Server
# Update each content database to use the AG listener name
Get-SPContentDatabase | ForEach-Object {
    Set-SPContentDatabase -Identity $_.Name -DatabaseServer "SPSE-AG-Listener"
    Write-Host "Updated: $($_.Name) -> SPSE-AG-Listener"
}

If you prefer to update databases individually or by web application:

Set-SPContentDatabase -Identity "WSS_Content_Intranet" -DatabaseServer "SPSE-AG-Listener"

After updating all content databases, perform a graceful IIS reset across all SharePoint servers:

# Run on each SharePoint server, or use Invoke-Command to fan out
iisreset /noforce

Verify the update

# Confirm all databases now show the listener name as the server
Get-SPContentDatabase | Select-Object Name, Server | Sort-Object Name

Every row in the Server column should show SPSE-AG-Listener — not SPSE-SQL1 or any instance name. Load a SharePoint page, run a site collection health check, and confirm no SQL connection errors appear in ULS logs (Get-SPLogEvent | Where-Object { $_.Message -match "SQL" }).

What’s Next

With all content databases joined to the AG and SharePoint updated to use the listener, your SPSE farm has automatic SQL failover. A failure of the primary SQL node triggers WSFC health detection, which initiates an AG failover to the synchronized secondary — SharePoint reconnects through the listener within the failover window (typically 20–30 seconds for WSFC quorum + AG failover) without any manual DBA intervention.

Post #10 covers the SharePoint Search Service Application and its databases. Search is the exception to the “add everything to the AG” rule — search component databases have specific constraints that require a different approach, and the AG join sequence for search databases differs from what you have just done for content databases.

Automate Adding 50+ SharePoint Databases to Your AG

Seeding one SharePoint content database to an AG manually is five T-SQL commands minimum plus secondary coordination. For 50 databases, that is hundreds of commands, careful ordering, and no margin for a copy-paste error.

The SQL Migration Bundle includes both scripts used in this post:

• Add-DatabasesToAG.ps1 — full primary-side seeding automation: discovers all content databases, runs backup → restore coordination → AG join → sync verification for every database in a single unattended run
• Add-SecondaryDatabases.ps1 — secondary-side NORECOVERY restore and LSN validation, with readiness signaling to the primary script
• Tested against SharePoint Subscription Edition + SQL Server 2019/2022 + Windows Server 2022
• Includes parameter documentation and a FailedDatabases.csv error output for targeted retry workflows

🗄️ SQL Migration Bundle

Automate adding 50+ SharePoint databases to your AG — skip the manual T-SQL.

Contact sudharsan_1985@live.in to get the SQL Migration Bundle.

Part 9 of 12 — SharePoint 2019 to Subscription Edition Migration
Next: Post 10 — Search Architecture in SPSE →