SQL Server 2016 Failover + AlwaysOn add database to availability group

SQL Server Failover + AlwaysOn add database to availability group

Previous articles have described the configuration of SQL Server Failover + AlwaysOn in detail. Today we mainly introduce adding databases to availability groups under SQL Server Failover Cluster+AlwaysOn.

We first need to create a database and a table

Database name

HAGroupDB2

Create a tabl

Insert data into a table

We added this database to the high availability group; the currently newly created database is not synchronized

We add databases under the high availability group.

Hint requires us to make a full backup.

So we back up.

We also back up the transaction log

After the backup is completed, we need to restore the backed-up database and log on node 3.

Restore database

When restoring, the restore state must be selected in the option: RESTORE WITH NORECOVERY

Database restore completed

Let's check the restore status.

Then restore the log of things

Restore option-Let's choose the second option-RESOTRE WITH NORECOVERY

Restore completed

After we restore the database and transaction log, the state of the database is not being restored

And then we started to add databases to AG.

We chose Join only.

We link to an existing replica server

Connected successfully

Join successfully

We look at the database information in AG

To view the status of the cluster database, it has also become synchronized

Let's check the panel and everything's fine.

Combining the above operations, we can use powershell to do this.

$DatabaseBackupFile = "\\ share\ backups\ MyDatabase.bak" $LogBackupFile = "\\ share\ backups\ MyDatabase.trn" $MyAgPrimaryPath = "SQLSERVER:\ SQL\ PrimaryServer\ InstanceName\ AvailabilityGroups\ MyAg" $MyAgSecondaryPath = "SQLSERVER:\ SQL\ SecondaryServer\ InstanceName\ AvailabilityGroups\ MyAg" Backup-SqlDatabase-Database "MyDatabase"-BackupFile $DatabaseBackupFile-ServerInstance "PrimaryServer\ InstanceName" Backup-SqlDatabase-Database "MyDatabase"-BackupFile $LogBackupFile-ServerInstance "ServerInstance"-PrimaryServer 'PrimaryServer-InstanceName "- InstanceName $InstanceName -ServerInstance "SecondaryServer\ InstanceName"-NoRecovery Restore-SqlDatabase-Database "MyDatabase"-BackupFile $LogBackupFile-ServerInstance "SecondaryServer\ InstanceName"-RestoreAction 'Log'-NoRecovery Add-SqlAvailabilityDatabase-Path $MyAgPrimaryPath-Database "MyDatabase" Add-SqlAvailabilityDatabase-Path $MyAgSecondaryPath-Database "MyDatabase"