date: Wed 24 Sep 2025 12:41:07 PM PDT
subj: MS SQL 2022 standard AOAG restore a database
---------------------------------------------------------
If you are new to Microsoft SQL server, or not, and you are
using Always On Availability Groups (AOAG) with Windows Failover
Cluster (WFC) using MS SQL Standard, you've probably had to or
will have to at one point, restore a database. Attempting to
restore a database to the primary replicant will not work.
You'll get an error not being able to restore a database that is
part of an Availability Group (AG) or that is synchronizing.
This is a huge pain in the ARSH.
To restore a database that is part of an AG is remove the
database from the AG in the primary replica. Then restore the
database, delete the database from the secondary replica, and
then add the database back to the AG. If you have automatic
seeding enabled, the database will be created on your secondary
replicant.
In my AGs I use automatic seeding.
Each MS SQL server nodes have the exact same directory
structure/layout, and same drive letters to allow for automatic
seeding.
------------------------------------
CODE: remove database from AG
------------------------------------
use master
go
alter availability group [my_ag]
remove [my_database]
go
---------------------------------------
Connect to secondary replica delete DB:
---------------------------------------
drop database [my_database]
---------------------------------------
CODE: restore database
------------------------------------
------------------------------------
------------------------------------
CODE: add database back into AG
------------------------------------
use master
go
alter availability group [my_ag]
add [my_database]
go
------------------------------------
Alternative setup with listeners
------------------------------------
Because I want the flexibility of AOAG and the High Availability
using WFC, without using transitional MS SQL clusters,
maintaining a listener will make the cluster look like a
traditional cluster, but you can't have a listener without an
AG.
I haven't explored creating a client access point, but,
even if I did, the client access points are still under
an AG, so its better to just create the listener instead
of a client access point.
The reason for building the MS SQL Server this way, is due to
virtualising the MS SQL Server hosts. Normally when building a
transitional MS SQL Cluster using something like Vmware vsphere,
you must have shared storage between the MS SQL host nodes. This
means you must use Raw Device Mapping when building your Virtual
Machines (vm). Building them is this way is fine, but you will
not be able to create snapshots of your virtual machines when
attaching storage using RDM. Also you must always separate your
Host nodes when using RDM onto different ESXI hosts. If you have
a single ESXI host, you will not be able to have two or more
nodes sharing storage on the same ESXI host. This is a huge pain
if you only have one ESXI host, or even less than three ESXI
hosts supporting your infrastructure. AOAG and WFC allows you to
build VMs without shared storage (no RDM), allowing for:
creating snapshots
replication between data centers
vmotion onto a single ESXI host
host to host transfers (vmotion)
packing up an entire vm and exporting it
There is a price to pay for AOAG WFC setup. That price is
complexity and administration overhead. Have to administrate two
completely separate MS SQL servers instead of one. With
traditional clusters you can add host nodes to support a MS SQL
cluster for HA, but would effectively only have a single MS SQL
server to administer. Also AOAG with MS SQL Standard, you are
only allowed to attach a single database per AG. You can only
have a single replicant, and it must be within the same domain.
To get MS SQL Standard AOAG to behave more like a transitional
cluster create an empty AG with a listener. This way you'll
never have to restore the database, or even an AG with a
listener, that has no database. This will keep your listener
active just like in a transitional MS SQL cluster for HA.
