Windows Azure Pack PowerShell SQL Resource Provider Add Servers & Hosting Group

Here we are again to go over some more commands for automation of Azure Pack, and in this case the addition and or removal of new/old SQL servers to your existing infrastructure, in my previous posts I have gone over the Get, Add, and Removal of Users, Plans, and Subscriptions which are the core components of Azure Pack. For this post I will go over the process of obtaining, the SQL Server Hosting Group, in this case it is a standalone group as Microsoft has not exposed the ability to add a highly available group via PowerShell.

Once we have spent our time working on the SQL Server Hosting group we will move on to adding a new SQL server to the Group, along with the removal of the SQL Server.

Much like the last posts I have included the first 33 lines where we obtain all of the required details for Azure Pack to execute the commands.

# query SQL function
function Invoke-SQL {
param([string] $connstring,[string] $sqlCommand)
$connectionString = $connstring
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
$connection.Close()
$dataSet.Tables
}
 
# get WAP connection string
$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq 'ApplicationServicesConnectionstring'}).value
$connstring = $connstring.Split(';')[0] + ';' + $connstring.Split(';')[1] + "; Integrated Security=SSPI"
 
# get WAP websites
$adminsite = (Invoke-SQL -sqlCommand "SELECT value FROM [Config].[Settings] where Namespace = 'AdminSite' and Name = 'Authentication.Fqdn'" -connstring $connstring).value
$TenantSite = (Invoke-SQL -sqlCommand "SELECT value FROM [Config].[Settings] where Namespace = 'TenantSite' and Name = 'Authentication.Fqdn'" -connstring $connstring).value
$AuthSite = (Invoke-SQL -sqlCommand "SELECT value FROM [Config].[Settings] where Namespace = 'AuthSite' and Name = 'Authentication.Fqdn'" -connstring $connstring).value
$windowsauthsite = (Invoke-SQL -sqlCommand "SELECT value FROM [Config].[Settings] where Namespace = 'WindowsAuthSite' and Name = 'Authentication.Fqdn'" -connstring $connstring).value
$AdminAPI = (Invoke-SQL -sqlCommand "SELECT value FROM [Config].[Settings] where Namespace = 'AdminSite' and Name = 'Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'" -connstring $connstring).value
$TenantAPI = (Invoke-SQL -sqlCommand "SELECT value FROM [Config].[Settings] where Namespace = 'TenantSite' and Name = 'Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'" -connstring $connstring).value
$ClientRealm = (Invoke-SQL -sqlCommand "SELECT value FROM [Config].[Settings] where Namespace = 'AdminSite' and Name = 'Authentication.RelyingParty'" -connstring $connstring).value.Split(',')[1].replace('"Realm":"','').replace('"','')
 
# Check is cert is signed
$admin = $adminsite.Split(':')
$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace('/',''),$admin[2])).GetStream())).IsSigned
 
# get token
$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert
 
# Get SQL Server Hosting Group
Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert
 
# Remove SQL Server Hosting Group
$SQLSvrGroupName = 'Laptop'
$SQLSvrGroup = Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -GroupName $SQLSvrGroupName
Remove-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -ServerGroupId $SQLSvrGroup.GroupId
 
# Add SQL Server Hosting Group
$GRPNAME = 'Laptop'
Add-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -GroupName $GRPNAME -ResourceGovernorEnabled
 
# Get SQL Server
Get-MgmtSvcSqlHostingServer -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert
 
# Remove SQL Server
$Svrname = 'desktop-ojt0lg6'
$sqlsvr = Get-MgmtSvcSqlHostingServer -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert | Where-Object {$_.Name -eq $Svrname}
Remove-MgmtSvcSqlHostingServer -AdminUri $adminapi -token $token -DisableCertificateValidation:$cert -HostingServerId $sqlsvr.ServerId
 
# Add SQL Server
$cred = Get-Credential -UserName 'SA' -Message 'Enter the SA Password for the SQL Server'
$GRPNAME = 'Laptop'
$SQLSize = 1024
$SQLCPU = 4
$SQLMEM = 4
$SQLIOPS = 2000
$SQLMAXResourcePools = 10
$SQLSVRName = 'desktop-ojt0lg6'
$svrgrp = Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -token $token -DisableCertificateValidation:$cert | Where-Object {$_.GroupName -eq $GRPNAME}
Add-MgmtSvcSqlHostingServer -adminuri $adminapi -Token $token -DisableCertificateValidation:$cert -SqlUser $cred -Name $SQLSVRName -TotalSpaceMB $SQLSize -ServerGroupId $svrgrp.GroupId -NumberOfCpuCores $SQLCPU -TotalMemoryGB $SQLMEM -SupportedIopsPerVolume $SQLIOPS -MaximumResourcePools $SQLMAXResourcePools

This is where I will leave you on this post, but stay turned for future posts around adding the SQL Server hosting group to a Plan.

Good Luck
Steve