How to Add Database to SQL Availability Group

Please refer steps below on How to Add Database to SQL Availability Group

A. Download and Restore the Sample database
1. Download SQL 2016 Sample Database – Around 125MB

  1. Login to SQL1 and restore the downloaded Database
    AddSQLDBtoAG-01
  2. Select the path of the downloaded Database
    AddSQLDBtoAG-02
  3. Select the path where to store SQL Database
    AddSQLDBtoAG-03

You can also use the following T-SQL Commands by modify the following
* Disk = DISK = N’C:\Temp\WideWorldImporters-Full.bak’ – Location of the downloaded sample database
* N’S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA – location to store the SQL Database

USE [master]
RESTORE DATABASE [WideWorldImporters] FROM  DISK = N'C:\Temp\WideWorldImporters-Full.bak' 
WITH  FILE = 1,  MOVE N'WWI_Primary' TO N'S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters.mdf',  
MOVE N'WWI_UserData' TO N'S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters_UserData.ndf',  
MOVE N'WWI_Log' TO N'S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters.ldf',  
MOVE N'WWI_InMemory_Data_1' TO N'S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters_InMemory_Data_1',  
NOUNLOAD,  STATS = 5
GO
  1. Verify Sample database had been successfully restored
Get-ChildItem -Path "S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA"

AddSQLDBtoAG-04

B. Prepare Network Share

  1. Create a New Folder – S:\SQLAGBackup for SQL database backup –
  2. Share the Folder as SQLAGBackup
  3. Assign Full Share permission to UAT\sqlsvc (SQL Service Account)
$BackupPath = "S:\SQLAGBackup"
$BackupShare = "SQLAGBackup"

New-Item -Path $BackupPath -ItemType Container
New-SmbShare -Path $BackupPath -Name $BackupShare

#Full Access Share Permission to the SQL Service Account
Grant-SmbShareAccess -Name $BackupShare -AccountName "UAT\sqlsvc" -AccessRight Full -Confirm:$false

C. Change the Databsae to Full Recovery Mode

  1. Change the newly imported database to Full Recovery Mode
    AddSQLDBtoAG-12

Using T-SQL

USE WideWorldImporters;
ALTER DATABASE WideWorldImporters SET RECOVERY FULL;

Refer to the link to understand further on the different between Simple & Full Recovery Mode
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017

D. Backup the new database to Share Folder

  1. Right click on WideWorldImporters database, and select Back Up
    AddSQLDBtoAG-05
  2. Select the Backup Folder S:\SQLAGBACKUP
    AddSQLDBtoAG-06

Using T-SQL

BACKUP DATABASE [WideWorldImporters] TO  DISK = N'S:\SQLAGBackup\WideWorldImporters.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'WideWorldImporters-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Using PowerShell

$SQLDB ="WideWorldImporters"
$BackupUNC = "\\192.168.1.221\SQLAGBackup\$SQLDB.bak"
$SQL1 = "192.168.1.221"

$DBBackup = "BACKUP DATABASE $SQLDB 
TO DISK ='$BackupUNC'"

Invoke-Sqlcmd -ServerInstance $SQL1 -Query $DBBackup

E. Restore the DB to 2nd SQL Node

  1. Login to SQL2 and right click on Database. Select Restore Database
    AddSQLDBtoAG-07
  2. Restore from UNC path \192.168.1.221\SQLAGBackup\WideWorldImporters.bak
    AddSQLDBtoAG-08

  3. Ensure that SQL Database is restored to the correct path
    AddSQLDBtoAG-09

  4. Select RESTORE WITH NORECOVERY
    AddSQLDBtoAG-10

  5. Database is restored and is in Restoring mode
    AddSQLDBtoAG-11

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. In this case, the restore sequence can restore other backups and roll them forward.

Using T-SQL

USE [master]
RESTORE DATABASE [WideWorldImporters] FROM  DISK = N'\\192.168.1.221\SQLAGBackup\WideWorldImporters.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

Using PowerShell

#Restore BAckup DB to 2nd Node
$SQL2 = "SQL2"
$RestoreDB="RESTORE DATABASE $SQLDB  
    FROM DISK = '$BackupUNC'   
    WITH NORECOVERY"

Invoke-Sqlcmd -ServerInstance $SQL2 -Query $RestoreDB

F. Add DB to SQL Availability Group on SQL1 (Primary Node)

  1. Login to SQL1 and right click on Availability Database. Select Add Database
    AddSQLDBtoAG-13
  2. Click Next
    AddSQLDBtoAG-14
  3. Select WideWorldImporters
    AddSQLDBtoAG-15

  4. Connect to SQL2 server with valid credential
    AddSQLDBtoAG-16

  5. Select JOIN ONLY since we had restored the database to SQL2 manually
    AddSQLDBtoAG-17

  6. Validation skipped
    AddSQLDBtoAG-18

  7. Click Finish
    AddSQLDBtoAG-19

  8. Database joined successfully
    AddSQLDBtoAG-20

  9. Database is in GREEN color and syncronization mode
    AddSQLDBtoAG-21

Using PowerShell

#Get the Name of the SQL AG
$listSQLAG = "Select * from sys.availability_groups"
$SqlAG = Invoke-Sqlcmd -ServerInstance $Sql1 -Query $ListSQLAG
$SQLAGName = $SqlAG.Name 

#Add the DB to SQL AG in Node1
$AddDBNode1 = "ALTER AVAILABILITY GROUP $SQLAGName ADD DATABASE $SQLDB"

Invoke-Sqlcmd -ServerInstance $Sql1 -Query $AddDBNode1
#Add Replica DB to Node2 
$AddDBNode2 = " ALTER DATABASE $SQLDB SET HADR AVAILABILITY GROUP = $SQLAGName"
Invoke-Sqlcmd -ServerInstance $Sql2 -Query $AddDBNode2

Appendix
You can skip Section E & F and let the GUI Wizard to perform the task

  1. Login to SQL1 and right click on Availability Database. Select Add Database
    AddSQLDBtoAG-13

  2. Click Next
    AddSQLDBtoAG-14

  3. Select WideWorldImporters
    AddSQLDBtoAG-15

  4. Connect to SQL2 server with valid credential
    AddSQLDBtoAG-16

  5. Select Full Database and Log Backup
    ** Please skip Section E & F (Do NOT restore the SQL database manually)
    AddSQLDBtoAG-22

  6. Validation passed
    AddSQLDBtoAG-23

  7. Click Finish
    AddSQLDBtoAG-24

  8. The Wizard completed successfully
    AddSQLDBtoAG-25

  9. Database is in GREEN color and syncronization mode
    AddSQLDBtoAG-21

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top