Browsed by
Category: SQL

Windows Azure Pack PowerShell SQL Resource Provider Add SQL Server Hosting Group to Plan

Windows Azure Pack PowerShell SQL Resource Provider Add SQL Server Hosting Group to Plan

This post equates to the 5th in the series of blog posts for Azure Pack, where we have gone over the process of obtaining, adding, and removing the core components of Azure Pack being the User, Plans and Subscription objects, then we moved onto the process of Adding & removing the SQL Server hosting groups, and there dependent SQL servers in the SQL Resource Provider. So in this post we will complete the circle on the administrative side by adding the newly created SQL Server group to a Plan.

Unlike the past posts where the concepts were quite self-explanatory when attaching the SQL Server hosting group to a Plan, it needs to be completed in a two-step process, the first is to associate the SQL Resource Provider to the Plan, then once it has been attached there is a requirement to allocate the actual resources by using a Quota.

From here your users will be able to create their own databases on the presented SQL Servers.

# 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

 

# Add SQL Server Group to Plan

$ResProviderName = ‘sqlservers’

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

$ResourceProvider = Get-MgmtSvcResourceProvider -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -Name $ResProviderName

Add-MgmtSvcPlanService -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -InstanceId $ResourceProvider.InstanceId -ServiceName $ResourceProvider.Name -PlanId $plan.ID

 

# Add SQL Quota to Plan

$ServiceName = ‘sqlservers’

$QuotaList = New-MgmtSvcQuotaList

$ResProvider = Get-MgmtSvcResourceProvider -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -Name $servicename

$SQLQuota = Add-MgmtSvcListQuota -QuotaList $QuotaList -ServiceName $ServiceName -ServiceInstanceId $ResProvider.InstanceId

$sqlquotares = Add-MgmtSvcQuotaSetting -Quota $SqlQuota -Key Editions -Value ‘[{“displayName”:”Laptop”, “groupName”:”Laptop”, “resourceCount”:”1″, “resourceSize”:”1024″, “resourceSizeLimit”:”1024″, “offerEditionId”:”081313063701″, “groupType”:null}]’

Update-MgmtSvcPlanQuota -AdminUri $AdminAPI -Token $token -QuotaList $QuotaList -PlanId $plan.Id -DisableCertificateValidation:$cert

 

# Remove SQL Server group from plan

$ResProviderName = ‘sqlservers’

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

remove-MgmtSvcPlanService -adminuri $adminapi -token $token -disablecertificatevalidation:$cert -ServiceName $ResProviderName -PlanId $plan.id

 

# Get list of Services attached to Plan

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

$plan.ServiceQuotas.settings.Value

I hope this helps and makes sense, please let me know via twitter @steve_hosko if you would like to know any more information around this.

Good Luck

Steve

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

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