Add MS SQL Always On Database via Powershell

You can use the GUI to set up SQL AO groups, but if you want to include it any database creation scripts, you can also do it in PowerShell.

First you’ll need to import the PowerShell module for SQL.

Import-Module SQLPS -DisableNameChecking

Then we’ll set some variables to get ready.  First you set up the database name and the network share to store and distribute the initial sync’s backup.

$dbname = 'databasename'
$networkshare = '\\servername\sharename\'

Then it uses those to build the file names for the backups.

$dbfilebackup = $networkshare + $DBName + '.bak'
$dblogbackup = $networkshare + $DBName + '.trn'

Now you need to tell it about the SQL AO group name, as well as the primary and secondary SQL server names.

$sqlaogroup = 'sqlao01'
$sqlprimary = 'server01'
$sqlsecondary = 'server02'

Then we use that data to build the SQL strings to connect to the availability group.

$sqlstringprimary = 'sqlserver:\sql\' + $sqlprimary + '\default\availabilitygroups\' + $sqlaogroup
$sqlstringsecondary = 'sqlserver:\sql\' + $sqlsecondary + '\default\availabilitygroups\' + $sqlaogroup

Now we’re ready to get to work.  First we’ll take the initial backup of the database.

Backup-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlprimary
Backup-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlprimary -BackupAction Log

Once that’s done, we’ll restore the database to the secondary server.

Restore-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlsecondary -NoRecovery
Restore-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlsecondary -RestoreAction Log -NoRecovery

After that completes it is time to add the database to always on, first on the primary, then on the secondary server.

Add-SqlAvailabilityDatabase -Path $sqlstringprimary -Database $DBName
Add-SqlAvailabilityDatabase -Path $sqlstringsecondary -Database $DBName

Now you’re done and the database should be syncing.  This process also works for a tertiary server, just add the data restore and add commands.

Import-Module SQLPS -DisableNameChecking
$dbname = 'databasename'
$networkshare = '\\servername\sharename\'
$dbfilebackup = $networkshare + $DBName + '.bak'
$dblogbackup = $networkshare + $DBName + '.trn'
$sqlaogroup = 'sqlao01'
$sqlprimary = 'server01'
$sqlsecondary = 'server02'
$sqlstringprimary = 'sqlserver:\sql\' + $sqlprimary + '\default\availabilitygroups\' + $sqlaogroup
$sqlstringsecondary = 'sqlserver:\sql\' + $sqlsecondary + '\default\availabilitygroups\' + $sqlaogroup
Backup-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlprimary
Backup-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlprimary -BackupAction Log
Restore-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlsecondary -NoRecovery
Restore-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlsecondary -RestoreAction Log -NoRecovery
Add-SqlAvailabilityDatabase -Path $sqlstringprimary -Database $DBName
Add-SqlAvailabilityDatabase -Path $sqlstringsecondary -Database $DBName

.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s