#Requires -Version 3
function New-CustomAG{
<# .SYNOPSIS
Creates AlwaysOn availabilty group for servers provided.
.DESCRIPTION
Utilizes SMO to access SQL Server to backup database and create Availabity Group Based on Params
If SQL Client Tools are not loaded on the machine it will error and fail.
If AlwaysOn is not enabled on the Instance of SQL Server the service will be restarted to enable it. This is required for it to continue with AG Creation.
If you do not want the service to be recycled enable prior to executing the script.
.PARAMETER Servers
Servers participating in AlwaysOn, first server will be assumed to be where the database is which needs to be replicated.
.PARAMETER SQLAgName
Availability Group Name database will be created in
.PARAMETER CNO
Cluster Name Object which was created IN Active Directory. If not passed assumes its derived from server names.
.PARAMETER AgListenerName
Listener Name to be created that references availability group if no listener is passed it will be created same as SQLAgname
.PARAMETER SqlAgDatabase
Database which will be placed in Availability Group. If HADRSeed database will be created and dropped.
.PARAMETER AgListenerPort
Port listener will listen on
.PARAMETER IPAddresses
One or Many Ip addresses which will be tied to the listener. One Ip is required for each subnet
.PARAMETER AgListenerSubnetMask
Subnetmask for Listeners (Assumption all will utilize the same subnetmask)
.PARAMETER BackupDirectory
Backup location which all nodes will have access to. Used to backup and restore database from.
.EXAMPLE
Creates Availability Group AG-TNTNTX10 for three servers cross subnet
New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010 -SqlAgName AG-TNTNTX10 -IPAddresses 192.168.81.211,192.168.91.212 -BackupDirectory \\ohnas001\SQLBackups
.EXAMPLE
New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010,WASQL5010 -SqlAgName AG-TNTXWA5001 -CNO CNOTNTNTX5010 -IPAddresses 192.168.81.31,192.168.91.31,192.168.71.31 -BackupDirectory \\ohnas001\SQLBackups -verbose
#>
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)]
[string[]]$Servers,
[Parameter(Mandatory=$true)]
[string]$SqlAgName,
[string]$CNO,
[string]$AgListenerName,
[string]$SqlAgDatabase="HADRSeed",
[string]$AgListenerPort="1433",
[string[]]$IPAddresses,
[string]$AgListenerSubnetMask ="255.255.255.0",
[Parameter(Mandatory=$true)]
[string]$BackupDirectory
)
Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Loading SMO Assemblies ...");
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") |Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ...");
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ...");
$HadrEndpointName = "Hadr_Endpoint"
$HadrEndpointPort = 5022
$LoginType = "WindowsUser"
if (!$AgListenerName) #Listener was not passed default to AG Name
{
$AgListenerName = $SqlAgName
}
$SqlServerPrim = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servers[0]
#Loop through servers Backing up Database/Tranlog and Restore on each Secondary with NoRecovery
$loopCnt = 0
foreach ($Server in $Servers){
$SqlConn = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
Try{
If($SqlConn.IsHadrEnabled -eq 0)
{
Enable-SqlAlwaysOn -ServerInstance $Server -Force
Get-Service -computer $Server -DisplayName SQL*Server*MSSQL* -Exclude *Agent*|Restart-Service
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Finished Enabling AlwaysOn on the nodes ...");
}
}
catch{
Throw "Failed Enabling AlwaysOn on the $Server"
Exit
}
If($loopCnt -eq 0) #First Server is Primary Back up Full and TranLogs
{
Try{
If($SqlAgDatabase -eq "HADRSeed" -and ($SqlConn.Databases[$SqlAgDatabase].Name -eq $null))
{
$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -ArgumentList $SqlConn,"HADRSeed"
$db.Create()
}
}
Catch{
Throw "Failed create $SqlAgDatabase database."
Exit
}
Try{
if ($SQLconn.Databases[$SqlAgDatabase].RecoveryModel -ne "FULL")
{
$sqlconn.Databases[$SqlAgDatabase].RecoveryModel = "Full"
$sqlconn.Databases[$SqlAgDatabase].Alter();
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Database is not in Full Recovery Mode Setting to Full...");
}
}
Catch{
Throw "Failed to set $SqlAgDatabase to Full Recovery."
Exit
}
Try{
$DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
$DbBackup.Database = $SqlAgDatabase
$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$DbBackup.Initialize = $true
$DbBackup.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbBackup.SqlBackup($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup Full for $SqlAgDatabase ...");
$DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
$DbBackup.Database = $SqlAgDatabase
$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log
$DbBackup.Initialize = $true
$DbBackup.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbBackup.SqlBackup($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup TranLog for $SqlAgDatabase ...");
}
Catch{
Throw "Failed to backup $SqlAgDatabase"
Exit
}
}#if First Server Backup
else
{
try{
$DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
$DbRestore.Database = $SqlAgDatabase
$DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
$DbRestore.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak",
[Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbRestore.NoRecovery = $true
$DbRestore.SqlRestore($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Full for $SqlAgDatabase on $Server...");
$DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
$DbRestore.Database = $SqlAgDatabase
$DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
$DbRestore.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn",
[Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbRestore.NoRecovery = $true
$DbRestore.SqlRestore($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Tran Log for $SqlAgDatabase on $Server...");
}
catch{
Throw "Failed to Restore $SqlAgDatabase on $Server..."
Exit
}
}#Else Secondary Servers
$loopcnt=$loopCnt+1
}#For Each Loop through servers Backing up Database/Tranlog
$AvailabilityGroup = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroup -ArgumentList $SqlServerPrim, $SqlAgName
$AvailabilityGroup.AutomatedBackupPreference="Primary"
#Loop through servers and create Endpoints for AlwaysOn
$loopCnt = 0
Foreach($Server in $Servers){
Try{
$SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
$Endpoint = $SqlConn.Endpoints | Where-Object {$_.EndpointType -eq [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring}
$ServiceAccount = $SQLconn.ServiceAccount
if(($SqlConn.logins | Where-Object {($_.LoginType -eq "WindowsUser") -and ($_.Name -eq $ServiceAccount)}) -eq $null){
$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $SqlConn ,$ServiceAccount
$login.LoginType = $LoginType
$login.Create()
}
if(!$Endpoint){
$Endpoint = New-Object -typename Microsoft.SqlServer.Management.Smo.Endpoint -ArgumentList $SqlConn,$HadrEndpointName
$Endpoint.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
$Endpoint.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp
$Endpoint.Protocol.Tcp.ListenerPort = $HadrEndpointPort
$Endpoint.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::All
$Endpoint.Payload.DatabaseMirroring.EndpointEncryption = [Microsoft.SqlServer.Management.Smo.EndpointEncryption]::Required
$Endpoint.Payload.DatabaseMirroring.EndpointEncryptionAlgorithm = [Microsoft.SqlServer.Management.Smo.EndpointEncryptionAlgorithm]::Aes
$Endpoint.Create()
$Endpoint.Start()
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Create Endpoint on $Server...");
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Granting connect Permission on $Server to $ServiceAccount ...");
#$ConnectPerm = New-Object Microsoft.SqlServer.Managment.Smo.ObjectPermissionSet(Connect)
#$Endpoint.Grant($ConnectPerm,$ServiceAccount)
$Cmd = "GRANT CONNECT ON ENDPOINT::[" + $HadrEndpointName + "] TO [" + $ServiceAccount + "]"
$con = "server=$Server;database=master;Integrated Security=True;"
$da = new-object -typename System.Data.SqlClient.SqlDataAdapter -ArgumentList $Cmd, $con
$dt = new-object -typename System.Data.DataTable
$da.fill($dt) | out-null
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done granting connect Permission on $Server to $ServiceAccount ...");
}
If($loopCnt -eq 0){
$PrimaryReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $AvailabilityGroup, $SqlServerPrim.NetName
$PrimaryReplica.EndpointUrl = "TCP://$($SqlServerPrim.NetName):$($Endpoint.Protocol.Tcp.ListenerPort)"
$PrimaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic
$PrimaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit
$AvailabilityGroup.AvailabilityReplicas.Add($PrimaryReplica)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Primary Replica..."+$PrimaryReplica.EndpointUrl);
}
Else{
$SecondaryReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $AvailabilityGroup, $SqlConn.NetName
$SecondaryReplica.EndpointUrl = "TCP://$($SqlConn.NetName):$($Endpoint.Protocol.Tcp.ListenerPort)"
If ($loopCnt -eq 1)
{
$SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic
$SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit
}
Else{
$SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Manual
$SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::ASynchronousCommit
}
$AvailabilityGroup.AvailabilityReplicas.Add($SecondaryReplica)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Secondary Replica..."+$SecondaryReplica.EndpointUrl)
}
}
Catch {
Throw "Failed to Create/Grant Endpoints on $Server..."
Exit
}
$loopCnt=$loopCnt+1
}#For Each Create Endpoints
$AvailabilityDb = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityDatabase -ArgumentList $AvailabilityGroup, $SqlAgDatabase
$AvailabilityGroup.AvailabilityDatabases.Add($AvailabilityDb)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Database to AG..."+$AvailabilityDb);
#if No ip Addresses Passed we will not create a listener
if ($IpAddresses){
#If CNO is not passed we will derive it from server names
#First three Char ="CLU"
#Next Series are the first two char of server name which is location ex. NY for NewYork
#Final is the Last four digits of the Primary Server
#For the following Servers MISQL5010, TNSQL5010, OHSQL5010 CNO would be CluMITNOH5010
If (!$CNO){
Try
{
$CNO = "Clu"
foreach ($Server in $Servers)
{
$CNO = $CNO + $Server.Substring(0,2)
}
$CNO =$CNO + $Server.Substring($Server.Length - 4,4)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Captured Cluster Name as $CNO...")
}
Catch
{
Throw ": Failed to Get Cluster Name"
exit
}
}
#Verify Active Directory Tools are installed, if they are load if not Throw Error
If (!(get-module -ListAvailable | Where-Object {$_.Name -eq "ActiveDirectory"})){
Throw "Active Directory Module is Required."
Exit
}
else{import-module ActiveDirectory -ErrorAction Stop}
try{
$CNO_OU = Get-ADComputer $CNO
$Trim = $CNO.length+4
$CNOlgth = $CNO_OU.DistinguishedName.Length - $trim
$OUPath = $CNO_OU.ToString().Substring($Trim,$CNOlgth)
}
catch{
Throw ": Failed to find Computer in AD"
exit
}
#Create Computer Object for the AgListenerName
#Grant Full control to CNO Computer Object using DSACLS
$m = Get-ADComputer -Filter {Name -eq $SqlAgName} -Server $env:USERDOMAIN | Select-Object -Property * | Measure-Object
If ($m.Count -eq 0)
{
Try{
New-ADComputer -Name $AgListenerName -SamAccountName $AgListenerName -Path $OUPath -Enabled $false
}
Catch{
Throw "Failed to Create $AgListenerName in $AG_OU"
Exit
}
$SucccessChk =0
#Check for AD Object Validate at least three successful attempts
$i=1
While ($i -le 5) {
Try{
$ListChk = Get-ADComputer -filter {Name -like $AgListenerName}
If ($ListChk){$SuccessChk++}
Start-Sleep -Seconds 10
If($SuccesChk -eq 3){break}
}
Catch{
Throw "Failed Validate $AgListenerName was created in $OUPath"
Exit
}
$i++
}
}
Try{
$AG_OU = Get-ADComputer $AgListenerName
DSACLS $AG_OU /I:T /G $CNO_OU":GA" | Out-Null
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Assigned permisions to $SqlAgName and $CNO in $OUPath")
}
Catch{
Throw "Failed Validate grant permissions on $AgListenerName in location $OU_PAth to $CNO in location $CNO_OU"
Exit
}
$AgListener = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListener -ArgumentList $AvailabilityGroup, $AgListenerName
$AgListener.PortNumber = $AgListenerPort
Foreach($IP in $IPAddresses){
Try{
$AgListenerIp = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress -ArgumentList $AgListener
$AgListenerIp.IsDHCP = $false
$AgListenerIp.IPAddress = $IP
$AgListenerIp.SubnetMask = $AgListenerSubnetMask
$AgListener.AvailabilityGroupListenerIPAddresses.Add($AgListenerIp)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added $IP to $AgListenerName...");
}
Catch {
Throw "Failed to Add $IP to $AgListenerName..."
Exit
}
}#For Each IP Address
Try{
$AvailabilityGroup.AvailabilityGroupListeners.Add($AgListener);
}
Catch{
Throw "Failed to Add $AgListenerName to $SqlAgName..."
Exit
}
}#If Ipaddresses are passed
#Create Availabilty Group
Try{
$SqlServerPrim.AvailabilityGroups.Add($AvailabilityGroup)
$AvailabilityGroup.Create()
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Created AG...");
}
Catch{
Throw ": Failed to Create Availability Group $SqlAgName..."
Exit
}
#Loop through Secondary Servers and Join them to the Availability Group
$loopCnt =0
Foreach($Server in $Servers){
Try {
if ($loopCnt -ne 0){
$SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
$SqlConn.JoinAvailabilityGroup($SqlAgName)
$SqlConn.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].JoinAvailablityGroup()
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Join Secondary $Server to AG...");
}
}
Catch{
Throw "Failed to Join $Server to $SqlAgName..."
Exit
}
$loopCnt=$loopCnt+1
}#For Loop for Joining Secondaries
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Creating Adding Replicas...");
#Remove all Backup Files which were creating during this excersize
Try{
$DBBackupFile = "$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak"
$DBTRNFile = "$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn"
If (Test-Path -Path $DBBackupFile){Remove-Item -Path $DBBackupFile}
If (Test-Path -Path $DBTRNFile){Remove-Item -Path $DBTRNFile}
} Catch{
Throw "Failed to Cleanup Backup Files..."
Exit
}
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Cleaning Up Backup Files...");
#If we created a dummy database to create the AG Remove the database from AG and drop it
If($SqlAgDatabase -eq "HADRSeed"){
$loopCnt = 0
$SqlServerPrim.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].Drop();
Foreach($Server in $Servers){
Try {
Start-Sleep -Seconds 30;
$SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
If($loopCnt -eq 0){$sqlconn.KillAllProcesses($SqlAgDatabase)};
$sqlconn.Databases[$SqlAgDatabase].Drop()
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Removed $SqlAgDatabase from $Server");
}
Catch{
Write-Verbose -Message "Failed to remove $SqlAgDatabase from $Server"
}
$loopCnt=$loopCnt+1
}#ForEach Server Remove Seed DB
}#If HADRSeed
}#Function End
New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010,WASQL5010 -SqlAgName AG-TNTXWA5001 -CNO CluTNTNTX5010 -IPAddresses 192.168.81.31,192.168.91.31,192.168.71.31 -BackupDirectory \\ohnas001\SQLBackups -verbose
function New-CustomAG{
<# .SYNOPSIS
Creates AlwaysOn availabilty group for servers provided.
.DESCRIPTION
Utilizes SMO to access SQL Server to backup database and create Availabity Group Based on Params
If SQL Client Tools are not loaded on the machine it will error and fail.
If AlwaysOn is not enabled on the Instance of SQL Server the service will be restarted to enable it. This is required for it to continue with AG Creation.
If you do not want the service to be recycled enable prior to executing the script.
.PARAMETER Servers
Servers participating in AlwaysOn, first server will be assumed to be where the database is which needs to be replicated.
.PARAMETER SQLAgName
Availability Group Name database will be created in
.PARAMETER CNO
Cluster Name Object which was created IN Active Directory. If not passed assumes its derived from server names.
.PARAMETER AgListenerName
Listener Name to be created that references availability group if no listener is passed it will be created same as SQLAgname
.PARAMETER SqlAgDatabase
Database which will be placed in Availability Group. If HADRSeed database will be created and dropped.
.PARAMETER AgListenerPort
Port listener will listen on
.PARAMETER IPAddresses
One or Many Ip addresses which will be tied to the listener. One Ip is required for each subnet
.PARAMETER AgListenerSubnetMask
Subnetmask for Listeners (Assumption all will utilize the same subnetmask)
.PARAMETER BackupDirectory
Backup location which all nodes will have access to. Used to backup and restore database from.
.EXAMPLE
Creates Availability Group AG-TNTNTX10 for three servers cross subnet
New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010 -SqlAgName AG-TNTNTX10 -IPAddresses 192.168.81.211,192.168.91.212 -BackupDirectory \\ohnas001\SQLBackups
.EXAMPLE
New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010,WASQL5010 -SqlAgName AG-TNTXWA5001 -CNO CNOTNTNTX5010 -IPAddresses 192.168.81.31,192.168.91.31,192.168.71.31 -BackupDirectory \\ohnas001\SQLBackups -verbose
#>
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)]
[string[]]$Servers,
[Parameter(Mandatory=$true)]
[string]$SqlAgName,
[string]$CNO,
[string]$AgListenerName,
[string]$SqlAgDatabase="HADRSeed",
[string]$AgListenerPort="1433",
[string[]]$IPAddresses,
[string]$AgListenerSubnetMask ="255.255.255.0",
[Parameter(Mandatory=$true)]
[string]$BackupDirectory
)
Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Loading SMO Assemblies ...");
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") |Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ...");
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Loading SMO Assemblies ...");
$HadrEndpointName = "Hadr_Endpoint"
$HadrEndpointPort = 5022
$LoginType = "WindowsUser"
if (!$AgListenerName) #Listener was not passed default to AG Name
{
$AgListenerName = $SqlAgName
}
$SqlServerPrim = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servers[0]
#Loop through servers Backing up Database/Tranlog and Restore on each Secondary with NoRecovery
$loopCnt = 0
foreach ($Server in $Servers){
$SqlConn = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
Try{
If($SqlConn.IsHadrEnabled -eq 0)
{
Enable-SqlAlwaysOn -ServerInstance $Server -Force
Get-Service -computer $Server -DisplayName SQL*Server*MSSQL* -Exclude *Agent*|Restart-Service
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Finished Enabling AlwaysOn on the nodes ...");
}
}
catch{
Throw "Failed Enabling AlwaysOn on the $Server"
Exit
}
If($loopCnt -eq 0) #First Server is Primary Back up Full and TranLogs
{
Try{
If($SqlAgDatabase -eq "HADRSeed" -and ($SqlConn.Databases[$SqlAgDatabase].Name -eq $null))
{
$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -ArgumentList $SqlConn,"HADRSeed"
$db.Create()
}
}
Catch{
Throw "Failed create $SqlAgDatabase database."
Exit
}
Try{
if ($SQLconn.Databases[$SqlAgDatabase].RecoveryModel -ne "FULL")
{
$sqlconn.Databases[$SqlAgDatabase].RecoveryModel = "Full"
$sqlconn.Databases[$SqlAgDatabase].Alter();
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Database is not in Full Recovery Mode Setting to Full...");
}
}
Catch{
Throw "Failed to set $SqlAgDatabase to Full Recovery."
Exit
}
Try{
$DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
$DbBackup.Database = $SqlAgDatabase
$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$DbBackup.Initialize = $true
$DbBackup.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbBackup.SqlBackup($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup Full for $SqlAgDatabase ...");
$DbBackup = New-Object -typename Microsoft.SqlServer.Management.Smo.Backup
$DbBackup.Database = $SqlAgDatabase
$DbBackup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Log
$DbBackup.Initialize = $true
$DbBackup.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbBackup.SqlBackup($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": DbBackup TranLog for $SqlAgDatabase ...");
}
Catch{
Throw "Failed to backup $SqlAgDatabase"
Exit
}
}#if First Server Backup
else
{
try{
$DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
$DbRestore.Database = $SqlAgDatabase
$DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
$DbRestore.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak",
[Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbRestore.NoRecovery = $true
$DbRestore.SqlRestore($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Full for $SqlAgDatabase on $Server...");
$DbRestore = New-Object -typename Microsoft.SqlServer.Management.Smo.Restore
$DbRestore.Database = $SqlAgDatabase
$DbRestore.Action = [Microsoft.SqlServer.Management.Smo.RestoreActionType]::Log
$DbRestore.Devices.AddDevice("$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn",
[Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$DbRestore.NoRecovery = $true
$DbRestore.SqlRestore($SqlConn)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Restore Tran Log for $SqlAgDatabase on $Server...");
}
catch{
Throw "Failed to Restore $SqlAgDatabase on $Server..."
Exit
}
}#Else Secondary Servers
$loopcnt=$loopCnt+1
}#For Each Loop through servers Backing up Database/Tranlog
$AvailabilityGroup = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroup -ArgumentList $SqlServerPrim, $SqlAgName
$AvailabilityGroup.AutomatedBackupPreference="Primary"
#Loop through servers and create Endpoints for AlwaysOn
$loopCnt = 0
Foreach($Server in $Servers){
Try{
$SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
$Endpoint = $SqlConn.Endpoints | Where-Object {$_.EndpointType -eq [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring}
$ServiceAccount = $SQLconn.ServiceAccount
if(($SqlConn.logins | Where-Object {($_.LoginType -eq "WindowsUser") -and ($_.Name -eq $ServiceAccount)}) -eq $null){
$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $SqlConn ,$ServiceAccount
$login.LoginType = $LoginType
$login.Create()
}
if(!$Endpoint){
$Endpoint = New-Object -typename Microsoft.SqlServer.Management.Smo.Endpoint -ArgumentList $SqlConn,$HadrEndpointName
$Endpoint.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
$Endpoint.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp
$Endpoint.Protocol.Tcp.ListenerPort = $HadrEndpointPort
$Endpoint.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::All
$Endpoint.Payload.DatabaseMirroring.EndpointEncryption = [Microsoft.SqlServer.Management.Smo.EndpointEncryption]::Required
$Endpoint.Payload.DatabaseMirroring.EndpointEncryptionAlgorithm = [Microsoft.SqlServer.Management.Smo.EndpointEncryptionAlgorithm]::Aes
$Endpoint.Create()
$Endpoint.Start()
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Create Endpoint on $Server...");
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Granting connect Permission on $Server to $ServiceAccount ...");
#$ConnectPerm = New-Object Microsoft.SqlServer.Managment.Smo.ObjectPermissionSet(Connect)
#$Endpoint.Grant($ConnectPerm,$ServiceAccount)
$Cmd = "GRANT CONNECT ON ENDPOINT::[" + $HadrEndpointName + "] TO [" + $ServiceAccount + "]"
$con = "server=$Server;database=master;Integrated Security=True;"
$da = new-object -typename System.Data.SqlClient.SqlDataAdapter -ArgumentList $Cmd, $con
$dt = new-object -typename System.Data.DataTable
$da.fill($dt) | out-null
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done granting connect Permission on $Server to $ServiceAccount ...");
}
If($loopCnt -eq 0){
$PrimaryReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $AvailabilityGroup, $SqlServerPrim.NetName
$PrimaryReplica.EndpointUrl = "TCP://$($SqlServerPrim.NetName):$($Endpoint.Protocol.Tcp.ListenerPort)"
$PrimaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic
$PrimaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit
$AvailabilityGroup.AvailabilityReplicas.Add($PrimaryReplica)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Primary Replica..."+$PrimaryReplica.EndpointUrl);
}
Else{
$SecondaryReplica = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityReplica -ArgumentList $AvailabilityGroup, $SqlConn.NetName
$SecondaryReplica.EndpointUrl = "TCP://$($SqlConn.NetName):$($Endpoint.Protocol.Tcp.ListenerPort)"
If ($loopCnt -eq 1)
{
$SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Automatic
$SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::SynchronousCommit
}
Else{
$SecondaryReplica.FailoverMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]::Manual
$SecondaryReplica.AvailabilityMode = [Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]::ASynchronousCommit
}
$AvailabilityGroup.AvailabilityReplicas.Add($SecondaryReplica)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Secondary Replica..."+$SecondaryReplica.EndpointUrl)
}
}
Catch {
Throw "Failed to Create/Grant Endpoints on $Server..."
Exit
}
$loopCnt=$loopCnt+1
}#For Each Create Endpoints
$AvailabilityDb = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityDatabase -ArgumentList $AvailabilityGroup, $SqlAgDatabase
$AvailabilityGroup.AvailabilityDatabases.Add($AvailabilityDb)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added Database to AG..."+$AvailabilityDb);
#if No ip Addresses Passed we will not create a listener
if ($IpAddresses){
#If CNO is not passed we will derive it from server names
#First three Char ="CLU"
#Next Series are the first two char of server name which is location ex. NY for NewYork
#Final is the Last four digits of the Primary Server
#For the following Servers MISQL5010, TNSQL5010, OHSQL5010 CNO would be CluMITNOH5010
If (!$CNO){
Try
{
$CNO = "Clu"
foreach ($Server in $Servers)
{
$CNO = $CNO + $Server.Substring(0,2)
}
$CNO =$CNO + $Server.Substring($Server.Length - 4,4)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Captured Cluster Name as $CNO...")
}
Catch
{
Throw ": Failed to Get Cluster Name"
exit
}
}
#Verify Active Directory Tools are installed, if they are load if not Throw Error
If (!(get-module -ListAvailable | Where-Object {$_.Name -eq "ActiveDirectory"})){
Throw "Active Directory Module is Required."
Exit
}
else{import-module ActiveDirectory -ErrorAction Stop}
try{
$CNO_OU = Get-ADComputer $CNO
$Trim = $CNO.length+4
$CNOlgth = $CNO_OU.DistinguishedName.Length - $trim
$OUPath = $CNO_OU.ToString().Substring($Trim,$CNOlgth)
}
catch{
Throw ": Failed to find Computer in AD"
exit
}
#Create Computer Object for the AgListenerName
#Grant Full control to CNO Computer Object using DSACLS
$m = Get-ADComputer -Filter {Name -eq $SqlAgName} -Server $env:USERDOMAIN | Select-Object -Property * | Measure-Object
If ($m.Count -eq 0)
{
Try{
New-ADComputer -Name $AgListenerName -SamAccountName $AgListenerName -Path $OUPath -Enabled $false
}
Catch{
Throw "Failed to Create $AgListenerName in $AG_OU"
Exit
}
$SucccessChk =0
#Check for AD Object Validate at least three successful attempts
$i=1
While ($i -le 5) {
Try{
$ListChk = Get-ADComputer -filter {Name -like $AgListenerName}
If ($ListChk){$SuccessChk++}
Start-Sleep -Seconds 10
If($SuccesChk -eq 3){break}
}
Catch{
Throw "Failed Validate $AgListenerName was created in $OUPath"
Exit
}
$i++
}
}
Try{
$AG_OU = Get-ADComputer $AgListenerName
DSACLS $AG_OU /I:T /G $CNO_OU":GA" | Out-Null
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) +": Assigned permisions to $SqlAgName and $CNO in $OUPath")
}
Catch{
Throw "Failed Validate grant permissions on $AgListenerName in location $OU_PAth to $CNO in location $CNO_OU"
Exit
}
$AgListener = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListener -ArgumentList $AvailabilityGroup, $AgListenerName
$AgListener.PortNumber = $AgListenerPort
Foreach($IP in $IPAddresses){
Try{
$AgListenerIp = New-Object -typename Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress -ArgumentList $AgListener
$AgListenerIp.IsDHCP = $false
$AgListenerIp.IPAddress = $IP
$AgListenerIp.SubnetMask = $AgListenerSubnetMask
$AgListener.AvailabilityGroupListenerIPAddresses.Add($AgListenerIp)
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Added $IP to $AgListenerName...");
}
Catch {
Throw "Failed to Add $IP to $AgListenerName..."
Exit
}
}#For Each IP Address
Try{
$AvailabilityGroup.AvailabilityGroupListeners.Add($AgListener);
}
Catch{
Throw "Failed to Add $AgListenerName to $SqlAgName..."
Exit
}
}#If Ipaddresses are passed
#Create Availabilty Group
Try{
$SqlServerPrim.AvailabilityGroups.Add($AvailabilityGroup)
$AvailabilityGroup.Create()
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Created AG...");
}
Catch{
Throw ": Failed to Create Availability Group $SqlAgName..."
Exit
}
#Loop through Secondary Servers and Join them to the Availability Group
$loopCnt =0
Foreach($Server in $Servers){
Try {
if ($loopCnt -ne 0){
$SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
$SqlConn.JoinAvailabilityGroup($SqlAgName)
$SqlConn.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].JoinAvailablityGroup()
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Join Secondary $Server to AG...");
}
}
Catch{
Throw "Failed to Join $Server to $SqlAgName..."
Exit
}
$loopCnt=$loopCnt+1
}#For Loop for Joining Secondaries
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Creating Adding Replicas...");
#Remove all Backup Files which were creating during this excersize
Try{
$DBBackupFile = "$BackupDirectory\$($SqlAgDatabase)_AgSetup_full.bak"
$DBTRNFile = "$BackupDirectory\$($SqlAgDatabase)_AgSetup_log.trn"
If (Test-Path -Path $DBBackupFile){Remove-Item -Path $DBBackupFile}
If (Test-Path -Path $DBTRNFile){Remove-Item -Path $DBTRNFile}
} Catch{
Throw "Failed to Cleanup Backup Files..."
Exit
}
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Done Cleaning Up Backup Files...");
#If we created a dummy database to create the AG Remove the database from AG and drop it
If($SqlAgDatabase -eq "HADRSeed"){
$loopCnt = 0
$SqlServerPrim.AvailabilityGroups[$SqlAgName].AvailabilityDatabases[$SqlAgDatabase].Drop();
Foreach($Server in $Servers){
Try {
Start-Sleep -Seconds 30;
$SqlConn = New-Object -typename Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
If($loopCnt -eq 0){$sqlconn.KillAllProcesses($SqlAgDatabase)};
$sqlconn.Databases[$SqlAgDatabase].Drop()
Write-Verbose -Message ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": Removed $SqlAgDatabase from $Server");
}
Catch{
Write-Verbose -Message "Failed to remove $SqlAgDatabase from $Server"
}
$loopCnt=$loopCnt+1
}#ForEach Server Remove Seed DB
}#If HADRSeed
}#Function End
New-CustomAG -Servers TNSQL5010,TNSQL5011,TXSQL5010,WASQL5010 -SqlAgName AG-TNTXWA5001 -CNO CluTNTNTX5010 -IPAddresses 192.168.81.31,192.168.91.31,192.168.71.31 -BackupDirectory \\ohnas001\SQLBackups -verbose
No comments:
Post a Comment