Prepare databases for Always On Availability Groups
In this we will learn how to prepare a database for an Always On availability group in SQL Server by using SQL Server Management Studio, Transact-SQL, or PowerShell. However, preparing a database requires two steps:
- Firstly, restore a recent database backup of the primary database and subsequent log backups onto each server instance that hosts the secondary replica, using RESTORE WITH NORECOVERY
- Secondly, join the restored database to the availability group.
Use SQL Server Management Studio
To prepare a secondary database
- Firstly, unless you already have a recent database backup of the primary database, create a new full or differential database backup. As a best practice, place this backup and any subsequent log backups onto the recommended network share.
- Secondly, create at least one new log backup of the primary database.
- Thirdly, on the server instance that hosts the secondary replica, restore the full database backup of the primary database (and optionally a differential backup) followed by any subsequent log backups.
- On the RESTORE DATABASE Options page, select Leave the database non-operational, and do not roll back the uncommitted transactions.
- However, if the file paths of the primary database and the secondary database differ. For example, if the primary database is on drive ‘F:’ but the server instance that hosts the secondary replica lacks an F: drive, include the MOVE option in your WITH clause.
- Lastly, to complete configuration of the secondary database, you need to join the secondary database to the availability group.
Using Transact-SQL
To prepare a secondary database
- Firstly, unless you have a recent full backup of the primary database, connect to the server instance that hosts the primary replica and create a full database backup. As a best practice, place this backup and any subsequent log backups onto the recommended network share.
- Secondly, on the server instance that hosts the secondary replica, restore the full database backup of the primary database followed by all subsequent log backups. Use WITH NORECOVERY for every restore operation.
- However, if the file paths of the primary database and the secondary database differ. For example, if the primary database is on drive ‘F:’ but the server instance that hosts the secondary replica lacks an F: drive, include the MOVE option in your WITH clause.
- Thirdly, if any log backups have been taken on the primary database since the required log backup. Then, you must also copy these to the server instance that hosts the secondary replica and apply each of those log backups to the secondary database, starting with the earliest and always using RESTORE WITH NORECOVERY.
- Lastly, to complete configuration of the secondary database, you need to join the secondary database to the availability group.
Transact-SQL Example
The following example prepares a secondary database. However, this example uses the AdventureWorks2012 sample database, which uses the simple recovery model by default.
- Firstly, to use the AdventureWorks2012 database, modify it to use the full recovery model:
USE master;
GO
ALTER DATABASE MyDB1
SET RECOVERY FULL;
GO
- Secondly, after modifying the recovery model of the database from SIMPLE to FULL, create a full backup, which can be used to create the secondary database. Because the recovery model has just been changed, the WITH FORMAT option is specified to create a new media set. However, on the server instance that hosts the primary replica (INSTANCE01), create a full backup of the primary database as follows:
BACKUP DATABASE MyDB1
TO DISK = ‘C:\MyDB1.bak’
WITH FORMAT
GO
- Thirdly, copy the full backup to the server instance that hosts the secondary replica.
- After that, restore the full backup, using RESTORE WITH NORECOVERY, onto the server instance that hosts the secondary replica.
- If the paths are identical:
Then, on the computer that hosts the secondary replica, restore the full backup as follows:
RESTORE DATABASE MyDB1
FROM DISK = ‘C:\MyDB1.bak’
WITH NORECOVERY
GO
And, if the paths differ:
However, if the path of the secondary database differs from the path of the primary database (for instance, their drive letters differ), creating the secondary database requires that the restore operation include a MOVE clause.
- Next, after you restore the full backup, you must create a log backup on the primary database. For example, the following Transact-SQL statement backs up the log to the a backup file named E:\MyDB1_log.trn:
BACKUP LOG MyDB1
TO DISK = ‘E:\MyDB1_log.trn’
GO
- Then, before you can join the database to the secondary replica, you must apply the required log backup (and any subsequent log backups).
- For example, the following Transact-SQL statement restores the first log from C:\MyDB1.trn:
RESTORE LOG MyDB1
FROM DISK = ‘E:\MyDB1_log.trn’
WITH FILE=1, NORECOVERY
GO
- Lastly, if any additional log backups occur before the database joins the secondary replica, you must also restore all of those log backups, in sequence, to the server instance that hosts the secondary replica using RESTORE WITH NORECOVERY.
- For example, the following Transact-SQL statement restores two additional logs from E:\MyDB1_log.trn:
RESTORE LOG MyDB1
FROM DISK = ‘E:\MyDB1_log.trn’
WITH FILE=2, NORECOVERY
GO
RESTORE LOG MyDB1
FROM DISK = ‘E:\MyDB1_log.trn’
WITH FILE=3, NORECOVERY
GO
Reference: Microsoft Documentation