Using the SqlServer module, it is possible to set up, maintain, and remove SqlAvailability groups. Creating a new SqlAvailability group can be quite complex in T-SQL; however, it is surprisingly simple in PowerShell.
First, we need to establish a connection to both the primary and the secondary instance. This speeds up subsequent calls and ensures both instances are available:
$primaryServer = Get-SqlInstance -MachineName PrimaryComputer -Name Instance
$secondaryServer = Get-SqlInstance -MachineName SecondaryComputer -Name Instance
Next, it is necessary to clone the database you want mirrored onto the replica server. For this, you can use Backup-SqlDatabase and Restore-SqlDatabase, as follows:
# Backup database and log from primary server
$paramBackupSqlDatabase = @{
Database = "Database1"
BackupFile = \fileserverackupsDatabase1.bak
InputObject = $primaryServer
}
Backup-SqlDatabase @paramBackupSqlDatabase
$paramBackupSqlDatabase = @{
Database = "Database1"
BackupFile = \fileserverackupsDatabase1.log
InputObject = $secondaryServer
BackupAction = 'Log'
}
Backup-SqlDatabase @paramBackupSqlDatabase
# Restore the database and log on the secondary (using NO RECOVERY)
$paramRestoreSqlDatabase = @{
Database = "Database1"
BackupFile = \fileserverackupsDatabase1.bak
InputObject = $secondaryServer
NoRecovery = $true
}
Restore-SqlDatabase @paramRestoreSqlDatabase
$paramRestoreSqlDatabase = @{
Database = "Database1"
BackupFile = \fileserverackupsDatabase1.log
InputObject = $secondaryServer
RestoreAction = 'Log'
NoRecovery = $true
}
Restore-SqlDatabase @paramRestoreSqlDatabase
Next, we define the replication on both replica and primary in memory:
# Create an in-memory representation of the primary replica.
$paramNewSqlAvailabilityReplica = @{
Name = "PrimaryComputerInstance"
EndpointUrl = "TCP://PrimaryComputer.domain.com:5022"
AvailabilityMode = "SynchronousCommit"
FailoverMode = "Automatic"
Version = 12
AsTemplate = $true
}
$primaryReplica = New-SqlAvailabilityReplica @paramNewSqlAvailabilityReplica
# Create an in-memory representation of the secondary replica.
$paramNewSqlAvailabilityReplica = @{
Name = "SecondaryComputerInstance"
EndpointUrl = "TCP://SecondaryComputer.domain.com:5022"
AvailabilityMode = "SynchronousCommit"
FailoverMode = "Automatic"
Version = 12
AsTemplate = $true
}
$secondaryReplica = New-SqlAvailabilityReplica @paramNewSqlAvailabilityReplica
Finally, we use the two replicas to define a replication group on the primary, join the secondary instance, and add its copy of the database to the availability group:
# Create the availability group
$paramNewSqlAvailabilityGroup = @{
Name = "AvailabilityGroup1"
InputObject = $primaryServer
AvailabilityReplica = @($primaryReplica, $secondaryReplica)
Database = "Database1"
}
New-SqlAvailabilityGroup @paramNewSqlAvailabilityGroup
# Join the secondary replica to the availability group.
$paramJoinSqlAvailabilityGroup = @{
InputObject = $secondaryServer
Name = "AvailabilityGroup1"
}
Join-SqlAvailabilityGroup @paramJoinSqlAvailabilityGroup
# Join the secondary database to the availability group.
$paramAddSqlAvailabilityDatabase = @{
Path = "SQLSERVER:SQLSecondaryComputerInstanceAvailabilityGroupsAvailabilityGroup1"
Database = "Database1"
}
Add-SqlAvailabilityDatabase @paramAddSqlAvailabilityDatabase
With that, the availability group should be up and running.