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

2. Login to SQL1 and restore the downloaded Database
   

3. Select the path of the downloaded Database
   

4. Select the path where to store SQL Database
   

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

5. Verify Sample database had been successfully restored

Get-ChildItem -Path "S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA"

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
   

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
   

2. Select the Backup Folder S:\SQLAGBACKUP
   

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
   

2. Restore from UNC path \192.168.1.221\SQLAGBackup\WideWorldImporters.bak
   

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

4. Select RESTORE WITH NORECOVERY
   

5. Database is restored and is in Restoring mode
   

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
   

2. Click Next
   

3. Select WideWorldImporters
   

4. Connect to SQL2 server with valid credential
   

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

6. Validation skipped
   

7. Click Finish
   

8. Database joined successfully
   

9. Database is in GREEN color and syncronization mode
   

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
   

2. Click Next
   

3. Select WideWorldImporters
   

4. Connect to SQL2 server with valid credential
   

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

6. Validation passed
   

7. Click Finish
   

8. The Wizard completed successfully
   

9. Database is in GREEN color and syncronization mode